A
A. T.
I am having trouble finding a way to analyze inventory data. Here's the
situation. I have data which shows all purchases during the year (including
data showing this same information which was still in inventory from the
previous years). I also have data on the count of inventory which was left
in stock at the end of the year. In order to properly value this inventory I
need a way to show how much of each purchase is still in stock. This means
that the quantities of each purchase must be sequentially subtracted from
the stock left in inventory until there is no more stock to account for.
I have created a query which accounts for all the purchased items and will
calculate the extended value for each item but I haven't found a way to keep
a cumulative total of how much of the count of stock in inventory is
remaining as each individual purchase is deducted from that count.
I do have the inventory in the query sorting for each individual item along
with invoice dates and invoice numbers which will allow me to process the
data in the correct order to meet my need to output a First In First Out
(FIFO) analysis.
I have tried writing a VBA function either with a module level variable or a
static variable which I thought might have worked but Access doesn't
recognize it as an "Aggregate Function".
Any Ideas? Thank you very much.
A T
situation. I have data which shows all purchases during the year (including
data showing this same information which was still in inventory from the
previous years). I also have data on the count of inventory which was left
in stock at the end of the year. In order to properly value this inventory I
need a way to show how much of each purchase is still in stock. This means
that the quantities of each purchase must be sequentially subtracted from
the stock left in inventory until there is no more stock to account for.
I have created a query which accounts for all the purchased items and will
calculate the extended value for each item but I haven't found a way to keep
a cumulative total of how much of the count of stock in inventory is
remaining as each individual purchase is deducted from that count.
I do have the inventory in the query sorting for each individual item along
with invoice dates and invoice numbers which will allow me to process the
data in the correct order to meet my need to output a First In First Out
(FIFO) analysis.
I have tried writing a VBA function either with a module level variable or a
static variable which I thought might have worked but Access doesn't
recognize it as an "Aggregate Function".
Any Ideas? Thank you very much.
A T