Pivot Table Calculating totals differently

E

esparzaone

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top