How do I add cumulative total in a query not report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is easy in a report with the cumulative total in the properties window
of the field. I'd like to know how to do this in a query or some other
method that allows writing to a file.

Thanks,


JD
 
Dear JD:

A cumulative total, or running sum, can be produced as an added column in a
query.

Not surprisingly, the sequence in which this is to be done is a significant
factor in doing this. Also, there is the question of whether the sum runs
for the entire table, or whether it starts over for specific groups within
the table.

I suggest you post a query that shows everything you want done except for
the cumulative totals. Include an ORDER BY clause that uniquely orders the
rows. If the ordering is not unique, then those rows that "tie" will not
add in one at a time, but will add in at the same point.

If you wish to have the total reset to zero for subsets of the table, then
specify which column(s) comprise the group over which this occurs.

I have found over the years that an example of how this is done, based on
your specific requirements, can be a very good way for you to learn how this
is done.

Tom Ellison
 
It's hard to improve on what Tom Ellison just said, but I thought an
example might be illustrative. I'll add up successive numbers to get a
list of triangular numbers (such as 10 bowling pins in a triangle of 4
rows).

I start with a Table of the numbers to be added:

[Counting] Table Datasheet View:

Integer
-------
1
2
3
4
5

Then I define a Query to accumulate the totals, but display the results
in the reverse order:

[Q_TriangularNumber] SQL:

SELECT C.Integer, Sum(T.Integer) AS [Triangular Number]
FROM Counting AS C, Counting AS T
WHERE (((T.Integer)<=[C].[Integer]))
GROUP BY C.Integer
ORDER BY C.Integer DESC;

And the results show the cumulative totals (displayed in the order I
chose, which would be tricky in a Running Sum field in a Report). As
Tom pointed out, if you have duplicate values (which I sneakily avoided
here), you need to take them into account, such as via the Table's
primary key.

[Q_TriangularNumber] Query Datasheet View:

Integer Triangular Number
------- -----------------
5 15
4 10
3 6
2 3
1 1

But you have other options as well (and not just copying the whole Table
to Excel, either). For example, you could (and I sometimes do) print
your Report to the "Generic / Text Only" printer, a useful device even
though mine didn't cost me as much as my color inkjet model. (If you
don't have one in your list of printers, open Control Panel --> Printers
and Faxes, then choose Add Printer and select the Print To File option.)
I usually get my best results with this by making a copy of the Report
design I wish to produce, and on the COPY changing the fonts on all the
controls to Courier 12 point (or similar) and erasing all the rules and
other fancy decorations that the Report Wizard added. It doesn't look
great but works well as a text file. After writing to the file, check
to be sure that you have everything -- Access likes to not bother you
with overflow messages, preferring to just truncate strings that are too
long. (Or maybe I've missed a global setting somewhere.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Well... Lets say your query has 5 fields and you want to sum values the
fourth field and show them on the last row of your query.
try adding
at the edn of your query this:

UNION
Select " ", " ", " ", Sum(field4)
FROM YourQueryHere

but first...I hope to have understood what you need!!

Ciao
Rocco
 
Jdurham said:
This is easy in a report with the cumulative total in the properties window
of the field. I'd like to know how to do this in a query or some other
method that allows writing to a file.

Thanks,


JD
Click to expand...

Vincent....You da man!

I was looking to have an extra column with the running cumulative total.
After a slight adjustment to Vincent's post I got exactly what I
needed..Thanks,

SELECT C.Date, C.Result, Sum(T.Result) AS TEST
FROM YourTable AS C, YourTable AS T
WHERE (((T.Date)<=[C].[Date]))
GROUP BY C.Date, C.Result
ORDER BY C.Date;

Thanks Again.....
 
Back
Top