Cumulative Query

[Dominic Vella]

Hi

Has anyone tried to create a query that can cumulate numbers,

eg if 'table1' contained the following
Jan-2008, 7
Feb-2008, 3
Mar-2008, 5
Apr-2008, 1

I would like a query to come up with a result like this:
Jan-2008, 7
Feb-2008, 10
Mar-2008, 15
Apr-2008, 16

I can do it by creating have a query with the same table shown twice,
without a link, and then testing if table_1.date =< table.date then sum the
values
but I feel this is over processing the data.

Can anyone provide a better way to get this result?

tnx


Dom

 

[John Spencer]

Without knowing more about the fields, I can suggest the following

SELECT T.DateField, Sum(T2.Amount) as RunningSum
FROM Table1 as T LEFT JOIN Table1 as T2
ON T.DateField >= T2.DateField
GROUP BY T.DateField

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Of course, I should have mentioned that if you intend to use this result for a
report, it is may be simpler to do - don't calculate the running sum in the
query.

Just add a control to the detail section and set the control's source to the
quantity field and set the running sum property of the control to Over Group
or over All depending on if you want the running sum for all the records in
the report or just for sum.

Set the sort order using the report's Sorting and Grouping dialog.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dominic Vella]

Thank you

Much Appreciated

Dom

Without knowing more about the fields, I can suggest the following

SELECT T.DateField, Sum(T2.Amount) as RunningSum
FROM Table1 as T LEFT JOIN Table1 as T2
ON T.DateField >= T2.DateField
GROUP BY T.DateField

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County