I am using MS Access to create a DB. I have different physical containers and I want to obtain the running sum of the liquid that has been added and taken out of the container to give the balance of the liquid inside the container.
The biggest problem is ordering liquid transactions. I have read lots of resources, and I found out one way of doing this is adding time to the dates so they can be ordered, but I am supposed to not use time, so I have decided to add a manual ordering number within a date which I am not sure is the best way of achieving this, but at least I can do the ordering.
The fields I have are:
-ContainerId
-DateTransaction
-OrderInDate
-Quantity

Quantity is "-" for withdrawal and "+" for additions, so if I can properly get a running total, I will get the balance.
I thought of adding OrderInDate as seconds to the Date to correctly order the data and I have written a query like this (qryInventoryTransactionsOrder):
SELECT ContainerId, Quantity, DateTransaction, OrderInDate,
DateAdd("s",OrderInDate,DateTransaction) AS Expr1,
DSum("Quantity","qryInventoryTransactionsOrder",
"[ContainerId]=" & [ContainerId] & " AND
[Expr1] <= #" & [Expr1] & "#") AS Balance
FROM InventoryTransactions
ORDER BY ContainerId, DateAdd("s",OrderInDate,DateTransaction);

This returns very interesting result like this :




ContainerId
DateTransaction
OrderInDate
Quantity
Expr1
Balance




1
29/05/2023
1
-50
29/05/2023 00:00:01
-50


1
31/05/2023
1
100
31/05/2023 00:00:01
50


1
31/05/2023
2
255
31/05/2023 00:00:02
305


1
01/06/2023
1
-155
01/06/2023 00:00:01



1
01/06/2023
2
-155
01/06/2023 00:00:02



1
01/06/2023
3
2500
01/06/2023 00:00:03



1
08/06/2023
1
-500
08/06/2023 00:00:01
1995




As you will see "Balance" is correct for the first 3 lines, then it returns 3 empty results, and then 1995.
What am I doing wrong here or is there a better way to achieve this result?

Ms Access running sum with dates and order inside dates
I can't say anything about recursive queries in MS Access.It is interesting.
Try this query, where DSum counts sum from main table.
SELECT ContainerId, Quantity, DateTransaction, OrderInDate
    ,DateAdd("s",OrderInDate,DateTransaction) AS Expr1
    ,DSum("Quantity","InventoryTransactions"
       ,"[ContainerId]=" & [ContainerId] 
        & " AND Format(DateAdd(""s"",OrderInDate,DateTransaction),""yyyyMMddhhmmss"") <= " 
      & format(DateAdd("s",OrderInDate,DateTransaction),"yyyyMMddhhmmss") & "") 
    AS Balance
FROM InventoryTransactions
ORDER BY ContainerId, DateAdd("s",OrderInDate,DateTransaction);

