show data on same row in listbox 1

[Apollo13a]

The data in my table is on two rows and I want to show the corresponding records in a listbox but on the same row. My table(tblSixtyDay) has fields tblSixtyDay(PK), DateOn, DateOFF, Rotation and State.
tblSixtyDay DateON DateOFF Rotation State
1 1-1-04 1 1
2 2-15-04 1 2

desired output:
DateON DateOFF Rotation
1-1-04 2-15-04 1

Can I do this?
Thanks, Jim

 

[PHV]

Something like this ?
SELECT A.DateON, B.DateOFF, A.Rotation
FROM tblSixtyDay A INNER JOIN tblSixtyDay B
ON (A.Rotation=B.Rotation) AND (B.tblSixtyDay>A.tblSixtyDay)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Apollo13a]

Thanks, my previous post might be misleading Actually, the rotation numbers are the same as follows:

tblSixtyDay DateON DateOFF Rotation State
1 1-1-04 1 1
2 2-15-04 1 2

desired output:
DateON DateOFF Rotation
1-1-04 2-15-04 1

Do I need to alias' the tables? A2k asks me to enter parameter b.tblSixtyDay

 

[PHV]

Perhaps this ?
SELECT A.DateON, B.DateOFF, A.Rotation
FROM tblSixtyDay A INNER JOIN tblSixtyDay B
ON A.Rotation=B.Rotation
WHERE B.DateOFF>A.DateON

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Apollo13a]

Thanks PHV, really close. The only thing is that if I have a DateOn and no corresponding DateOff the DateOn doesn't show in the query. Thanks for your help.
Jim

 

[PHV]

And this ?
SELECT A.DateON, B.DateOFF, A.Rotation
FROM tblSixtyDay A LEFT JOIN tblSixtyDay B
ON A.Rotation=B.Rotation
WHERE (B.DateOFF>A.DateON Or B.DateOFF Is Null)
Or this ?
SELECT A.DateON, B.DateOFF, A.Rotation
FROM tblSixtyDay A LEFT JOIN tblSixtyDay B
ON A.Rotation=B.Rotation And A.DateON<B.DateOFF

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Apollo13a]

That did the trick.
Thank You PHV