DSum()

[Ryan]

I have the following data in a table.

Material number Quantity
1232 10
1232 -52
1232 13
5658 0
5658 3
5658 -2

I want to run a query that gives me a running total that resets itself at
each change in Material number. Can I use DSum or do I need to write some
code? The results should give the following:

Material number Quantity Running Total
1232 10 10
1232 -52 -42
1232 13 -29
5658 0 0
5658 3 3
5658 -2 1

Any help would be great!

 

[Allen Browne]

You can use DSum() or a subquery to get the total so far.

There are a few issues with this:
a) The table must have a primary key: the concept of a running total depends
on the order of records, and the order of records is not properly defined
without a primary key. We will assume a primary key named ID for the example
below.

b) A subquery will be faster than DSum(), but will make the query read-only
(so not good for a form), and may not work for a report either ('multi-level
group-by not allowed' error.)

c) If the user filters or sorts the query results differently, the running
sum will no longer be correct.

d) The simplest solution is to do this in a report instead of a query. You
can set the Running Sum property of the text box on the report, and avoid
all these issues.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

The query will end up being something like this:

SELECT [Material number], ID, [Quantity],
(SELECT Sum(Quantity) AS HowMany
FROM Table1 AS Dupe
WHERE Dupe.[Material number] = Table1.[Material number]
AND Dupe.ID <= Table1.ID) AS RunningTotal
FROM Table1
ORDER BY [Material number], ID;