How to Find the Date of Last Change in Inventory

[doyle60]

If I have a table that lists a Style,Color, InventoryUnits, and
DateOfLastInventory, how can I find the last change in inventory? That
is, I wish to find Inventory that isn't moving.

For example, let's say I have the following in a table:

Style Color Units Date
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

I wish to get:

Style Color Units Days
1912 White 200 45

Or 45 days between 5/1/ and 6/15 (though it could obviously be more
days, that is the number I want).

And if I have this:

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 wish to get:

Style Color Units Days
2012B Pink 111 90

And if I have just two dates *with* a change in inventory, or if I have
only one record for a style/color, it should return 0 days.

Thanks,

Matt

 

[Guest]

I don't quite get it. I have some questions:
Are all the items in the same table?
Do you want the highest number of days for all items, or by Style, or by
Style and Color?
How do you come up with 90 days in your second example when the date is
6/15/2005? The differenece between the 5/1/2005 and 6/15/2005 is actually 44
days.
What do you mean by two dates with a change in inventory? I don't see
anything in your example that indicates a "change" in inventory. Are you
saying the date in the record represents an inventory change? That doesn't
seem likely, because many of the records seem to have the same number of
units.
Please post back with some specifics so we can have a crack at it.

 

[doyle60]

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

 

[Guest]

Thanks for the detailed info. I think I understand it now. The problem is,
I don't think a query will do what you want. It will take a combination of
one (or more, maybe) queries, and some VBA code. Let me see if I can at
least give a high level concept, then assist with answers as we move forward.

first, I would create a query that will return only a Style Color
combination with Group By's on both, so what it will return will be a unique
list of Style/Color combinations.

Then I would create another query that will return the data you need for the
calculations. This query should use the fields in the first query as
parameters. It should also be ordered by date in descending order (most
recent date first).

Now the control logic should loop through each row in the first query, look
at the rows in the second query to do the calculations. The control logic
should be something like this (WARNING! - untested "air" code)

Set qdf = Currentdb.QueryDefs("qselColorStyle")
Set rstItems = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
Do While Not rstItems.EOF
Set qdf = Currentdb.QueryDefs("qselInventory")
qdf.Parameters(0) = rstItems![Style]
qdf.Parameters(1) = rstItems![Color]
Set rstInventory = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
dtmLastDate = rstInventory![date]
intCount = rstInventory![Units]
rstInventory.MoveNext
If rstInventory.EOF Then
'No movement here
End If
Do While Not rstInventory.EOF
If rstInventory!Units = intCount Then
dtmFirstDate = rstInventory!Units
Else
'Now you have the count and dates to calculate
Exit Do
End If
rstInventory.MoveNext
Loop
rstInventory.Close
rstItems.MoveNext
Loop

So there is the basic form. Now, some of the special issues will have to be
Handled differently, but once you get this far, we can work out the rest.
Post Back if you have questions. I will not be back on until Monday.
Good Luck

 

[doyle60]

Thanks. I have done the following, as you suggested:

SLOBInventorytbl:
The table which holds all the data. The keys are Style, Color,
SLOBDate.

SlobInv1StyColqry:
I created this query which groups on Style and Color. It is based on
SLOBInventorytbl.

SLOBInv2StyColDatUntqry:
I created this query, based on SLOBInventorytbl, with these fields in
this order: Style, Color, SLOBDate, OnHand. (OnHand (Units) is the
value, a quantity of units in the warehouse.) There are no "Grouped
By"s on any of these fields because the table's keys make it
superfluous. The Style and Color fields are marked Ascending. The
SLOBDate field is marked Descending, as you instructed.

I'm not sure what to do with your code. But I did rename the fields.
Here it is now:

Set qdf = Currentdb.QueryDefs("SlobInv1StyColqry")
Set rstItems = qdf.OpenRecordset(dbOpenSnapsh­ot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
Do While Not rstItems.EOF
Set qdf = Currentdb.QueryDefs("SLOBInv2StyColDatUntqry")
qdf.Parameters(0) = rstItems![Style]
qdf.Parameters(1) = rstItems![Color]
Set rstInventory = qdf.OpenRecordset(dbOpenSnapsh­ot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
dtmLastDate = rstInventory![SLOBDate]
intCount = rstInventory![OnHand]
rstInventory.MoveNext
If rstInventory.EOF Then
'No movement here
End If
Do While Not rstInventory.EOF
If rstInventory!OnHand = intCount Then
dtmFirstDate = rstInventory!Units
Else
'Now you have the count and dates to calculate
Exit Do
End If
rstInventory.MoveNext
Loop
rstInventory.Close
rstItems.MoveNext
Loop
__________________________________

Thanks,

Matt

 

[Guest]

The code should go where ever you want to get the information. Since I don't
know how your app is constructed, it would be hard to say. My first guess is
it would be a command button and the code in the Click Event.

It would also depend on how you want to present the data.

Be aware that the code I posted may not work as we expected, and may need
some tweeking to get exactly what you are after. I wrote it on the fly with
no way to test it.

 

[doyle60]

I would like to get the results in a query. That way I can add back
some data from the SLOBInventorytbl. If I can't get the results in a
query, I'd like them pasted to a table, with the old data being thrown
out first. I do that type of thing a lot, but with queries and macros,
not VBA---eash!

The ultimate idea is to get it into a report---all of which I know how
to do.

By the way, just to see what would happen, I threw your code in a
command button and got many red lines, and compile errors. It doesn't
seem to like the question mark in:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh­?ot,

But maybe it's another issue.
It also seems to have line continuation problems.

These individual lines turn red:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh­?ot,
dbReadOnly)

("SLOBInv2StyColDatUntqry")

(dbOpenSnapsh?ot, dbReadOnly)

If rstInventory
!OnHand =
intCount Then
_____________________________

Is it possible to have this as a function and to introduce it into a
query? Or is it that we have to let the code run and place data into a
table. I do not wish to have the data only appear in a report. The
data should be available for me to see in a query or table.

Thanks so much,

Matt

 

[Guest]

See responses below:

I would like to get the results in a query. That way I can add back
some data from the SLOBInventorytbl. If I can't get the results in a
query, I'd like them pasted to a table, with the old data being thrown
out first. I do that type of thing a lot, but with queries and macros,
not VBA---eash!

The ultimate idea is to get it into a report---all of which I know how
to do.

By the way, just to see what would happen, I threw your code in a
command button and got many red lines, and compile errors. It doesn't
seem to like the question mark in:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?ot, See below

But maybe it's another issue.
It also seems to have line continuation problems.

This is a problem with the text here. It wraps lines that were intended to
be one line.

These individual lines turn red:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?ot,
dbReadOnly)

This should be one line and should read:
Set rstItems = qdf.OpenRecordset("SLOBInv2StyColDatUntqry",dbOpenSnapshot,
dbReadOnly)

("SLOBInv2StyColDatUntqry")

(dbOpenSnapsh?ot, dbReadOnly)

If rstInventory
!OnHand =
intCount Then All One Line
_____________________________

Is it possible to have this as a function and to introduce it into a
query? Or is it that we have to let the code run and place data into a
table. I do not wish to have the data only appear in a report. The
data should be available for me to see in a query or table.

You could use a query, but getting it from the query to the report would be
an issue. Perhaps a temporary table that the report could use would be the
best solution.

 

[doyle60]

So I changed that one spill over line and it seems to work. The other
lines I mentioned that are red are still red. When I run the code, it
gives me a compile error on "qdf =" in the first line.

<You could use a query, but getting it from the query to the report
would be
an issue.>

Really? I don't know why myself. Once I have a query, I'm usually
home free.

< Perhaps a temporary table that the report could use would be the
best solution. >

I have no problem with a table that holds the following data:

Style, Color, LastSLOBDate, SLOBDays.

Where the LastSLOBDate would be the most recent date for the
Style/Color, and where SLOBDays are the value we are looking for here.


I technically don't need LastSLOBDate here; I can get it later (and
filter out those style/colors that do not have the latest date---that
is, the inventory is null, sold out.

By the way, SLOB stand for Slow Moving and Obsolete (Inventory).

What should I do next? I'm sort of stuck here.

Matt

 

[Guest]

If theses are the lines you are talking about, I don't know where they came
from in relation to the code I sent.

If your report has visibility to the query, then the query is the way to go.
If you could post the code as you modifided it, the lines that turn red, and
the error message the lines are returning, maybe we can work through it.

 

[doyle60]

< If theses are the lines you are talking about, I don't know where
they came
from in relation to the code I sent.>

Two things I don't understand. First, who changed my original message
dated Jun 13, 9:24 am? The code I pasted there (with name changes) was
further edited and has blue writing in it to boot. I thought it was
you. I was a bit confused that my post was edited (looking as if I
wrote it) but thought it was some trick of this new google list. I
don't know why there is blue writing in there or who edited it.

Secondly, when I copy your code here and paste it into Access, it
changes it. For example, when I copy the line:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?ot, dbReadOnly)

and paste into into Access, it turns it into this:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?-ot, dbReadOnly)

putting a hyphen in the word "snapshot." But when I copy this line,
with the hyphen, into this page, it actually doesn't copy the hyphen.

I've never had these issues before.

< If your report has visibility to the query, then the query is the way
to go. >

I don't have a report yet, nor a query. I first need a query or, as
you suggested, a table. My goal is to have this table look something
like this:

Style Color SLOBDate SLOBDays
1912 White 06/10/05 20
1912 Black 06/10/05 90
1912 Pink 06/10/05 120
2012 White 06/10/05 20
2012 Gray 06/10/05 240

The SLOBDate may appear a bit useless, but I think it best to have it.

< If you could post the code as you modifided it, the lines that turn
red, and
the error message the lines are returning, maybe we can work through
it. >

Okay, here is your original code, with the new names:

Set qdf = CurrentDb.QueryDefs("SLOBInv1StyColqry")
Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?ot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
Do While Not rstItems.EOF
Set qdf = CurrentDb.QueryDefs("SLOBInv2StyColDatUntqry")
qdf.Parameters(0) = rstItems![Style]
qdf.Parameters(1) = rstItems![Color]
Set rstInventory = qdf.OpenRecordset(dbOpenSnapsh?ot,
dbReadOnly)
rst.MoveLast
rst.MoveFirst
dtmLastDate = rstInventory![SLOBDate]
intCount = rstInventory![OnHand]
rstInventory.MoveNext
If rstInventory.EOF Then
'No movement here
End If
Do While Not rstInventory.EOF
If rstInventory!OnHand = intCount Then
dtmFirstDate = rstInventory!OnHand
Else
'Now you have the count and dates to calculate
Exit Do
End If
rstInventory.MoveNext
Loop
rstInventory.Close
rstItems.MoveNext
Loop
________________________________

This code gets a varible not defined on "qdf =" and does not have any
red lines.

Could you explain what you want me to do with the code once it is done.
I haven't a clue what to do with it, frankly.

The way I see it now, I would like a button on a form that creates a
table with the data I need. The table being cleared first and
appended.

I'm sorry about these oddities. I hope you stick with it.

Matt

 

[Guest]

That is weird. Those anomolies created some of the errors in the code. I
fixed it below. I am suprised you did not get the undefined variable message
on rstItems or rstInventory. This should go at the top of your procedure:
dim rstItems as Recordset
dim rstInventory as Recordset
dim qdf as QueryDef

Also notice there is a line that doesn't have the code to complete everything.
'Now you have the count and dates to calculate
This is where you would put the code necessary to do your calculations.
There are no Question marks or hyphens in the code.

< If theses are the lines you are talking about, I don't know where
they came
from in relation to the code I sent.>

Two things I don't understand. First, who changed my original message
dated Jun 13, 9:24 am? The code I pasted there (with name changes) was
further edited and has blue writing in it to boot. I thought it was
you. I was a bit confused that my post was edited (looking as if I
wrote it) but thought it was some trick of this new google list. I
don't know why there is blue writing in there or who edited it.

Secondly, when I copy your code here and paste it into Access, it
changes it. For example, when I copy the line:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?ot, dbReadOnly)

and paste into into Access, it turns it into this:

Set rstItems = qdf.OpenRecordset(dbOpenSnapsh?-ot, dbReadOnly)

putting a hyphen in the word "snapshot." But when I copy this line,
with the hyphen, into this page, it actually doesn't copy the hyphen.

I've never had these issues before.

< If your report has visibility to the query, then the query is the way
to go. >

I don't have a report yet, nor a query. I first need a query or, as
you suggested, a table. My goal is to have this table look something
like this:

Style Color SLOBDate SLOBDays
1912 White 06/10/05 20
1912 Black 06/10/05 90
1912 Pink 06/10/05 120
2012 White 06/10/05 20
2012 Gray 06/10/05 240

The SLOBDate may appear a bit useless, but I think it best to have it.

< If you could post the code as you modifided it, the lines that turn
red, and
the error message the lines are returning, maybe we can work through
it. >

Okay, here is your original code, with the new names:

Set qdf = CurrentDb.QueryDefs("SLOBInv1StyColqry")
Set rstItems = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
Do While Not rstItems.EOF
Set qdf = CurrentDb.QueryDefs("SLOBInv2StyColDatUntqry")
qdf.Parameters(0) = rstItems![Style]
qdf.Parameters(1) = rstItems![Color]
Set rstInventory = qdf.OpenRecordset(dbOpenSnapshot,
dbReadOnly)
rst.MoveLast
rst.MoveFirst
dtmLastDate = rstInventory![SLOBDate]
intCount = rstInventory![OnHand]
rstInventory.MoveNext
If rstInventory.EOF Then
'No movement here
End If
Do While Not rstInventory.EOF
If rstInventory!OnHand = intCount Then
dtmFirstDate = rstInventory!OnHand
Else
'Now you have the count and dates to calculate
Exit Do
End If
rstInventory.MoveNext
Loop
rstInventory.Close
rstItems.MoveNext
Loop
________________________________

This code gets a varible not defined on "qdf =" and does not have any
red lines.

Could you explain what you want me to do with the code once it is done.
I haven't a clue what to do with it, frankly.

The way I see it now, I would like a button on a form that creates a
table with the data I need. The table being cleared first and
appended.

I'm sorry about these oddities. I hope you stick with it.

Matt

 

[doyle60]

I added the three lines to the top. I now get a compile error,
variable not defined, on the line:

rst.MoveLast

< Also notice there is a line that doesn't have the code to complete
everything.
'Now you have the count and dates to calculate >

I am sorry to say that I don't know what to do here. Do you know that
your dealing with panties?, yes, these "units" are thongs and strings
and bikinis.

Anyway, thanks so much for sticking to this.

Matt

 

[Guest]

Bikini! Thong! So, what's in it for me?

The rst. should be rstItems or rstInventory, depending on which record set
you are dealing with.

Okay, once we get this debugged and running without falling on its face, we
can worry about the calculation part.

 

[doyle60]

I should explain that I put the code on the double click event of a
command button and when I double click it, it gives me the compile
error, variable not defined, on the line:

rst.MoveLast

That is to say, I do not get the compile error when viewing the code
and pressing the compile button.

I should tell you that the only VBA code I know how to write is your
basic If--Else Statements to make things visible or not, draw lines,
etc. I also do ones that requery, and fill in data given certain
criteria. But some are a bit more compicated than that. At times, I
can tweek complicated code I don't fully understand. But other than
that, I'm at your mercy on this one. I have no idea how to complete
this code or implement it.

I hope we can get it into some table.

Thanks,

Matt

 

[Guest]

Matt,

I think the best solution for you is to find someone local who can look over
your shoulder and give you a hand. I really believe this one is too complex
to work out via these posts. If I had your database in front of me, I could
certainly solve this problem, but please understand I am like a blind NASCAR
driver trying to make it around the track with instructions through a radio.

You information has been complete and clear, it is just the complexity of
the situations.

Maybe at this point, your VBA is limited, but none of us were born knowing
it. This may be a good opportunity for you to enjoy a learning experience.

Best of Luck to you.