Accumulative sums

  • Thread starter Thread starter mariajdias
  • Start date Start date
M

mariajdias

Guest
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
 
How are you trying to do it at the moment?
 
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!
 
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?
 
I'm working on a table. Is there a similar solution for a table?

Thanks also.
 
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.
 
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...
 

Attachments

  • qryexample1.jpg
    qryexample1.jpg
    35.9 KB · Views: 220
Last edited:
...and query number 2.
 

Attachments

  • qryexample2.jpg
    qryexample2.jpg
    32.6 KB · Views: 230
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!
 
You must log in or register to reply here.

Similar threads

I
Create and training record database
Replies
6
Views
1,366
MadPiet
M
J
Sum Query and text box
Replies
11
Views
1,079
arnelgp
arnelgp
C
How to return null when summing values that include nulls
Replies
6
Views
1,137
cheekybuddha
cheekybuddha
S
Problem to run Access database
Replies
3
Views
632
mike60smart
mike60smart
D
Using VBA to find perfect numbers, and accurately calculate pi to 100,000 decimal places.
Replies
11
Views
919
Steve R.
Steve R.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom