A query with a running sum?

  • Thread starter Thread starter Hans Merkl
  • Start date Start date
H

Hans Merkl

Hi,

I am trying to create a query that calculates a running sum over its
entries. I know I can create a report that does it, but I would like to have
a query so I can modify the data. Here is an example:

Amount Running sum
10 10
-5 5
3 8

Is such a thing possible?

Thanks

Hans Merkl
RHM Media, LLC
 
Dear Hans:

It is possible to do what you want, but only if you have a way of
sorting the rows that is unique. If there are multiple rows that sort
the same, then those rows will all add into the running sum
simultaneously, since those rows are, in the sort order, all
equivalent.

Please post a simple SELECT query here with the sorting you need. If
you need the RunningSum to start over for different groups, please
list the field(s) that define each group. Otherwise, the RunningSum
will be over the entire table or query on which this is based, like
your example.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

A sample query would be "Select Date,Amount from Transactions order by
date". How would you add the running sum to this query?

Thanks

Hans
 
Hi Hans

This is very simple to do. Sent me your email and I'll send you a sample.

Maurice
 
Dear Hans:

SELECT [Date], Amount, (SELECT SUM(Amount) FROM Transactions T1 WHERE
T1.Date <= T.Date) AS RunningSum
FROM Transactions
ORDER BY Date

It would not seem unlikely you could have more than 1 transactions on
the same [Date] and, if so, these will all add into the running sum
simultaneously. If you can provide one or more columns that make the
sort unique, this can be easily changed to have a unique running sum.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I got the idea. Thanks very much.

I once worked with a database on a PalmPilot. There you could refer to the
previous record in a query. This makes running sums very easy. Would be nice
to have in SQL :-)
--
Hans Merkl
RHM Media, LLC

Tom Ellison said:
Dear Hans:

SELECT [Date], Amount, (SELECT SUM(Amount) FROM Transactions T1 WHERE
T1.Date <= T.Date) AS RunningSum
FROM Transactions
ORDER BY Date

It would not seem unlikely you could have more than 1 transactions on
the same [Date] and, if so, these will all add into the running sum
simultaneously. If you can provide one or more columns that make the
sort unique, this can be easily changed to have a unique running sum.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

A sample query would be "Select Date,Amount from Transactions order by
date". How would you add the running sum to this query?

Thanks

Hans
Click to expand...
Click to expand...
 
Dear Hans:

I understand this. SQL is a standard language for data access, and it
does not distinguish between multiple rows with the same sort key.
That is because to do so would be ambiguous. From this standpoint, it
is a good thing, but based on your past experience it seems to be a
problem because it prevents you from doing something the easy way,
even though that way is ambiguous.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top