Average Inventory

[hotplate]

Hi,

I am trying to find the average monthly dollar amount in inventory.

I have five fields that represent:

date in
date out
quantity
cost of part
part

Do I have to keep a daily log of a dollar amount in inventory? Some
parts may enter inventory in one month and leave in another, and some
parts may still be in inventory so the date out will be empty.

Does anyone have an example of how to do this?

 

[John Spencer MVP]

I would handle this by having an additional table that contains at a minimum
dates for all the dates in your range. So Calendar table with at least one
field TheDate.

Now you can join that table to your inventory table

SELECT Year(Calendar.TheDate)
, Month(Calendar.TheDate)
, Avg(Quantity * [Cost of Part]) as AvgCost
FROM Inventory INNER JOIN Calendar
On Calendar.TheDate >= Inventory.[Date In]
AND Calendar.TheDate <= Nz(Inventory.[Date Out],Date())
Group By Year(Calendar.TheDate)
, Month(Calendar.TheDate)

You could optionally specify a period by using a Where Clause
SELECT Year(Calendar.TheDate)
, Month(Calendar.TheDate)
, Avg(Quantity * [Cost of Part]) as AvgCost
FROM Inventory INNER JOIN Calendar
On Calendar.TheDate >= Inventory.[Date In]
AND Calendar.TheDate <= Nz(Inventory.[Date Out],Date())
WHERE Calendar.TheDate Between #2009-01-01# and #2009-01-31#
Group By Year(Calendar.TheDate)
, Month(Calendar.TheDate)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[hotplate]

Thanks, that put me in the right direction and I figured it out from
there!

I would handle this by having an additional table that contains at a minimum
dates for all the dates in your range.  So Calendar table with at leastone
field TheDate.

Now you can join that table to your inventory table

SELECT Year(Calendar.TheDate)
, Month(Calendar.TheDate)
, Avg(Quantity * [Cost of Part]) as AvgCost
FROM Inventory INNER JOIN Calendar
On Calendar.TheDate >= Inventory.[Date In]
AND Calendar.TheDate <= Nz(Inventory.[Date Out],Date())
Group By Year(Calendar.TheDate)
, Month(Calendar.TheDate)

You could optionally specify a period by using a Where Clause
SELECT Year(Calendar.TheDate)
, Month(Calendar.TheDate)
, Avg(Quantity * [Cost of Part]) as AvgCost
FROM Inventory INNER JOIN Calendar
On Calendar.TheDate >= Inventory.[Date In]
AND Calendar.TheDate <= Nz(Inventory.[Date Out],Date())
WHERE Calendar.TheDate Between #2009-01-01# and #2009-01-31#
Group By Year(Calendar.TheDate)
, Month(Calendar.TheDate)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I am trying to find the average monthly dollar amount in inventory.

I have five fields that represent:

date in
date out
quantity
cost of part
part

Do I have to keep a daily log of a dollar amount in inventory?  Some
parts may enter inventory in one month and leave in another, and some
parts may still be in inventory so the date out will be empty.

Does anyone have an example of how to do this?

Thanks, that put me in the right direction and I figured it out from
there!