[thelist] SQL query statement help

[Joshua Olson]

----- Original Message -----
From: "Belinda Johnson" <belinda at prodsol.net>
Sent: Monday, December 09, 2002 1:09 PM


> In a nutshell - we have a very basic asp app using an Access database with
> separate tables for each auction item. (Right now there are about 50
> tables - by the end of the auction it will be at least double that as
we're
> adding 12-15 donated auction items per day through this Friday).

Uh oh... I think you may have made your life a lot more difficult with this
design.  But regardless, the query to sum up the highest bids (which is the
latest bid, right) will be something like this if you want only one query:

SELECT
  ((SELECT TOP 1 amount FROM angel order by T4 desc) +
   (SELECT TOP 1 amount FROM dog order by T4 desc) +
   (SELECT TOP 1 amount FROM cat order by T4 desc) +
   (SELECT TOP 1 amount FROM horse order by T4 desc) +
   (SELECT TOP 1 amount FROM mouse order by T4 desc) +
   (SELECT TOP 1 amount FROM scubagear order by T4 desc)) AS totalamount
FROM dummytable

dummytable can be any table that only has one record.

Or, you can do it in ASP by looping through all the tables, performing the
one query you had before, and adding up the results within ASP.

Perhaps an alternate method to all of this (given your current investment)
would be to store the RUNNING total somewhere.  Anytime someone adds a bid
for the item, adjust the running total accordingly.  Then, display the
running total on the page.  You may want to put the running total in a
database table with one column and one record, or perhaps store the running
total in a text file or application variable.

HTH,
-joshua