A
A. T.
Thank you to MGFoster for referencing MSKB 138911. This looks as if it could
contain a partial solution but it appears I will need a sequentially
numbered field in the query (or a way to access the corresponding record
number in a form based on the query) in order to DSum individual purchase
quantities within each item type. The catch is that this numbered field must
be based on the query sort order because there is no way to create a
meaningful number series based on any of the underlying tables.
<ORIGINAL MESSAGE>
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
contain a partial solution but it appears I will need a sequentially
numbered field in the query (or a way to access the corresponding record
number in a form based on the query) in order to DSum individual purchase
quantities within each item type. The catch is that this numbered field must
be based on the query sort order because there is no way to create a
meaningful number series based on any of the underlying tables.
<ORIGINAL MESSAGE>
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