Running total in Access Query based on item and date

I’m struggling with an Access Query trying to create a calculated field which presents a running total of parts that have gone or returned in the store; sorted in the order of date in one table.

The table “DMPartsT” would look like below:

ID     RecordDate     PartID     PartTransactions
1      12/04/2018     3          6
22     22/02/2017     9          14
3      18/05/2018     5          7
2      12/11/2017     3         -3
11     25/09/2018     7          12
4      21/06/2018     5         -2
5      14/07/2018     8          43
7      15/01/2018     3          18
6      24/03/2018     10         30
8      25/05/2018     3          -2

The result I am aiming for is for the query to return a table containing an added calculated field which would show a running total of a particular part, sorted in date. For instance, for PartID = 3, the generated result should be as follows:

ID     RecordDate     PartID     PartTransactions     PartTotal
2      12/11/2017     3         -3                    -3
7      15/01/2018     3          18                    15
1      12/04/2018     3          6                     21
8      25/05/2018     3         -2                     19

The query should look at all the available data in the table.

I have played around with the most obvious choice, the DSUM expression, in the calculated field with a low degree of success. A version of the code I use looks like:

PartTotal: IIf([PartID]=3,DSum("[PartTransactions]","DmPartsT","[RecordDate]<=#" & [RecordDate] & "#"))

and, with the ‘RecordDate’ Sorted in Acsending, and ‘PartID’ filtered to 3 in the query window, I get a list of all the parts of type 3 in the right date order, however with Part_Total figures all incorrect!

I’d be grateful if anyone could lend a hand in figuring out the correct approach. Thanks.

Answer

If you’re going to use DSum, you need to format your string. But you should rarely ever use a domain aggregate in a query. In nearly all cases, you should just use a subquery instead.

Also, you need to specify the part ID when summing as well, so include it in the WHERE clause of the subquery (or domain aggregate).

PartTotal: (SELECT SUM([PartTransactions]) FROM DmPartsT t WHERE t.[RecordDate]<=  DMPartsT.[RecordDate] AND t.PartID = DmPartsT.PartID)

This avoids errors with casting back and forth to a string, and allows the optimizer to analyze and optimize your query.