How to Find First Record With Same Value as Last Record

[doyle60]

Klatuu has been kind enough to help me in my previous postings, titled
"How to Find the Date of Last Change in Inventory." But seems to think
it too difficult to do here. I am hoping someone else here may have a
surprisingly elegant solution or be willing to work out the
difficulties.

The problem is this: Let's say I have a table that holds the inventory
Units every date I take the inventory. So in the below table, I have
taken the inventory data on 3/15, 3/30, 4/6, 4/15 and 5/30. I store
this information along with many other Styles and Colors (one style can
come in many colors) in a table. The keys being Style, Color and Date.


Style Color Units Date
1912 White 200 03/15/05
1912 White 50 03/30/05
1912 White 34 04/06/05
1912 White 200 04/15/05
1912 White 200 05/30/05

How do I get back the amount of days that the Style/Color stayed the
same value as the last Date? In other words, we are aging inventory.
How many days was 1912 White that 200 units it was on the last date of
inventory 5/30/05? The answer I want is the difference between 4/15/05
(when it was still 200) and 5/30 (the last day of inventory). That 34
units of 04/06/05 is the stopper. So the answer should be 45 days.

My goal is to get this data into a table by pressing a command button.
The table should look like this (adding in some other styles and
Colors):

Style Color Units Days LastDate
1912 White 200 45 05/30/05
1912 Black 2,400 54 05/30/05
2012 White 345 45 04/15/05

etc.

If it's in a table, I can work with the data a bit more, adding back
some filters and Descriptions of the product.

I write If--Else statements and a bit more complicated, but not that
complicated, VBA all day long, but not this type of thing. I will need
guidance throughout.

Thanks so much,

Matt

 

[David C. Holley]

You may want to add another field to the underlying table that captures
that lastInventoryCount and lastInventoryDate. From there add code so
that when a new inventory count is added, Access goes back looks as the
last date the count was added and brings that value forward. Then its
just a matter of looking at the difference between the inventoryCount
and lastInventoryCount to find variances.

Product InvDate InvCount LastInvDate LastInvCount
23423 3/15/2005 200 2/15/2005 200
23423 3/30/2005 50 3/15/2005 200
23423 4/6/2005 34 3/30/2005 50
23423 4/15/2005 200 4/6/2005 34
23423 5/30/2005 200 4/15/2005 200

I'm guessing that will do what you want.

David H

 

[doyle60]

Yes, I think I get it. Compare two at a time, and do it after each
inventory is taken. That way I deal with a finite number, 2, and not
some looping infinitive. This is the sort of elegance I was looking
for. I'll try it. Thanks.

Matt

 

[SteveS]

Klatuu has been kind enough to help me in my previous postings, titled
"How to Find the Date of Last Change in Inventory." But seems to think
it too difficult to do here. I am hoping someone else here may have a
surprisingly elegant solution or be willing to work out the
difficulties.

The problem is this: Let's say I have a table that holds the inventory
Units every date I take the inventory. So in the below table, I have
taken the inventory data on 3/15, 3/30, 4/6, 4/15 and 5/30. I store
this information along with many other Styles and Colors (one style can
come in many colors) in a table. The keys being Style, Color and Date.


Style Color Units Date
1912 White 200 03/15/05
1912 White 50 03/30/05
1912 White 34 04/06/05
1912 White 200 04/15/05
1912 White 200 05/30/05

How do I get back the amount of days that the Style/Color stayed the
same value as the last Date? In other words, we are aging inventory.
How many days was 1912 White that 200 units it was on the last date of
inventory 5/30/05? The answer I want is the difference between 4/15/05
(when it was still 200) and 5/30 (the last day of inventory). That 34
units of 04/06/05 is the stopper. So the answer should be 45 days.

My goal is to get this data into a table by pressing a command button.
The table should look like this (adding in some other styles and
Colors):

Style Color Units Days LastDate
1912 White 200 45 05/30/05
1912 Black 2,400 54 05/30/05
2012 White 345 45 04/15/05

etc.

If it's in a table, I can work with the data a bit more, adding back
some filters and Descriptions of the product.

I write If--Else statements and a bit more complicated, but not that
complicated, VBA all day long, but not this type of thing. I will need
guidance throughout.

Thanks so much,

Matt

Matt,

Interesting problem. Maybe this will help you get moving again.

Here is what I did.

1) Created a table "SLOB" with structure of

Table: SLOB


id Long Integer (autonumber - PK)
Style Text
Color Text
InvDate Date/Time
Units Integer


2) Created a temp table "SLOBTemp" with structure of

Table: SLOBTemp

Style Text
Color Text
InvDate_START Date/Time
InvDate_END Date/Time
Units Integer



3) Created a query "qryInvChg" with SQL of

SELECT SLOBTemp.Style, SLOBTemp.Color, SLOBTemp.Units, SLOBTemp.InvDate_START,
SLOBTemp.InvDate_END, [InvDate_END]-[InvDate_START] AS Days_Diff FROM SLOBTemp;


4) Created a form with one button to run the following code:

'****** begin code *******
Private Sub Command0_Click()
Dim rs As Recordset, rsTmp As Recordset
Dim strSQL As String
Dim varStyle As String
Dim varColor As String
Dim varUnits As Integer
Dim varDate1 As Date
Dim varDate2 As Date


' delete all records from temp table
DoCmd.SetWarnings False
CurrentDb.Execute "Delete * from SLOBTemp"
DoCmd.SetWarnings True

' next line should be one line
strSQL = "SELECT SLOB.Style, SLOB.Color, SLOB.InvDate, SLOB.Units FROM SLOB
ORDER BY SLOB.Style, SLOB.Color, SLOB.InvDate DESC;"

' open a recordset
Set rs = CurrentDb.OpenRecordset(strSQL)
' check for records
If rs.BOF And rs.EOF Then
rs.Close
Set rs = Nothing
MsgBox "no records"
Exit Sub
End If

rs.MoveLast
rs.MoveFirst

varStyle = ""
varColor = ""

Do While Not rs.EOF
If varStyle <> rs!Style And varColor <> rs!Color Then
varStyle = rs!Style
varColor = rs!Color
varUnits = rs!units
varDate1 = rs!invdate
varDate2 = varDate1

' loop while same color and style
Do While (varStyle = rs!Style And varColor = rs!Color And Not rs.EOF)
' check if units are the same
If rs!units = varUnits Then
varDate2 = rs!invdate
Else
Exit Do
End If
rs.MoveNext
If rs.EOF Then
rs.close
set rs = nothing
Exit Sub
End If
'varUnits = rs!units
Loop

' add a new record to the temp table
Set rsTmp = CurrentDb.OpenRecordset("SLOBTemp")
With rsTmp
.AddNew
!Style = varStyle
!Color = varColor
!units = varUnits
!InvDate_START = varDate2
!InvDate_END = varDate1
.Update
End With
rsTmp.Close
Set rsTmp = Nothing
End If
rs.MoveNext
Loop 'Until rs.EOF
rs.Close
Set rs = Nothing

' open the query to view results
DoCmd.OpenQuery ("qryInvChg")
End Sub
'**** end code *********

HTH

 

[doyle60]

Thanks Steve. I actually started building the same as above, but in a
query-macro model---one that avoids a lot of VBA---copying SQL written
with queries into Macro Run SQLs. But I will scrap it, as it is only
half finished, and try your method. Back to work tomorrow. Thanks so
much.

Matt

 

[doyle60]

Something is a bit wrong.

First, it only works for Styles that only have one color. If a style
comes in more than one color, it seems to return 0 in the query. I set
the key as you stated, one auto key, but technically it should be
Style, Color, InvDate.

So I temporarily changed the styles to see how it works besides.

There are some other problems. First, it seems to mess up when numbers
are the same in between dates. For example, when I have this:

ID Style Color InvDate Units
6 A05526 Chocolate 4/30/2005 1400
21 A05526 Chocolate 5/29/2005 100
12 A05526 Chocolate 5/30/2005 100
22 A05526 Chocolate 6/10/2005 1400

It gives me the correct answer in the SLOBTemp: 6/10, 6/10 and 1400.
But when it is the last style alphabetically, it returns 5/29, 5/30
and 100.

Secondly, for some reason this data (found in the middle
alphabetically):

ID Style Color InvDate Units
5 D002008 Pink Floral 4/30/2005 1000
11 D002008 Pink Floral 5/30/2005 100
17 D002008 Pink Floral 6/10/2005 100

is returning 5/30, 5/30 and 100, when it should be 5/30, 6/10 and
100. But a similar Style, only different in Style Name, but with three
lines exactly the same, actually gives the correct data.

I changed the names to:

SLOBTemptbl
SLOBtbl
SLOBInvChgqry

to keep with my naming rules. But please use the same names if easier
for you.

Thanks,

Matt

 

[SteveS]

Something is a bit wrong.

First, it only works for Styles that only have one color. If a style
comes in more than one color, it seems to return 0 in the query. I set
the key as you stated, one auto key, but technically it should be
Style, Color, InvDate.

So I temporarily changed the styles to see how it works besides.

There are some other problems. First, it seems to mess up when numbers
are the same in between dates. For example, when I have this:

ID Style Color InvDate Units
6 A05526 Chocolate 4/30/2005 1400
21 A05526 Chocolate 5/29/2005 100
12 A05526 Chocolate 5/30/2005 100
22 A05526 Chocolate 6/10/2005 1400

It gives me the correct answer in the SLOBTemp: 6/10, 6/10 and 1400.
But when it is the last style alphabetically, it returns 5/29, 5/30
and 100.

Secondly, for some reason this data (found in the middle
alphabetically):

ID Style Color InvDate Units
5 D002008 Pink Floral 4/30/2005 1000
11 D002008 Pink Floral 5/30/2005 100
17 D002008 Pink Floral 6/10/2005 100

is returning 5/30, 5/30 and 100, when it should be 5/30, 6/10 and
100. But a similar Style, only different in Style Name, but with three
lines exactly the same, actually gives the correct data.

I changed the names to:

SLOBTemptbl
SLOBtbl
SLOBInvChgqry

to keep with my naming rules. But please use the same names if easier
for you.

Thanks,

Matt

Sorry, I had an "AND" where I should have put an "OR".

The Primary Key doesn't matter in this case because the code is opening a
recordset based on one table and checking each record - style,color,inv_date

This is the data I used:

Style Color InvDate Units
1912 White 1/25/2005 200
1912 White 3/1/1938 50
1912 White 3/15/2005 38
1912 White 5/1/2005 200
1912 White 6/1/2005 200
2012B Pink 1/25/2005 415
2012B Pink 2/28/2005 248
2012B Pink 3/15/2005 111
2012B Pink 5/1/2005 111
2012B Pink 6/1/2005 111
2012B Pink 6/15/2005 111
2012G Pink 3/15/2005 111
2012G Pink 5/1/2005 111
2012G Pink 6/1/2005 111
2012G Pink 6/15/2005 90
1912 White 6/15/2005 200
D002008 Pink Floral 4/30/2005 1000
D002008 Pink Floral 5/30/2005 100
D002008 Pink Floral 6/10/2005 100
A05526 Chocolate 4/30/2005 1400
A05526 Chocolate 5/29/2005 100
A05526 Chocolate 5/30/2005 100
A05526 Chocolate 6/10/2005 1400


And this is the results:

Style Color Units InvDate_START InvDate_END Days_Diff
1912 White 200 5/1/2005 6/15/2005 45
2012B Pink 111 3/15/2005 6/15/2005 92
2012G Pink 90 6/15/2005 6/15/2005 0
A05526 Chocolate 1400 6/10/2005 6/10/2005 0
D002008 Pink Floral 100 5/30/2005 6/10/2005 11


If you don't want to see records where "days_diff" = 0, then add ">0" to the
criteria row for the column "days_diff" is the query SLOBInvChgqry.

'******* start revised code *************
Private Sub Command0_Click()
'On Error GoTo Err_Command0_Click '(***1)
Dim rs As Recordset, rsTmp As Recordset
Dim strSQL As String
Dim varStyle As String
Dim varColor As String
Dim varUnits As Integer
Dim varDate1 As Date
Dim varDate2 As Date
Dim i As Integer, k As Integer

DoCmd.SetWarnings False

CurrentDb.Execute "Delete * from SLOBTemptbl"

DoCmd.SetWarnings True

strSQL = "SELECT SLOBtbl.Style, SLOBtbl.Color, SLOBtbl.InvDate,
SLOBtbl.Units FROM SLOBtbl ORDER BY SLOBtbl.Style, SLOBtbl.Color,
SLOBtbl.InvDate DESC;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.BOF And rs.EOF Then
rs.Close
Set rs = Nothing
MsgBox "no records"
Exit Sub
End If

rs.MoveLast
rs.MoveFirst

varStyle = ""
varColor = ""

Do While Not rs.EOF
' should have been "OR" \/ not "AND"
If varStyle <> rs!Style Or varColor <> rs!Color Then
varStyle = rs!Style
varColor = rs!Color
varUnits = rs!Units
varDate1 = rs!InvDate
varDate2 = varDate1
Do While (varStyle = rs!Style And varColor = rs!Color And Not rs.EOF)
If rs!Units = varUnits Then
varDate2 = rs!InvDate
Else
Exit Do
End If
rs.MoveNext
If rs.EOF Then
Exit Sub
End If
'varUnits = rs!units

Loop

' save it to temp table
Set rsTmp = CurrentDb.OpenRecordset("SLOBTemptbl")
With rsTmp
.AddNew
!Style = varStyle
!Color = varColor
!Units = varUnits
!InvDate_START = varDate2
!InvDate_END = varDate1
.Update
End With
rsTmp.Close
Set rsTmp = Nothing
End If
rs.MoveNext
Loop 'Until rs.EOF

'Exit_Command0_Click: '(***2)
rs.Close
Set rs = Nothing
' display it
DoCmd.OpenQuery ("SLOBInvChgqry")

' Exit Sub '(***)

'Err_Command0_Click: '(***3)
' MsgBox Err.Description '(***)
' Resume Exit_Command0_Click '(***4)

End Sub
"******* end code ******************

I added error reporting, but commented it out - I don't know your button name.

If you want to use the error checking, change "Command0" in the 4 places marked
like '(***1) to the name of your button and uncomment the 6 lines that are
marked with '(*** )

 

[doyle60]

I'm afraid I'm still getting mistakes. I can't put and rhyme and
reason to it.

This data:

Style Color InvDate Units
A00071S Black 4/20/2005 1000
A00071S Black 4/30/2005 1000
A00071S Black 5/21/2005 5
A00071S Black 5/30/2005 5
A00071S Black 6/10/2005 1000
A00071S Black 6/11/2005 1000
B00071S Blue 4/30/2005 1000
B00071S Blue 5/30/2005 500
B00071S Blue 6/10/2005 1000
C00071S Brown 4/30/2005 1000
C00071S Brown 5/29/2005 500
C00071S Brown 5/30/2005 500
C00071S Brown 6/10/2005 1000
D00071S Chocolate 4/30/2005 500
D00071S Chocolate 5/29/2005 50
D00071S Chocolate 5/30/2005 95
D00071S Chocolate 6/10/2005 1000
E00071S Gray 4/30/2005 700
E00071S Gray 5/30/2005 700
E00071S Gray 6/10/2005 700
F00071S Green 4/30/2005 1000
F00071S Green 5/29/2005 500
F00071S Green 5/30/2005 500
F00071S Green 6/10/2005 1000
G00071S Lt. Pink 4/30/2005 1000
G00071S Lt. Pink 5/30/2005 1000
G00071S Lt. Pink 6/10/2005 500
H00071S Mauve 6/10/2005 50
J00071S Orange 4/30/2005 1000
J00071S Orange 5/30/2005 1000
J00071S Orange 6/10/2005 500
K00071S Peri 6/10/2005 1000
K00071S Pink 4/30/2005 90
K00071S Pink 5/29/2005 1000
K00071S Pink 5/30/2005 1000
K00071S Pink 6/10/2005 1000
L00071S Pink Floral 4/30/2005 500
L00071S Pink Floral 5/30/2005 500
L00071S Pink Floral 6/10/2005 1000
M00071S Purple 5/10/2005 1000
N00071S Red 4/30/2005 500
N00071S Red 5/29/2005 500
N00071S Red 5/30/2005 500
N00071S Red 6/10/2005 1000
P00071S Sandy 5/10/2005 1000
Q00071S Yellow 4/30/2005 1000
Q00071S Yellow 5/29/2005 500
Q00071S Yellow 5/30/2005 500
Q00071S Yellow 6/10/2005 1000

Yields the following data, with the ones marked **** being incorrect:

Style Color Units InvDate_Start InvDate_End Days_Diff
A00071S Black 1000 6/10/2005 6/11/2005 1
B00071S Blue 1000 6/10/2005 6/10/2005 0 ****
C00071S Brown 1000 6/10/2005 6/10/2005 0
D00071S Chocolate 1000 6/10/2005 6/10/2005 0
E00071S Gray 700 4/30/2005 6/10/2005 41
F00071S Green 500 5/29/2005 5/30/2005 1 ****
G00071S Lt. Pink 500 6/10/2005 6/10/2005 0
H00071S Mauve 50 6/10/2005 6/10/2005 0
J00071S Orange 1000 4/30/2005 5/30/2005 30 ****
K00071S Pink 1000 5/29/2005 6/10/2005 12
L00071S Pink Floral 1000 6/10/2005 6/10/2005 0
M00071S Purple 1000 5/10/2005 5/10/2005 0
N00071S Red 500 4/30/2005 5/30/2005 30 ****
Q00071S Yellow 1000 6/10/2005 6/10/2005 0

I'm trying to figure out why but can't quite get it. Different results
occur when I make all styles the same and use different colors names or
when I make all styles different with the same color names. Or when I
have the first style alphabetically be a different number of lines.
I'm thinking it is learning something. In one run with two
style/colors having only one line, one comes up and one dissappears.
There is no rhyme or reason for that.

Thanks so much for your help.

Matt

 

[doyle60]

A style above does effect the results of a style below. When I have
the following:

ID Style Color InvDate Units
56 B00071S Blue 4/30/2005 1000
55 B00071S Blue 5/30/2005 1000
43 B00071S Blue 6/10/2005 1000
28 F00071S Green 4/30/2005 1000
29 F00071S Green 5/29/2005 500
49 F00071S Green 5/30/2005 500
50 F00071S Green 6/10/2005 1000

I get:

Style Color Units InvDate_Start InvDate_End Days_Diff
B00071S Blue 1000 4/30/2005 6/10/2005 41
F00071S Green 500 5/29/2005 5/30/2005 1

Which is incorrect for the Green.

But if I have change only one of the Blue line Units, like this:

ID Style Color InvDate Units
56 B00071S Blue 4/30/2005 1000
55 B00071S Blue 5/30/2005 300
43 B00071S Blue 6/10/2005 1000
28 F00071S Green 4/30/2005 1000
29 F00071S Green 5/29/2005 500
49 F00071S Green 5/30/2005 500
50 F00071S Green 6/10/2005 1000

I get the correct answer for Green:

Style Color Units InvDate_Start InvDate_End Days_Diff
B00071S Blue 1000 6/10/2005 6/10/2005 0
F00071S Green 1000 6/10/2005 6/10/2005 0

Curious stuff. It seems to be learning something it shouldn't from a
previous action.

Thanks,

Matt

 

[David C. Holley]

Did you consider the suggestion that I made about capturing the
lastInventoryDate and lastInventoryCount when a new Inventory record is
added to the table?

 

[doyle60]

Yes, I did. I even started building it. It's a great solution because
*I* can do it myself. I at first thought SteveS's code was your
solution put into VBA, a little different than I would have done using
Queries and Macros. After installing SteveS's code, I realized it was
the old way, the one that Katuu started. Well, since SteveS went
through all that trouble---even after I posted I would try your way---I
decided to go with it.

The benefit of the old way, SteveS's way, is I keep Inventory data of
old. Of course, I can do it your way *and* save data in another table.
But I'll let SteveS bang it out as much as he wants.

Matt

 

[SteveS]

I'm afraid I'm still getting mistakes. I can't put and rhyme and
reason to it.

This data:

Style Color InvDate Units
A00071S Black 4/20/2005 1000
A00071S Black 4/30/2005 1000
A00071S Black 5/21/2005 5
A00071S Black 5/30/2005 5
A00071S Black 6/10/2005 1000
A00071S Black 6/11/2005 1000
B00071S Blue 4/30/2005 1000
B00071S Blue 5/30/2005 500
B00071S Blue 6/10/2005 1000
C00071S Brown 4/30/2005 1000
C00071S Brown 5/29/2005 500
C00071S Brown 5/30/2005 500
C00071S Brown 6/10/2005 1000
D00071S Chocolate 4/30/2005 500
D00071S Chocolate 5/29/2005 50
D00071S Chocolate 5/30/2005 95
D00071S Chocolate 6/10/2005 1000
E00071S Gray 4/30/2005 700
E00071S Gray 5/30/2005 700
E00071S Gray 6/10/2005 700
F00071S Green 4/30/2005 1000
F00071S Green 5/29/2005 500
F00071S Green 5/30/2005 500
F00071S Green 6/10/2005 1000
G00071S Lt. Pink 4/30/2005 1000
G00071S Lt. Pink 5/30/2005 1000
G00071S Lt. Pink 6/10/2005 500
H00071S Mauve 6/10/2005 50
J00071S Orange 4/30/2005 1000
J00071S Orange 5/30/2005 1000
J00071S Orange 6/10/2005 500
K00071S Peri 6/10/2005 1000
K00071S Pink 4/30/2005 90
K00071S Pink 5/29/2005 1000
K00071S Pink 5/30/2005 1000
K00071S Pink 6/10/2005 1000
L00071S Pink Floral 4/30/2005 500
L00071S Pink Floral 5/30/2005 500
L00071S Pink Floral 6/10/2005 1000
M00071S Purple 5/10/2005 1000
N00071S Red 4/30/2005 500
N00071S Red 5/29/2005 500
N00071S Red 5/30/2005 500
N00071S Red 6/10/2005 1000
P00071S Sandy 5/10/2005 1000
Q00071S Yellow 4/30/2005 1000
Q00071S Yellow 5/29/2005 500
Q00071S Yellow 5/30/2005 500
Q00071S Yellow 6/10/2005 1000

Yields the following data, with the ones marked **** being incorrect:

Style Color Units InvDate_Start InvDate_End Days_Diff
A00071S Black 1000 6/10/2005 6/11/2005 1
B00071S Blue 1000 6/10/2005 6/10/2005 0 ****
C00071S Brown 1000 6/10/2005 6/10/2005 0
D00071S Chocolate 1000 6/10/2005 6/10/2005 0
E00071S Gray 700 4/30/2005 6/10/2005 41
F00071S Green 500 5/29/2005 5/30/2005 1 ****
G00071S Lt. Pink 500 6/10/2005 6/10/2005 0
H00071S Mauve 50 6/10/2005 6/10/2005 0
J00071S Orange 1000 4/30/2005 5/30/2005 30 ****
K00071S Pink 1000 5/29/2005 6/10/2005 12
L00071S Pink Floral 1000 6/10/2005 6/10/2005 0
M00071S Purple 1000 5/10/2005 5/10/2005 0
N00071S Red 500 4/30/2005 5/30/2005 30 ****
Q00071S Yellow 1000 6/10/2005 6/10/2005 0

I'm trying to figure out why but can't quite get it. Different results
occur when I make all styles the same and use different colors names or
when I make all styles different with the same color names. Or when I
have the first style alphabetically be a different number of lines.
I'm thinking it is learning something. In one run with two
style/colors having only one line, one comes up and one dissappears.
There is no rhyme or reason for that.

Thanks so much for your help.

Matt

OK, this should (I hope) do it....

Using the data above, the results are now:

Style Color Units InvDate_START InvDate_END Days_Diff
A00071S Black 1000 6/10/2005 6/11/2005 1
A05526 Chocolate 1400 6/10/2005 6/10/2005 0
B00071S Blue 1000 6/10/2005 6/10/2005 0
C00071S Brown 1000 6/10/2005 6/10/2005 0
D00071S Chocolate 1000 6/10/2005 6/10/2005 0
D002008 Pink Floral 100 5/30/2005 6/10/2005 11
E00071S Gray 700 4/30/2005 6/10/2005 41
F00071S Green 1000 6/10/2005 6/10/2005 0
G00071S Lt. Pink 500 6/10/2005 6/10/2005 0
H00071S Mauve 50 6/10/2005 6/10/2005 0
J00071S Orange 500 6/10/2005 6/10/2005 0
K00071S Peri 1000 6/10/2005 6/10/2005 0
K00071S Pink 1000 5/29/2005 6/10/2005 12
L00071S Pink Floral 1000 6/10/2005 6/10/2005 0
M00071S Purple 1000 5/10/2005 5/10/2005 0
N00071S Red 1000 6/10/2005 6/10/2005 0
P00071S Sandy 1000 5/10/2005 5/10/2005 0
Q00071S Yellow 1000 6/10/2005 6/10/2005 0


'----------------------
the Modified code....
'***** begin code ******
Option Compare Database
Option Explicit

Private Sub Command0_Click()
'On Error GoTo Err_Command0_Click '(***1)
Dim rs As Recordset, rsTmp As Recordset
Dim strSQL As String
Dim varStyle As String
Dim varColor As String
Dim varUnits As Integer
Dim varDate1 As Date
Dim varDate2 As Date
Dim i As Integer, k As Integer

DoCmd.SetWarnings False

CurrentDb.Execute "Delete * from SLOBTemptbl"

DoCmd.SetWarnings True

strSQL = "SELECT SLOBtbl.Style, SLOBtbl.Color, SLOBtbl.InvDate,
SLOBtbl.Units FROM SLOBtbl ORDER BY SLOBtbl.Style, SLOBtbl.Color,
SLOBtbl.InvDate DESC;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.BOF And rs.EOF Then
rs.Close
Set rs = Nothing
MsgBox "no records"
Exit Sub
End If

rs.MoveLast
rs.MoveFirst

varStyle = ""
varColor = ""

Do While Not rs.EOF
If varStyle <> rs!Style Or varColor <> rs!Color Then
varStyle = rs!Style
varColor = rs!Color
varUnits = rs!Units
varDate1 = rs!InvDate
varDate2 = varDate1
Do While (varStyle = rs!Style And varColor = rs!Color And Not rs.EOF)
If varUnits = rs!Units Then
varDate2 = rs!InvDate
Else
Exit Do
End If
rs.MoveNext
If rs.EOF Then
Exit Do
End If
Loop

' save it to temp table
Set rsTmp = CurrentDb.OpenRecordset("SLOBTemptbl")
With rsTmp
.AddNew
!Style = varStyle
!Color = varColor
!Units = varUnits
!InvDate_START = varDate2
!InvDate_END = varDate1
.Update
End With
rsTmp.Close
Set rsTmp = Nothing
End If
If Not rs.EOF Then
'if style and color stay the same then move to the next record.
'if style and color change then move to the previous record
If (varStyle = rs!Style And varColor = rs!Color) Then
rs.MoveNext
Else
rs.MovePrevious
End If
End If
Loop 'Until rs.EOF

'Exit_Command0_Click: '(***2)
rs.Close
Set rs = Nothing
' display it
DoCmd.OpenQuery ("SLOBInvChgqry")

' Exit Sub

'Err_Command0_Click: '(***3)
' MsgBox Err.Description
' Resume Exit_Command0_Click '(***4)

End Sub
'******* end code ******

 

[SteveS]

Yes, I did. I even started building it. It's a great solution because
*I* can do it myself. I at first thought SteveS's code was your
solution put into VBA, a little different than I would have done using
Queries and Macros. After installing SteveS's code, I realized it was
the old way, the one that Katuu started. Well, since SteveS went
through all that trouble---even after I posted I would try your way---I
decided to go with it.

The benefit of the old way, SteveS's way, is I keep Inventory data of
old. Of course, I can do it your way *and* save data in another table.
But I'll let SteveS bang it out as much as he wants.

Matt

Thanks Matt, this has been fun...

 

[doyle60]

Yes! I think it works. I tried my best to make it return erroneous
data, but could not. However, I did get a compile error (duplicate
declaration in current scope) on the very first line:

Dim rs As Recordset, rsTmp As Recordset

I commented it out and the code worked. Should I worry? Please let me
know. Otherwise, thanks so much. I really appreciate it.

Matt

 

[doyle60]

Woops! Maybe I posted too soon. I just implemented the code into the
real world---using all 3400 records---and it gives me an "overflow"
error. I usually get this error when expected data is null or division
by zero. But in this case I can assure you that the data is all as
expected.

It only returns 131 records.

I was hoping to hold in this table a year's worth of inventory. That
would be about 40,000 or more records, with a total of about 3000 or so
Style/Colors.

Is there any hope for this?

It returns a mere 131 records and gives the overflow.

Thanks so much,

Matt

 

[Guest]

Woops! Maybe I posted too soon. I just implemented the code into the
real world---using all 3400 records---and it gives me an "overflow"
error. I usually get this error when expected data is null or division
by zero. But in this case I can assure you that the data is all as
expected.

It only returns 131 records.

I was hoping to hold in this table a year's worth of inventory. That
would be about 40,000 or more records, with a total of about 3000 or so
Style/Colors.

Is there any hope for this?

It returns a mere 131 records and gives the overflow.

Thanks so much,

Matt

Matt,

I can't get it to error.

Would you mind sending me a copy of the MDB? Just the two tables, the query
and the form (code).... compact/repair , then zip it.

I have a hotmail.com address limbim53.

 

[Guest]

Woops! Maybe I posted too soon. I just implemented the code into the
real world---using all 3400 records---and it gives me an "overflow"
error. I usually get this error when expected data is null or division
by zero. But in this case I can assure you that the data is all as
expected.

It only returns 131 records.

I was hoping to hold in this table a year's worth of inventory. That
would be about 40,000 or more records, with a total of about 3000 or so
Style/Colors.

Is there any hope for this?

It returns a mere 131 records and gives the overflow.

Thanks so much,

Matt

I finally was able to download the mdb by forwarding it to a diferent email
address. Hotmail wouldn't allow me to download the mdb - I could rant for a
long time.......

Anyway, the overflow error was "DIM varUnits as Integer"; it needs to be
"DIM varUnits as Long". Also, I explicitly referenced the recordset with DAO,
so you need a reference set for Microsoft DOA 3.6 Object Libraty if you don't
already have it set.

Now it reads thru 3406 records without a problem...

 

[RD]

On Tue, 21 Jun 2005 15:35:02 -0700, SteveS <sanfu at techie dot com> wrote:

Anyway, the overflow error was "DIM varUnits as Integer"; it needs to be
"DIM varUnits as Long". Also, I explicitly referenced the recordset with DAO,
so you need a reference set for Microsoft DOA 3.6 Object Libraty if you don't
already have it set.

DOA? ... LOL! Freudian slip?

You just made my morning!

Thanks,
RD