Re: How to Find the Date of Last Change in Inventory

Thanks for your interest.

Every so often I run a ODBC query that places our current inventory
(from a MainFrame) into an access table. I do not do it in regular
intervals, maybe once a week, twice a month or montly---there is no
rhyme and reason there. I usually simply replace the old inventory
with the new data. But now I want to save it all in one table, adding
a date at the end of each record, indicating the date I "took" the
inventory. The table will grow and grow, and from this table I want to
find Stagnant Stock, Idle Inventory, that is, what is not moving,
changing. I want to put a day value on that: How many days has it
remainded the same.

I called the date "DateOfLastInventory" in my opening sentence but only
called it "Date" in the examples (this may have been confusing). Here,
we will call it DateOfInventory.

So if a small section of the table has this (the Keys being Style,
Color, and DateOfInventory):

Style Color Units DateOfInventory
1912 White 200 01/25/05
1912 White 50 02/28/05
1912 White 38 03/15/05
1912 White 200 05/01/05
1912 White 200 06/01/05
1912 White 200 06/15/05
2012B Pink 415 01/25/05
2012B Pink 248 02/28/05
2012B Pink 111 03/15/05
2012B Pink 111 05/01/05
2012B Pink 111 06/01/05
2012B Pink 111 06/15/05

I want to get this:

Style Color Units Days
1912 White 200 44
2012B Pink 111 92

The 44 is the difference between 6/15 (the last day the inventory was
taken) and 5/01 (the last time the inventory was the same value of the
last day it was taken). Notice that the 1/25/05 inventory was 200 as
well. But that should not affect our results; there was movement
between that 200 and the 5/01 200.

The 92 days is the difference between 6/15 and 3/15 (the last day the
inventory was the same as 6/15, the last time inventory was taken).

If these are the records for style 2012G in our table:

2012G Pink 111 03/15/05
2012G Pink 111 05/01/05
2012G Pink 111 06/01/05
2012G Pink 50 06/15/05

Then the query should yield this for that style:

Style Color Units Days
2012G Pink 50 0

Because there was a change, the inventory is moving, is active.

If this is the record for 20555

20555 Grey 111 01/01/05
20555 Grey 50 03/15/05

Then the query should yield nothing. That is, it has to know that the
last inventory was taken on 6/15 and that if there is no data here for
6/15, it was all sold and gone. (Such Style/Colors can be deleted with
a previous query.)

Anway, that is the idea. The values are what the inventory actually is
at each point it is taken. That is, the Units above are what is
actually in the warehouse at that given date.

Again, thanks for your interest.

Direct answers to your questions:
All items are in the same table.
The answers should be by Style and Color. That is I should get a
separate result for "1912 Grey" and "1912 Black"---one result each.
I was just rounding with the 990 and 44 days---too lazy to find the
true figues, didn't think anyone would notice.

Matt

.

Relevant Pages

  • Re: How to Find the Date of Last Change in Inventory
    ... I don't think a query will do what you want. ... I usually simply replace the old inventory ... > we will call it DateOfInventory. ... > Style Color Units DateOfInventory ...
    (microsoft.public.access.modulesdaovba)
  • How to Find the Date of Last Change in Inventory
    ... DateOfLastInventory, how can I find the last change in inventory? ... Style Color Units Date ... 2012B Pink 415 01/25/05 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Need Help designing a table structure
    ... Well, I don't really know anything about ColdFusion, but you could do what ... form with fields that represent the fields from the inventory table, ... The form which is unbound can call another form ... which is bound using that query as the record source. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Show all records on a joined query with criteria
    ... > inventory items to the order. ... " created one query that has 2 tables joined showing ... SQL View from the menus, View> SQL View, and copy and past the SQL ... MyTableOneID AUTOINCREMENT PK ...
    (microsoft.public.access.queries)
  • Re: parts inventory management
    ... You might also want to look over the MS template for inventory management. ... PurchaseOrderNum ... TblPurchaseOrderDetail ... The query would include ...
    (microsoft.public.access.tablesdbdesign)
Loading