Cumulative Totals Query Help

[dvent]

Hi All,

I have a table containing Go Live dates. I want to produce a cumulative total by months i.e. the GL's in April, the GL's in April + May, the GL's in April + May + June etc.

I know I could produce 12 queries i.e. one for each month but I'm sure there must be an easier way to do this?

Thanks in advance
dvent

 

[DCrake]

If you want totals by month then create a totals query that groups and counts/sums by month

 

[dvent]

I have my totals:

01/04/2010 : 14
01/05/2010 : 26
01/06/2010 : 19

What I want it to show is:

01/04/2010 : 14
01/05/2010 : 14+26 (40)
01/05/2010 : 14+26+19 (59)

 

[DCrake]

Try searching for running totals query

 

[dvent]

Might need some assistance here.

I have my query containing 2 fields:

"MONTH" & "PLANNED GOLIVES"

I've created a third query with:

"MONTH" & "EXPR1"

EXPR1 = DSum([Planned GoLives],"GP_Planned_GLs")

This is giving me:

01/04/2010 - 182
01/05/2010 - 338
01/06/2010 - 247

From what I have read via Google, I thought this formula would work?

 

[DCrake]

Running sums are far easier in reports than in queries. Basically if you have a query with a date field and a value and you can the running sum your running sum column is a DSum([ValueField],[Table/Query],[DateField]<=[DateField])

 

[dvent]

Thanks for the help DCrake but still not coming out right. I think I've followed what you said but having the same issue:

 

[DCrake]

One big issue you will get is that you have used the word Month as a field name. This is an Access reserved word and may be the root of the problem. Rename and retry.

 

[dvent]

One big issue you will get is that you have used the word Month as a field name. This is an Access reserved word and may be the root of the problem. Rename and retry.

Renamed but have the same issue.

 

[dvent]

been playing around with this again and still no luck i'm afraid.

can anyone shed any light?