totals

[Guest]

Does anyone know how, using asp and an access database, one could display the total of the records retrieved. all of the records
retrieved are #'s and I need to display the sum of all those #'s.
thanks in advance!

 

[Tim Ferguson]

Does anyone know how, using asp and an access database, one could
display the total of the records retrieved.

SELECT SUM(SomeField) AS MyTotal
FROM MyTable
WHERE Criterion = TRUE;

Hope that helps


Tim F

 

[bobs]

I've been trying to figure out how to design a query that gives results AND totals those results for some time without success. I've seen the suggestion below, however, how do I incorporate it? Please advice. Thanks, Bob.

SELECT SUM(SomeField) AS MyTotal
FROM MyTable
WHERE Criterion = TRUE;

 

[Tim Ferguson]

I've been trying to figure out how to design a query that gives
results AND totals those results for some time without success. I've
seen the suggestion below, however, how do I incorporate it? Please
advice. Thanks, Bob.

I am not quite sure what you are asking for.

If you want help with constructing a query, the easiest way is to use the
query window in Access, then change to the SQL window (View | SQL) and
copy-paste it into wherever you want to use it.

If you want particular help with designing the query, you will have to give
some details of your tables and what you want to calculate.

If you want to know how to get your SQL into a asp program, then this will
be in any ADO resource:- basically it's something along the lines of

Set rst.ActiveConnection = ....

strSQL = SELECT .... "

rst.Open strSQL

Do While Not rst.EOF
document.print "<tr><td>" & rst!IDNumber & "</td>"

...


rst.MoveNext
Loop

Hope that helps


Tim F

 

[bobs]

Thanks. I was looking for help with writing the query only. I think the answer to my question is that there is no way to have a query that 1) list results and 2) total those results. I believe that this would need to be done with a report.

 

[John Nurick]

Hi Bob,

You can do it with something like this:

SELECT fRowType, fNum1, fTxt1, fNum2 FROM (
SELECT NULL AS fRowType, Num1 AS fNum1, Txt1 AS fTxt1,
Num2 AS fNum2, 1 AS fOrderMe
FROM T97A
WHERE blah blah
UNION
SELECT "Total" AS fRowType, SUM(Num1) AS fNum1,
NULL AS fTxt1, SUM(Num2) AS fNum2, 2 AS fOrderMe
FROM T97A
WHERE blah blah
)
ORDER BY fOrderMe, fNum1;

 

[Tim Ferguson]

there is no way to have a query that 1) list results and 2) total
those results.

Without knowing the details of what you want, this sounds probably right.

Or two queries.

But if you want to print or display the results of the query(ies) then you
need a report anyway, after all.

B Wishes


Tim F