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?
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);