Solved Accumulative Quantity in query (1 Viewer)

[Babycat]

Hi everyone,

I have table/query which includes 4 fields as below picture.
Intype field indicates import to or export from inventory.

I want a make an extra column - Balance Qty which is actual quantity at each time.

How can I make a such query?

If that is hard or complicated with multiple Product IDs. How is about only one Product ID in the table?

 

[pbaldy]

Search on running sum. You can either use a subquery or DSum() to sum records matching the current ID where the CreatedDate is less than or equal to the current record.

 

[Babycat]

Search on running sum. You can either use a subquery or DSum() to sum records matching the current ID where the CreatedDate is less than or equal to the current record.

Should I work it out with recordset method? it's likely simpler

 

[pbaldy]

You can try. I suspect it would be less efficient.

 

[Pat Hartman]

Because a query is "live" meaning it can be sorted at any time and the calculation would need to be performed again, it is extremely inefficient to calculate a running sum in a query. However, it is a trivial and efficient process in a report since the report is a one pass through the data and you don't have to worry about sorting on the fly. Performing the calculation in a recordset loop is the same method used by the report engine. However, that implies that you are going to save the calculated results which is not at all recommended.

 

[CJ_London]

Using a non standard join will be faster than a sub query but if final destination for the query results is a report, use the report functionality as pat suggests.

a non standard join can only be written in sql although you can build it using standard joins the switch to sql view to modify the join

your query might look something like this

select a.productid, a.createddate, a.in type,a.quantity, sum(b.quantity*iif(b.in type=‘out’, -1,1) as runningsum
From mytable a inner join mytable b on a.productid =b.productid and b.createddate<=a.createddate
Group by a.productid, a.createddate, a.intype,a.quantity
Order by a.productid, a.createddate

The non standard join is

b.createddate<=a.createddate

 

[Babycat]

Because a query is "live" meaning it can be sorted at any time and the calculation would need to be performed again, it is extremely inefficient to calculate a running sum in a query. However, it is a trivial and efficient process in a report since the report is a one pass through the data and you don't have to worry about sorting on the fly. Performing the calculation in a recordset loop is the same method used by the report engine. However, that implies that you are going to save the calculated results which is not at all recommended.

I am doing inventory access project, where user can trace back an item's data at any time in the past. I am trying to extract these info from ORDER and ORDERDETAIL table
For example, customer randomly desires to view the info of item A in Q2 (Mar/01 to June/31)
- ImportQty, Import_Amount,
- ExportQty, Export_Amount = ExportQty * everage_price
- Closing stock
- Closing Amount
- ....

Each import is probably with different quanity and price, so most challenge part is that average_price calculation, it is the key factor and it changes all the time
1st: import qty n1, price p1 --> BalanceAmount= n1*p1; average_price1 = p1;
2nd: import qty n2, price p2 --> BalanceAmount= (n1*p1 + n2*p2); average_price2= BalanceAmount/ (n1 +n2);
3rd: export qty n3 --> BalanceAmount= (n1*p1 + n2*p2) - (average_price2 * n3); average_price3 = BalanceAmount / (n1 +n2 - n3)
4rd: import qty n4, price p4 --> BalanceAmount= average_price3 *(n1 +n2 - n3) + n4*p4; average_price4 = BalanceAmount / (n1 +n2 - n3 + n4)
...so on

These value can be easily calculated and stoted in table at the time we do impoirt/export action
since they can be calculated, so I dont store them, but recalculating them in query looks exhausted.
These accumulative values also can be done with recordset loop, but i think query still faster and efficent

 

[Babycat]

Using a non standard join will be faster than a sub query but if final destination for the query results is a report, use the report functionality as pat suggests.

a non standard join can only be written in sql although you can build it using standard joins the switch to sql view to modify the join

your query might look something like this

select a.productid, a.createddate, a.in type,a.quantity, sum(b.quantity*iif(b.in type=‘out’, -1,1) as runningsum
From mytable a inner join mytable b on a.productid =b.productid and b.createddate<=a.createddate
Group by a.productid, a.createddate, a.intype,a.quantity
Order by a.productid, a.createddate

The non standard join is

b.createddate<=a.createddate

Looks promising, I will try it.

 

[CJ_London]

would have been better if you had stated what was required in the first place rather than only part of it. And to be clear you are required to use an average pricing basis rather than FIFO of LIFO?

Which leads on to the question - having determined your average price, what do you then do with it? If it forms part of a legal document/view such as an invoice then better to store the calculated value - if at some point in the future a previous transaction is found or requires correction you would not recalculate since you have already incorporated a value in your document - and that cannot be changed.

If on the other hand, it is used as part of reporting such as cost of sales or stock valuation in a P&L/BS view of the company then you are calculating to a point in time, so do not need to list every transaction. The other aspect is some financial systems allow restating of prior months, whilst others require any prior month corrections to be incorporated into the current month..

Finally, potentially there comes a point where you have 10 years worth of transactions - the averaging method requires you start from the beginning so that could be a lot of records. So at some point (such as year end), you will need to store the quantity and average price at that time and use that to kick off the calculations or perhaps better, the latest point when quantity is zero.

 

[Babycat]

would have been better if you had stated what was required in the first place rather than only part of it. And to be clear you are required to use an average pricing basis rather than FIFO of LIFO?

Which leads on to the question - having determined your average price, what do you then do with it? If it forms part of a legal document/view such as an invoice then better to store the calculated value - if at some point in the future a previous transaction is found or requires correction you would not recalculate since you have already incorporated a value in your document - and that cannot be changed.

If on the other hand, it is used as part of reporting such as cost of sales or stock valuation in a P&L/BS view of the company then you are calculating to a point in time, so do not need to list every transaction. The other aspect is some financial systems allow restating of prior months, whilst others require any prior month corrections to be incorporated into the current month..

Finally, potentially there comes a point where you have 10 years worth of transactions - the averaging method requires you start from the beginning so that could be a lot of records. So at some point (such as year end), you will need to store the quantity and average price at that time and use that to kick off the calculations or perhaps better, the latest point when quantity is zero.

Hi CJ_London

I was not confident with my english, too long post may not emphasize my need.
Yes, i need "average pricing" calculation scheme. I did not understand much about accounting terms, too.
I am aware that the calculated value will be stored at some certain point to re-use later. However my application does not have much transaction, so calculating from beginning still acceptable. Moreover, let say the stored point was at 31/12/2020, my customer enter time period from 30/11/2020 to 30/01/2021, there will be quite confusion on what price shoud use...
Anyway, with or without storing calculated data I still dont have clear efficent strategy to calculate these data with query.
Any suggestion from you?

Thanks and regards,

 

[Pat Hartman]

If the data is imported and you NEVER update it, then there is no reason to not do the import using a recordset so you can calculate the running sums and rolling averages and save them.

HOWEVER, if a different price today could effect data imported in the past because it increases/decreases the average price, then you can't store anything. But if you are using a rolling average - all values up to the current one, then history does not change and it is safe to store the value.

The procedure to calculate a rolling average is different from the one to calculate a straight average. A simple query calculates the average:
Select PartID, Avg(Price) As AvgPrice
From YourTable.

The rolling average can be done in a query using the same technique as the running sum (ie by using a non equi-join to the main table so you can look at one record and all the records that came before it but it is much more efficient to do it in a report if you are not going to save the calculation or using a recordset if you ar going to save the calculation.

 

[Babycat]

If the data is imported and you NEVER update it, then there is no reason to not do the import using a recordset so you can calculate the running sums and rolling averages and save them.

HOWEVER, if a different price today could effect data imported in the past because it increases/decreases the average price, then you can't store anything. But if you are using a rolling average - all values up to the current one, then history does not change and it is safe to store the value.

The procedure to calculate a rolling average is different from the one to calculate a straight average. A simple query calculates the average:
Select PartID, Avg(Price) As AvgPrice
From YourTable.

The rolling average can be done in a query using the same technique as the running sum (ie by using a non equi-join to the main table so you can look at one record and all the records that came before it but it is much more efficient to do it in a report if you are not going to save the calculation or using a recordset if you ar going to save the calculation.

The Average_price and inventory BalanceQty at a point in the past dont depend on today price. So saving them to table is fine. I can save them by recordset method at the time the transaction is being proceeded. However, they are calculated values, so most ppl suggest not saving them to table, it is also good for db size. But my other cencern is what if an order is somehow deleted from system, the saved calculation data is likely no longer correct.
And about this point Select PartID, Avg(Price) As AvgPrice, it is only correct if all import qantities are same. ie:
1st: import 2 apples at price 2$ each
2nd: import other 4 apples at price 4$ each
The average import price can not be 3$, it should be TotalAmount/TotalQty = 20/6 = 3,33$.

I attach picture of my tables

Code of import activity:

                        Set db = CurrentDb
                        Set rs = db.OpenRecordset("TBLORDER")
                            rs.AddNew
                            rs!Type = True         
                            rs!Createddate = Now
                            rs!StaffID = Txt_UserID
                            rs!Reason = Txt_Transreason
                            currentOrderID = rs!OrderID
                            rs.Update
                        rs.Close
                        Set rs = Nothing
                        Set db = Nothing
                       
                        InsertSQL = "INSERT INTO TBLORDERDETAIL ( OrderID, ProductID, Cost, Qty, Vendor  ) " _
                               & " SELECT " & currentOrderID & " AS OrderID, " _
                               & " L_TBLTEMP.ProductID, L_TBLTEMP.Cost, L_TBLTEMP.Qty, L_TBLTEMP.Vendor " _
                               & " FROM L_TBLTEMP; "
               
                        UpdateSQL = "UPDATE TBLPRODUCT P, L_TBLTEMP T" _
                                & " SET P.Lastaction = True, P.Exstock = Nz(P.Exstock,0) + Nz(T.Qty,0), P.CostPrice = Nz(T.Cost,0) " _
                                & " WHERE P.ProductID = T.ProductID;"
                             
                        DoCmd.SetWarnings False
                        DoCmd.RunSQL InsertSQL
                        DoCmd.RunSQL UpdateSQL
                        DoCmd.SetWarnings True

 

[Babycat]

The rolling average can be done in a query using the same technique as the running sum (ie by using a non equi-join to the main table so you can look at one record and all the records that came before it but it is much more efficient to do it in a report if you are not going to save the calculation or using a recordset if you ar going to save the calculation.

I need it in both form and report, I will try your suggested technique

 

[Pat Hartman]

The recommendation to not save calculated values applies to active data. Historical data goes the opposite way. rather than codes, the lookups are embedded so instead of 1, the gender would be Female. Instead of 1, the MaritalStatus would be Married and calculated values are stored to avoid the need to recalculate them for each use. In a history database, nothing changes so the rules lean toward efficiency and minimize joins whenever possible.

The problem with saving calculated values in a transaction oriented system is because if it is possible to add rows to the "past" or modify the "past", that totally messes up rolling averages and running sums. Use that as your guide. If any component of the calculation could change, you can't store the calculated value. That leaves us with how to do this most efficiently, The answer to that is with a report. The rolling average is problematic. You would need to go back to the beginning of time to calculate it even if the user just wanted to see last month's data.

 

[Babycat]

The recommendation to not save calculated values applies to active data. Historical data goes the opposite way. rather than codes, the lookups are embedded so instead of 1, the gender would be Female. Instead of 1, the MaritalStatus would be Married and calculated values are stored to avoid the need to recalculate them for each use. In a history database, nothing changes so the rules lean toward efficiency and minimize joins whenever possible.

The problem with saving calculated values in a transaction oriented system is because if it is possible to add rows to the "past" or modify the "past", that totally messes up rolling averages and running sums. Use that as your guide. If any component of the calculation could change, you can't store the calculated value. That leaves us with how to do this most efficiently, The answer to that is with a report. The rolling average is problematic. You would need to go back to the beginning of time to calculate it even if the user just wanted to see last month's data.

With very little db experience, this is really useful information for me. The running sums play the role of database checksum, it detects if data has been changed
I am gonna redesign my table for saving calculated data....

Thank you!

 

[Pat Hartman]

Good move