I'm afraid that in trying to follow the numbers/quantities that I don't get the logic of things like how you are 300 short when you need 900 but have 800 on hand. No matter; the problem reminds me of a similar situation where I had to perform calculations on fields between rows and include/omit rows in the report based on the calculation.
My solution was to put the primary records into a temp table, then in a function, move through the rows, then
- assign value on row 1 to a variable, move next and get the value
- assign value on row 2 to a variable and do the calculation
- if it met the test, move previous and set the flag (checkbox field) to true then move(1)
- the top of the loop moved next, reset the variables and the comparisons started over
- the report was filtered on true flags
- the function returned true/false if all went well/didn't
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.