Find Cumulative Sums in Access Query

[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

 

[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.