Query for Last Inventory Count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carol Purcell - 26 May 2005 17:27 GMT

I am trying to track inventory of products in multiple warehouses. I need
to keep a running total at each warehouse so I can view and analyze
historic usage at each site.

To achieve this, I set up an inventory table for each warehouse. For my
question, I'll call them Warehouse A, B and C.

The inventory reports come from each warehouse at different intervals and
on different days.

The query I want to build would basically ask the data base this question:
What is the MOST RECENT quantity recorded at "Warehouse A" PLUS "Warehouse
B" PLUS "Warehouse C" for each Widget.

(Widget Number is the primary key for the main table and is a foreign key
in each warehouse table).

My goal is to create and inventory form (screen) that would show people the
latest quantity in each warehouse and a calculated total of all available.

If you have a suggestion for me, note that I am a new user and would
appreciate "simple language" (I was a Mac user in my previous life) if
possible.

Reply to this Message

JLamb - 26 May 2005 18:31 GMT

Well my first suggestion is that you don't do a table for each warehouse.
Just do one table that has a field like Location or Warehouse. Then you can
look at your inventory in any particular Warehouse or at a particular
product, etc.

Then I would do one query to identify for each location what the "Latest
Date" is by pulling the location code and your date field and grouping and
taking the max of the date. Then you can join this into your main query to
restrict your rows and just list out the item no. and qty (?) grouped and
summed.

> I am trying to track inventory of products in multiple warehouses. I need
> to keep a running total at each warehouse so I can view and analyze
[quoted text clipped - 19 lines]
> appreciate "simple language" (I was a Mac user in my previous life) if
> possible.

Reply to this Message

Carol Purcell - 27 May 2005 21:19 GMT

I've done as you've suggested -- I now have one inventory table set up like
this:
InvID(PrimaryKey)
ItemNumber(ForeignKey)
WarehouseLocation(List of 5 choices)
DateOfCount
QtyOnHand

I want to be able to make a query that asks "what was the quantity on hand
on the LAST date of count and Warehouse Location A?" I would like the
results to show the "Item Number, Quantity on Hand, and the Warehouse
Location."

My instincts tell me to bring in the Item Number, Warehouse Location, Date
of Count, Quantity on Hand -- and then group by MAX under the date. But
this doesn't work at all. The MAX date group works fine IF I remove the
quantity on hand -- and that defeats the purpose.

This makes me believe I need to do two quiries. Do you have any ideas? Any
help is greatly appreciated!!!

Reply to this Message