Conditional Total Querying Help??

[efinnen]

Please bear with this explanation/question, I've recently been reintroduced to Access after a year absence and my brain is sorta fuzzy with some of the stuff I used to do...

And any help/advice that can be provided would be greatly appreciated.


So I have a table (go figure) that sort of resembles below, albeit many more records.

ID From To
1 1 5
1 3 9
1 7 13.2
1 20 24
1 28 31
1 30 42
2 10 20
2 25 27.5
2 26 31
3 400 401
4 485 490
4 495 500
yada yada yada


Now if you can see some of these Id's has several sets of overlaps as far as the From's and the To's are concerned. What I want to do is simply dissect/remove the overlaps and have a significanly simpler table that resembles something like below.

ID From T0
1 1 13.2
1 20 24
1 28 42
2 10 20
2 25 31
3 400 401
4 485 490
4 495 500
and so on



I appreciate the help

-Eric

 

[JPMontreal]

you will need a code like that:


Sub NoOverLaps()
Dim db As Database
Dim rs As Recordset
Dim intFirstId As Integer
Dim sgnTo As Single
Dim sgnSecondTo As Single
Dim intSeId As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from MyTable order by ID&quot
rs.MoveFirst
Do Until rs.EOF
intFirstId = rs.Fields("id&quot.Value
sgnTo = rs.Fields("To&quot.Value
rs.MoveNext
sgnSecondTo = rs.Fields("to&quot.vlaue
If intFirstId = rs.Fields(&quot;Id&quot.Value And sngto < sgnSecondTo Then
rs.MovePrevious
rs.Edit
rs.Fields(&quot;To&quot.Value = sgnSecondTo
rs.Update
rs.MoveNext
rs.Delete
End If
rs.MoveNext
Loop
Set rs = Nothing
End Sub


Jean-Paul
Montreal
mtljp2@sympatico.ca