Overlapping Dates 2

[brians0friends]

DATA:

RMS_ID PLAN MOVEIN_DATE MOVEOUT_DATE
1 FLEXB 08/22/2002 12/18/2002
1 FLEXB 01/19/2003 03/22/2003
2 MP10 08/22/2002 11/13/2002
2 MP10 01/17/2003 03/22/2003
2 MP10 01/22/2003 05/22/2003
3 MP10 08/22/2002 11/15/2002
3 MP10 01/20/2003 03/25/2003


I am trying to write a query that looks at each grouping of "RMS_ID" and determines if the proceeding Corresponding "RMS_ID" "MOVEIN_DATE". Overlaps the previous corresponding "RMS_ID" Records "MOVEOUT_DATE".

For instance if you look at "RMS_ID" "2" the second record "MOVEIN_DATE" of "01/17/2003" does not come before the previous corresponding "RMS_ID" "2" "MOVEOUT_DATE" "11/13/2002"...BUT when you look the third record for "RMS_ID" "2" the "MOVEIN_DATE" is "01/22/2003" which comes before the previous corresponding "RMS_ID" "2" "MOVEOUT_DATE" of "03/22/2003"...In this case I would like all records overlapping (the second and third record for "RMS_ID" "2&quot to return back in the query.

 

[vb6novice]

briansOfriends,

What you want is not going to happen in a query. A query considers the fields in each record as if there were no other records. And there's no way to be sure that the record that is before the one you are considering is the right one or the one that would logically come before the one you are considering.

It will take code to get the solution to this problem. Are you up for that?

 

[cmmrfrds]

You may be able to do it in a query by comparing the table to itself, but both records would need to be on the same line in the print out.

The basic idea.

select pre.RMS_ID, pre.PLAN, pre.MOVEIN_DATE, pre.MOVEOUT_DATE, post.MOVEIN_DATE, post.MOVEOUT_DATE
from dataTab as pre, dataTab as post
where pre.MOVEIN_DATE < post.MOVEIN_DATE
and pre.MOVEOUT_DATE > pre.MOVEIN_DATE
and pre.RMS_ID = post.pre.RMS_ID

 

[brians0friends]

Code, yes I am up for it.

 

[vb6novice]

briansOfriends,

I have put the data you supplied in a table (tblRooms) in an Access 97 database. I created another table with the same structure as the rooms table, called tblOverlaps. I created the code attached below.
The code does the following:
Deletes any records in the overlaps table.
Creates a recordset from the rooms table sorted by room ID then by move in date.
It loops through the records storing the previous record's fields to variables, moves to the next record, and compares the variable to the current record. If room id's are the same and the move in of the current rec is before the moveout of the previous rec, it writes the data from both recs into the overlaps table.

The results you orginally asked for are in the overlaps table.

 

[vb6novice]

briansOfriends,

I have put the data you supplied in a table (tblRooms) in an Access 97 database. I created another table with the same structure as the rooms table, called tblOverlaps. I created the code attached below.
The code does the following:
Deletes any records in the overlaps table.
Creates a recordset from the rooms table sorted by room ID then by move in date.
It loops through the records storing the previous record's fields to variables, moves to the next record, and compares the variable to the current record. If room id's are the same and the move in of the current rec is before the moveout of the previous rec, it writes the data from both recs into the overlaps table.

The results you orginally asked for are in the overlaps table.
Here's the code.

Sub compare_dates()

Dim dbs As Database
Dim rooms_rst As Recordset
Dim overlaps_rst As Recordset
Dim rms_sql As String
Dim room_id As Integer
Dim move_in, move_out As Date


rms_sql = &quot;SELECT tblRooms.RMS_ID, tblRooms.PLAN, tblRooms.MOVEIN_DATE, tblRooms.MOVEOUT_DATE &quot; & _
&quot;FROM tblRooms ORDER BY tblRooms.RMS_ID, tblRooms.MOVEIN_DATE;&quot;

Set dbs = CurrentDb
Set overlaps_rst = dbs.OpenRecordset(&quot;tblOverlaps&quot
Do Until overlaps_rst.EOF
overlaps_rst.Delete
overlaps_rst.MoveNext
Loop

Set rooms_rst = dbs.OpenRecordset(rms_sql)

rooms_rst.MoveFirst
room_id = rooms_rst![RMS_ID]
move_in = rooms_rst![MOVEIN_DATE]
move_out = rooms_rst![MOVEOUT_DATE]
rooms_rst.MoveNext
Do Until rooms_rst.EOF = True
If rooms_rst![RMS_ID] = room_id Then
If rooms_rst![MOVEIN_DATE] < move_out Then
With overlaps_rst
.AddNew
![RMS_ID] = room_id
![PLAN] = rooms_rst![PLAN]
![MOVEIN_DATE] = move_in
![MOVEOUT_DATE] = move_out
.Update
.AddNew
![RMS_ID] = rooms_rst![RMS_ID]
![PLAN] = rooms_rst![PLAN]
![MOVEIN_DATE] = rooms_rst![MOVEIN_DATE]
![MOVEOUT_DATE] = rooms_rst![MOVEOUT_DATE]
.Update

End With
End If
End If
room_id = rooms_rst![RMS_ID]
move_in = rooms_rst![MOVEIN_DATE]
move_out = rooms_rst![MOVEOUT_DATE]
rooms_rst.MoveNext
Loop

End Sub