Hi;
This seems so simple, but i can't work it out.....
I have three tables with relationships, tripdata, profile, profiledetails.
tripdata: contains destination, departuretime, driver etc... is linked to profile by profileid
profile: contains the name of the trip profile
profiledetail: contains tminus, phase, profileid. The phases are Checkin, Boarding, Departed. Tminus represents the number of minuites before the tripdata.departuretime that this phase starts.
I want to select destination, departuretime and current phase for the TOP 10 future departures.
This query returns the correct data, however it is missing one field: phase
Example 1:
Code:
SELECT DISTINCTROW TOP 10 tripdata.destination, tripdata.departuretime, Min(DateAdd("n",-[tminus],[departuretime])) AS [current]
FROM (profile INNER JOIN tripdata ON profile.id = tripdata.profileid) INNER JOIN profiledetail ON profile.id = profiledetail.profileid
GROUP BY tripdata.destination, tripdata.departuretime
HAVING (((Min(DateAdd("n",-[tminus],[departuretime])))>Now()))
ORDER BY Min(DateAdd("n",-[tminus],[departuretime]));
however, when i add the phase field, i get a row for each trip for each phase, and not just the current phase (ie Min is ignored):
Code:
SELECT DISTINCTROW TOP 10 tripdata.destination, tripdata.departuretime, Min(DateAdd("n",-[tminus],[departuretime])) AS [current], profiledetail.phase
FROM (profile INNER JOIN tripdata ON profile.id = tripdata.profileid) INNER JOIN profiledetail ON profile.id = profiledetail.profileid
GROUP BY tripdata.destination, tripdata.departuretime, profiledetail.phase
HAVING (((Min(DateAdd("n",-[tminus],[departuretime])))>Now()))
ORDER BY Min(DateAdd("n",-[tminus],[departuretime]));
how can i get the extra field showing the currentphase as in example 2, but only have one row for each trip as in example 1.
I have tried left joins, right joins, two queries, but i can't seem to get it to work right...
Thank You