bring related fields into a query that uses MaxofDate and GROUP BY

[Jey]

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

 

[Lord Kelvan]

.... why dont you just add them to your normal query i see no reason to
use a sub query

SELECT GPSLocations.Individual_ID,
GPSLocations.LatWGS84,GPSLocations.LongWGS84 ,Max(GPSLocations.Date)
AS MaxOfDate
FROM GPSLocations
GROUP BY
GPSLocations.Individual_ID,GPSLocations.LatWGS84,GPSLocations.LongWGS84;


hope this helps

Regards
Kelvan

 

[Jey]

That was the first thing I tried, but what it produces is a MaxDate for each
unique combination of [Individual_ID, lat, and long] which is about 95% of
all the records. (These animals don't stay still for very long!). I need a
MaxDate for each unique combination of Individual_ID (i.e. one date per
individual) with the lat/long of just that one locate.
Jey

 

[Jey]

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