Average Inventory

  • Thread starter Thread starter hotplate
  • Start date Start date
H

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?
 
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
 
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,
Click to expand...
I am trying to find the average monthly dollar amount in inventory.
Click to expand...
I have five fields that represent:
Click to expand...
date in
date out
quantity
cost of part
part
Click to expand...
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.
Click to expand...
Does anyone have an example of how to do this?
Click to expand...
Click to expand...

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