Let’s say I have an item A.
- A was ordered at $10/pc with qty of 100
- A was then ordered at $11/pcs with qty of 50
I have a table “Order” which keep track of qty and unit price at which the item is ordered.
I made first shipment of 70 which is recorded in table “Shipment”
I would like to have a summary table grouped by Item code. In this table, it will show that I still have to deliver 30pcs at $10/pc and another 50pcs at $11/pc.
How can I do that?