Accumulative sums

[mariajdias]

Hello,

I don’t know if this is the kind of question you can ask here, but I’m having trouble trying to do accumulative sums in access. What I would like to do is:

Order Number Values Column I would like to have
1 4 4
2 5 9
3 6 15

Basically, it sums the values up while it goes down. The values are in high to low order.

I have Access 97.

Thanks in advance!

Maria

 

[Ally]

How are you trying to do it at the moment?

 

[mariajdias]

Well, I went to Access Help and found Dsum. It seemed like the right function but I got stuck in the criteria. Now I don’t know if it’s the right path anymore.

Thanks!

 

[Ally]

Sorry - I hadn't cottoned on to exactly what you were trying to do. I think you're going to need to do it in code, because you're going to need to refer to the result of the last record.

Can anyone else help?

 

[Rich]

Use the RunningSum property for a control on your report

 

[mariajdias]

I'm working on a table. Is there a similar solution for a table?

Thanks also.

 

[dcx693]

I'd suggest you not calculate and store values in your table. I was able to solve the problem using two queries, but maybe there's a more clever way out there to do it with just one. This query will just display the calculated cummulative totals, it won't store them. If you want, you can change the query to an update query and update your tables to store the value.

Here's the first query, which I simply called "qry1":
SELECT tblCumSum.OrderNo, tblCumSum.Values, tblCumSum_1.Values
FROM tblCumSum, tblCumSum AS tblCumSum_1
WHERE (((tblCumSum.OrderNo)>=[tblCumSum_1].[OrderNo]));


The second query, called "qry2", is based on the first query:
SELECT qry1.OrderNo, qry1.tblCumSum.Values, Sum(qry1.tblCumSum_1.Values) AS Total
FROM qry1
GROUP BY qry1.OrderNo, qry1.tblCumSum.Values;

I've assumed that your data table is called tblCumSum (for Cummulative Sum). Just change it, and the field names above to those that you are actually using. I will also try to paste pictures of the queries in design view so you can see them.

 

[dcx693]

Here's a picture of the first query.

You may be wondering just what tblCumSum_1 is. I added the table to the query twice and joined them using the criteria tblCumSum.OrderNo >= tblCumSum_1.OrderNo. (If you add a table to a query more than once, Access automatically places an _1 to the end of the original name.)

What that does is match up order numbers on one copy of the table with order numbers that are bigger or equal to it on the other copy of the table. So, for example,

OrderNo 1 gets matched up with only OrderNo1 from the table copy.

OrderNo 2 gets matched up with orders nos 2 and 1, since it's > or = to 1 and 2.

etc...

 

[dcx693]

...and query number 2.

 

[Cosmos75]

Running Sum
Running Sum / Total / Count / Average in a query using a correlated subquery

Running Sum Query to create Graph / Chart
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806&highlight=Running

 

[mariajdias]

I tried qryexample1 and qryexample2 and the results just came in (the table has 30.000 records…) and it’s just what I wanted! Thank you for the help!

 

[dcx693]

mariajdias, you're welcome.