Re: ASP totals and sub-totals from an Access or SQL DB

From: Chris Hohmann (nospam_at_thankyou.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 13:05:34 -0800

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:eAM4JJCGEHA.1272@TK2MSFTNGP12.phx.gbl...
> Hi All
>
> I know I posted a similar topic a few days ago, but I may have gone on
a bit
> of a rant with it.
>
> Basically I want to know if it is possible to generate ASP-coded
sub-totals
> and totals on a basic recordset where the sub-totals are to break down
say
> by stock categories in a standard oRSv, eg
>
> stock cat stock code qty price
> 001 IVP 1 3.99
> 001 STP 23 14.99
> 001 BGT 6 3.99
> 001 VET 3 14.99
>
> <- insert sub-total for qty and price for 001 ->
>
> 004 X4562 5 3.99
> 004 X4566 11 14.99
> 004 X4563 3 3.99
> 004 X4567 2 14.99
>
> <- insert sub-total for qty and price for 004 ->
>
> <- insert grand total of qty and price for 001 and 004->
>
> I just couldn't fathom this in ASP, so I had to strip my SQL code,
stick it
> in MS Excel as a VBA/Query and then use MS Excel's Sub-total function
to do
> it for me.
>
> This resolved my immediate problems (ie pain in the neck from the end
user),
> but doesn't solve my problem of doing it in ASP.
>
> Any help you can give would be much appreciated.

MS SQL Server:
Use the CUBE operator.

MS Access:
Use a UNION query, ie.

SELECT
0 AS Level,
[stock cat],
[stock code],
qty,
price
FROM
<Table>
UNION ALL
SELECT
1,
[stock cat],
[stock cat] & ' Totals',
SUM(qty),
SUM(price)
GROUP BY
[stock cat]
UNION ALL
SELECT
2,
'',
'',
SUM(qty),
SUM(price)
FROM
<Table>
ORDER BY
[stock cat]='' DESC,
[stock cat],
Level

Notes:
In the future please provide details on the structure of your table
(name, fields, data types, etc...)

HTH
-Chris Hohmann