Problem: All the data I want to summarize is stored in a single database table. 
Using SQL I need to group the data into different levels of summarization. 


For example:

In the database table we have this:


Code		Description			Amount
--------------------------------------
200			Windows			1000    <This amount is empty in the database - it is calculated by the program>

210			Window Frames		200     <This amount is empty in the database - it is calculated by the program>
211			Bottom Frames		50
212			Top Frames		50
213			Side Frames		100

220			Glass			700     <This amount is empty in the database - it is calculated by the program>
221			Inner Glaze		250
222			Outer Glaze		250
223			Tinting			100

230			Misc			100	 <This amount is empty in the database - it is calculated by the program>
231			Rubber Seals		40
232			Handles			60



300			Doors			500	 <This amount is empty in the database - it is calculated by the program>

310			Door Frames		250      <This amount is empty in the database - it is calculated by the program>
311			Top Frame		100
312			Bottom Frame		100
313			Side Frames		50

320			Door			200	 <This amount is empty in the database - it is calculated by the program>
321			Basic Door		175
322			Venier 			25

330			Misc			50	 <This amount is empty in the database - it is calculated by the program>	
331			Door Knobs		50




So 220, 230, 310, 320 etc  are just titles with empty amounts. The amounts will be the subtotals of all the
following codes (ie 221, 222, 223 etc and 321, 322, 323 etc)

Codes like 200 and 300 are also empty except for a description - the amount is the subtotal of the 220, 230, 240 subtotals.
Hopefully this can bee seen in the list above - i've tried to group things into blocks. The top of each block is the subtotal
line.



In order to select only the Level 1 subtotals (eg 200, 300, 400)
we use a modulus calculation in our SQL select statement as follows:

---------------------
SELECT code, descritpion, amount from tbl_samsdata where
(code mod 100)=0 ORDER BY code
---------------------

To select only the rows that are level 2 (eg 210, 220, 310, 320 etc)
we do the same but with a different modulus:

---------------------
SELECT code, description, amount from tbl_samsdata where
(code mod 10)=0 AND (code mod 100)<>0 ORDER BY code
---------------------

That previous statement will get all the rows that are in 10s,
but not the rows which are in 100s (so it will retrieve all level 2
data but not level 1, even though level 1 is divisible by 10)

To get the rest of the rows (ie the actual data, with real money
values) we do a modulus 1 calculation like this:

---------------------
SELECT code, description, amount from tbl_samsdata where
(code mod 1)=0 AND (code mod 10)<>0 AND (code mod 100)<>0 ORDER BY
CODE
---------------------

This last statement is the one which gets the actual useful data.
Really, we can ignore the first two statements except to display
the description of the summary.



I figure I need to do something like this:


SELECT LEVEL 3 and SUM all of the values into a column called "Virtual totals level 3"

Then 

SELECT LEVEL 2 and sum all of the column "Virtual totals level 3" AS "Virtual totals level 2"

Then

SELECT LEVEL 1 and sum all of the column "Virtual totals level 2" AS "Virtual totals level 1"



Hmm.. now i'm losing it a bit - erm.... 

what I have to do is display level 1, and between the level 1 rows (ie between 200 and 300)
I have to display the level 2 rows (ie 210, 220, 230 etc) and between the level 2 rows (ie 210, 220, 230)
I have to display the level 3 rows (ie 211, 221, 222, 231 etc) ... and I have to be able to switch on/off
these rows with a button.


think think think... ponder.. make cup of tea... think think
Urgh....

I just read that it is possible to combine SQL queries (those SELECT statements) and to create
a virtual table from the results, even before this data is sent to the program I am writing (which will display it)
so there is hope there... I am sure this can be done.