Query to calculate quarterly rolling value

  • Thread starter Thread starter salesi
  • Start date Start date
S

salesi

Can someone advise as to how I might write a query to calculate a
quarterly rolling amount I can set it up on EXCEL based on cell values,
but I'm not sure how to write a query on Access to get the same
results.

Current Data
[Date] [Amount]
1/Jan/2003 100
1/Feb/2003 90
1/Mar/2003 80
1/Jan/2002 70
1/Feb/2002 60
1/Mar/2002 50
1/Jan/2001 40
1/Feb/2001 30
1/Mar/2001 20

Desired Result
[Date] [Amount] [quarterly rolling amount]
1/Jan/2003 100 100+90+80 = 270
1/Feb/2003 90 90+80+70 = 240
1/Mar/2003 80 80+70+60 = 210
1/Jan/2002 70 70+60+50 = 180
1/Feb/2002 60 60+50+40 = 150
1/Mar/2002 50 50+40+30 = 120
1/Jan/2001 40 40+30+20 = 90
1/Feb/2001 30 30+20+0 = 50
1/Mar/2001 20 20+0+0 = 0
:confused:
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[Date],
Sum([Self].[Amount])
FROM
[Your Table],
[Your Table] AS [Self]
WHERE
[Self].[Date] >= [Your Table].[Date]
AND
[Self].[Date] < DateSerial(Year([Your Table].[Date]), Month([Your
Table].[Date])+4, 1)
 
Back
Top