Query Challenge

  • Thread starter Thread starter Bret
  • Start date Start date
B

Bret

Below is an existing table which tracks customer totals
for each month. I need to design a query based on this
table that creates a new field with QUARTERLY totals.
Query will GROUPS BY ID, then SUMS current month + prior 2
months and places result on corresponding row.
The query is actually simple, its the placing the results
in the corresponding rows that becomes the issue. I have
resolved this thru programming however its very time
consuming 'walking thru recordsets' as I've done. (100,000
records) A sample of how the results should look is
provided at end of this email. Please help!.
Much thanks.


EXISTING TABLE
---------------------------
ID month MonthlyTotal
1 6-1-2003 50.00
1 7-1-2003 100.00
1 8-1-2003 150.00
1 9-1-2003 200.00
1 10-1-2003 250.00
1 11-1-2003 300.00
1 12-1-2003 350.00

DESIGN A QUERY WITH THIS RESULTS.
AS AN EXAMPLE: the 300.00 quarterly total is a sum of June
July & August.
ID month MonthlyTotal QuarterTotal
1 6-1-2003 50.00
1 7-1-2003 100.00
1 8-1-2003 150.00 300.00
1 9-1-2003 200.00 350.00
1 10-1-2003 250.00 600.00
1 11-1-2003 300.00 750.00
1 12-1-2003 350.00 900.00


thank you. Norbert
 
Bret said:
Below is an existing table which tracks customer totals
for each month. I need to design a query based on this
table that creates a new field with QUARTERLY totals.
Query will GROUPS BY ID, then SUMS current month + prior 2
months and places result on corresponding row.
The query is actually simple, its the placing the results
in the corresponding rows that becomes the issue. I have
resolved this thru programming however its very time
consuming 'walking thru recordsets' as I've done. (100,000
records) A sample of how the results should look is
provided at end of this email. Please help!.
Much thanks.


EXISTING TABLE
---------------------------
ID month MonthlyTotal
1 6-1-2003 50.00
1 7-1-2003 100.00
1 8-1-2003 150.00
1 9-1-2003 200.00
1 10-1-2003 250.00
1 11-1-2003 300.00
1 12-1-2003 350.00

DESIGN A QUERY WITH THIS RESULTS.
AS AN EXAMPLE: the 300.00 quarterly total is a sum of June
July & August.
ID month MonthlyTotal QuarterTotal
1 6-1-2003 50.00
1 7-1-2003 100.00
1 8-1-2003 150.00 300.00
1 9-1-2003 200.00 350.00
1 10-1-2003 250.00 600.00
1 11-1-2003 300.00 750.00
1 12-1-2003 350.00 900.00


thank you. Norbert
Click to expand...

See the MS KB article 138911 "How to create running totals in a query."
 
Back
Top