How to create a "point in time" calculation that is date stamped

[Joe]

I have an inventory tracking database that records items going from
one job to another job on different dates. I would like to capture
the total amount of inventory that is available at a “point in time”.

For example, I have a job code XYZ and I add 500 units of inventory
part # 123 on 7/1/09. On 7/15/09 I subtract 200 units of part #123
and then on 7/20/09 I add back 100 units of the same part #.

At the end of the month I would like to run a report that shows what
the inventory balance was on 7/15 and 7/20. In summary, each time the
inventory amount changes I would like to record the new balance and
the “as of” date that the balance was there for.

Ultimately I will use the “point in time” balances as part of a larger
calculation to determine what the cost is of holding that inventory.

I have an inventory tracking table. Not sure if there is a way to
place a calculated value in there or if there is a query that can
capture and determine what the new balance is?

Thanks

 

[Joe]

I have an inventory tracking database that records items going from
one job to another job on different dates.  I would like to capture
the total amount of inventory that is available at a “point in time”.

For example, I have a job code XYZ and I add 500 units of inventory
part # 123 on 7/1/09.  On 7/15/09 I subtract 200 units of part #123
and then on 7/20/09 I add back 100 units of the same part #.

At the end of the month I would like to run a report that shows what
the inventory balance was on 7/15 and 7/20.  In summary, each time the
inventory amount changes I would like to record the new balance and
the “as of” date that the balance was there for.

Ultimately I will use the “point in time” balances as part of a larger
calculation to determine what the cost is of holding that inventory.

I have an inventory tracking table.  Not sure if there is a way to
place a calculated value in there or if there is a query that can
capture and determine what the new balance is?

Thanks

I need to add a different post to address a larger issue around
this....