I'm Stumpped

[eileen1309]

I have a table that records vehicles on and off. I need to be able to show which vehicles came off and which vehicles came for the same day. for example
Name Vinno OnDate OffDate
example 100 2/1/04 2/13/04
example 101 2/13/04 2/1/05

any suggestions would be great.

 

[PHV]

Something like this ?
SELECT A.Name,A.Vinno,A.OnDate,A.OffDate
FROM yourTable A INNER JOIN yourTable B ON A.OffDate=B.OnDate
UNION SELECT A.Name,A.Vinno,A.OnDate,A.OffDate
FROM yourTable A INNER JOIN yourTable B ON A.OnDate=B.OffDate

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

 

[hkaing79]

I would create one query for OnDate and one query for OffDate. Then match their date.

Query 1 Query2
100 2/1/04 100 2/13/04
101 2/13/04 101 2/1/05

But, what if two or more vehicles were exchanged? For example

100 1/1/05 1/2/05
101 1/1/05 1/2/05
102 1/2/05 1/3/05
103 1/2/05 1/3/05

Perhaps you can restructure the table to look more like
Name Vinno OnDate Trans
example 100 2/1/04 0
example 100 2/13/04 1
example 101 2/13/04 0
example 101 2/1/05 1
where 0 represents date left and 1 represents date returned.

Then create a find duplicates query based on OnDate. The 0s will tell you which left and the 1s will tell you which returned.

Hope that helps.