Pivot Table Calculating totals differently

[Guest]

I have set up an ACCESS database for inventory purposes. I pull a massive
query into a pivot table. I have inserted some calculated fields. One of
these calculated fields is "$Inv" which is calculating #received/$received.
My problem comes when that specific inventory is gone and I am trying to
calculate what my total dollars on hand is. It refers back to the weighted
inventory and not to actual. I need to come up with some type of FIFO
solution or something. Below is a list of some calculated fields that I use.
Any suggestions would be great.

Avg Price =SUM('$ Received')/SUM('#received')
Curr Inventory=SUM('#received' )-SUM('#use' )-SUM(waste )
$Inv='Curr Inventory' *'Avg Price'
Average cost=AVERAGE(Price )

Thanks
Zenia

 

[rsenn]

If you are going to switch from a perpetual moving average system to
FIFO system you'll likely have to give each FIFO layer a unique id, an
make that part of your pivot table, and do your calculations not o
product totals but on FIFO layer totals