Return Aggrigate function (Min) and another field

[ntsupport]

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:

 
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):

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

[jzwp11]

You might try this...

Create a query similar to your first query, except not an aggregate query and not a TOP query:

query name: qryBaseData
SELECT tripdata.destination, tripdata.departuretime, DateAdd("n",-[tminus],[departuretime])AS [current], phase
FROM (profile INNER JOIN tripdata ON profile.id = tripdata.profileid) INNER JOIN profiledetail ON profile.id = profiledetail.profileid

Now create another query that brings in your first query and qryBaseData, join the two by destination, departuretime and current. Select the fields you want to display.