Find Cumulative Sums in Access Query (1 Viewer)

[Sarma]

I have a result of a query that looks as under:

Serial Number / Month / Amount

1 / Jan / 145
2 / Feb / 0
3 / Mar / 250
4 / Apr / 500
5 / May / 225

I have to make another query from this query that shows the results as under:

Serial Number / Month / Amount / Cum. Amount

1 / Jan / 145 / 145
2 / Feb / 0 / 145
3 / Mar / 250 / 395
4 / Apr / 500 / 895
5 / May / 225 / 1,120

Shall be grateful to receive help.

 

[pbaldy]

Search on "running sum". Typically you use a subquery or DSum().

 

[Sarma]

I have tried to run a query. I added a new field with DSUM("Amount","Table 1").

Result is that against each row, I got the same cumulative sum value whereas I need the cumulative value for each row.

Trust I am able to clarify.

Rgds.

 

[Sketchin]

RunTot: Format(DSum("variable","variable","[variable]<="& [variable] & ""), "format for the field")

 

[pbaldy]

Of course not, you have no criteria:

http://www.mvps.org/access/general/gen0018.htm

 

[Sarma]

Sketchin: Thanks for your interest & advice. Possible to give the exact syntax instead of generic words such as 'variable' ? I am a perennial learner. My basic job is petroleum engg.

Rgds

 

[Pat Hartman]

Running sums are quite easy to produce in a report and also very efficient. Due to the way queries work, you can only sum to a single level and running sums are very inefficient. You effectively need to run an embedded query for each row of the recordset. So, if you have 500 rows, you actually need to run 501 queries. Reports are a sequential process so Access can just accumulate each row as it "prints" it and print the total at a break. This is just one pass through the data so depending on the size of your recordset, it could take minutes to produce a query but a report showing the same data could be produced in seconds. Of course, if you have more than a few thousand rows, you shouldn't even try to do this in a query.

 

[Sarma]

I have used the following SQL and was able to obtain result:

SELECT Table1.[Serial No], Table1.Month, Table1.Amount, Sum(Format(DSum("Amount","Table1","[Serial No]<=" & [Serial No] & ""),"0,000.00")) AS RunTot
FROM Table1
GROUP BY Table1.[Serial No], Table1.Month, Table1.Amount
ORDER BY Table1.[Serial No];

 

[vbaInet]

You don't need the Sum() part. DSum() is sufficient. Create a query to pull DISTINCT rows from your table and add the DSum() function there.

By the way, Table1 is not an intuitive name. I would advise you rename it to something more meaningful.