If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
bring related fields into a query that uses MaxofDate and GROUP BY
Oops, one more complication that I hadn't forseen. In my first query I only
needed the date of the last locate... but if I want the location (lat/long), then I also need the specific loacte on that date... there can be more than one locate per day. I have a Time field, but I'm having trouble even incorporating that extra field to the query. When I try: SELECT GPSLocations.Individual_ID, Max(GPSLocations.Date) AS MaxOfDate, Max(GPSLocations.Time) AS MaxOfTime FROM GPSLocations GROUP BY GPSLocations.Individual_ID; I get one record per Individual (which is what I want) with the date of the last locate (good so far...) but with the "latest time of day" that the individual was located... not the last time it was located on the last day. How do I constrain the MaxOfTime clause to "MAxofTime where Date = MaxofDate" ? Jey "Jey" wrote: Hi, Thanks in advance if someone can show me how to do this! I'm storing GPS collar data for radio-collared animals. My table is called GPSLocations, and has fields: GPS_ID Individual_ID Date LatWGS84 LongWGS84 (plus more that don't apply to this problem) I have a query to extract the last GPS locate date for each individual: SELECT GPSLocations.Individual_ID, Max(GPSLocations.Date) AS MaxOfDate FROM GPSLocations GROUP BY GPSLocations.Individual_ID; ...but I want to modify it to also show the lat/long of that locate. I've looked at: http://www.mvps.org/access/queries/qry0020.htm but my query is complicated by also having a GROUP BY clause. I've tried things like: SELECT Q.Individual_ID, Q.Date, Q.LatWGS84, Q.LongWGS84 FROM GPSLocations As Q WHERE Q.Date = (SELECT T.Individual_ID, Max(T.Date) AS MaxofDate FROM GPSLocations As T WHERE T.GPS_ID = Q.GPS_ID GROUP BY T.Individual_ID) GROUP BY Q.Individual_ID; I just can't get it to work! Is it even possible to do in one query, or will I have to use more than one? Jey |
Thread Tools | |
Display Modes | |
|
|