Finding 3 Consecutive Months in History File

[hawg]

I am looking for a method to find all records of those who "qualified" for 3 or more CONSECUTIVE months. If an employee "qualified" in a particular month there will be a record in the table with the month and their ID. If they did not qualify--no record.
MyTable:

ID Month
Emp1 Jan99
Emp1 Feb99
Emp1 Aug99
Emp1 Sep99
Emp1 Oct99
Emp2 Feb98
Emp2 Apr99
.....

Above Emp1 would be returned for appearing in Aug,Sep,Oct while Emp2 misses the Bonus. I'm not interested gaps in time or the total months of "qualification", just if there was a period of 3 CONSECUTIVE months anywhere in their history.

Thanks in advance for any suggestions

 

[CajunCenturion]

Hi Hawg,
I don't have any code for this, but I offer the following algorythm for consideration.

1). Establish a two dimensional array where
the first subscript represents the year and,
the second subscript represents the month.

2). Size the array for the number of years that you need to process, beginner with the earliest year in the history file, and continuing to an appropriate point in the future.
Lets say your history start in 1980, and you want to go thru the year 2010.
Then the first dimension would be (LastYear - FirstYear) + 1
To determine the first subscript, subtract the base year from the history record year.
Feb81 would be 1981 - 1980 which is 1
Aug98 would be 1998 - 1980 which is 18
Dec10 woube be 2010 - 1980 which is 30

It appears that you have only 2 digit years,
but it should not be difficult to "window" the year to determine the century.

The second dimension is 0 to 11 for the months.
Because of the way the months are named, you'll need to convert them
Select Case UCase(Left(HistDate, 3))
Case "JAN"
month_id = 0
case "FEB
month_id = 1
...
End Select


Read the history table and order by employee number,
and for each employee, initialize the array to all False, and then build the array.
In building the array, you'll need to convert the year and month to the appropriate subscripts.
When you're done with the first employee, then check for 3 consecutives something like this
for year = 0 to lastyear
for month = 0 to 11
select case month
case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
if ((array(year, month) = True) and _
(array(year, month+1) = true) and _
(array(year, month+2) = true)) then
<we have a match>
case 11
if (year < lastyear) then
if array(year, 11) = True and _
array(year, 12) = true and _
array(year+1, 0) = true) then
<we have a match>
end if
endif
case 12
if (year < lastyear) then
if array(year, 12) = True and _
array(year+1, 0) = true and _
array(year+1, 1) = true) then
<we have a match>
end if
endif
end select
next month
next year

The reinitialize the array to false, and do the next employee.



Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[strongm]

Late I know, but here is an alternative solution. It assumes that you have created two queries against MyTable: rsID, which contains the unique IDs, and rsTable which is grouped by ID and Month in ascending order:
[tt]
rsID.MoveFirst
Do Until rsID.EOF
rsTable.Filter = &quot;ID = '&quot; & rsID(&quot;ID&quot & &quot;'&quot;
mydate = CDate(Left(rsTable(&quot;Month&quot, 3) & &quot; &quot; & Right(rsTable(&quot;Month&quot, 2))
Do Until rsTable.EOF Or (rsTable.RecordCount - rsTable.AbsolutePosition) < 2
rsTable.MoveNext
rsTable.MoveNext
If DateDiff(&quot;m&quot;, mydate, CDate(Left(rsTable(&quot;Month&quot, 3) & &quot; &quot; & Right(rsTable(&quot;Month&quot, 2))) = 2 Then
Debug.Print rsTable(&quot;ID&quot 'or add to appropriate disconnected recordset, or whatever
End If
mydate = CDate(Left(rsTable(&quot;Month&quot, 3) & &quot; &quot; & Right(rsTable(&quot;Month&quot, 2))
Loop
rsID.MoveNext
Loop