Best approach for running calculation on report details?

[Evilferret]

I have a report that needs to display the following information:



(1) Original quantity required for purchase order
(2) Quantity of goods shipped toward purchase order
(3) Quantity of goods on shelf for this purchase order
(4) Quantity of goods still to be produced

(1) The original quantity required for the purchase order is a field in my database. Will be easy to display.

(2), (3), and (4) are not fields in my database. I have a total quantity shipped and total quantity produced, but the shipments aren't associated with specific purchase orders. I'd like to loop through my records to build a temp table when the report is generated. The temp table will apply the parts that have been shipped to outstanding purchase orders, starting with the oldest purchase order.

Where would I implement the code to generate the temp table? Should it be within the report itself, should it be an event that triggers when the button is pressed for report generation?

Right now I'm looking at using two DAO.Recordsets (One containing parts shipped, one containing purchase orders) and looping through each to apply the parts shipped to the Purchase Orders. But this approach seems painfully inefficient.

I would like to be able to do something along the lines of doing a Sum() of the quantities on all purchase orders grouped by Part Number, then doing a running tally of quantities as the Purchase Orders (Details) come up in the report, and suppressing any purchase orders where the running tally is less than the Sum() of quantity shipped... However I have no idea how to proceed with this or where the code would go.

[June7]

Temp table code would be behind the button.

Maybe subreports would produce what you want.

How can a shipment not be traced to purchase order(s)?