I am analyzing wildlife research data and need to find the record that
represents the first time an individual animal was detected in a given
year. I thought I had a scheme that should work, but it's failing.
First I wrote a query that returns the year, individualID, date, visit
(am or pm), and recordID (PK). The query is sorted sequentially by
the first four fields in ascending order. It returns exactly what I
had hoped - say individual #9999 was detected 5 times in 2009, those
records are in correct chronological order, regardless of the
recordID.

I used this query as the source for a second query, which groups by
year and individualID, and selects first([recordID]). To follow the
example above, if the data was entered out of order, the first time
(chronologically) that individual #9999 was detected could have a
recordID of 5000, while a subsequent detection could have a recordID
of 4000. For some reason, even though the first query places
everything in chronological order, the first([recordID]) field will
return 4000, instead of returning 5000 as I want it to. Any ideas?
It's as if the order of the original query is lost when plugged into
the second query.

I tried adding date and visit to the ORDER BY clause of the second
query, but I can't have them in the order by clause without performing
an aggregate function on them, and if I stick them in the query with a
function I still get the wrong recordID. I also tried removing the
reference to Query1 and replacing it with the actual SQL from the
first query - still I get the wrond recordID. Here's the query:

SELECT Year, IndividualID, First(ID) AS [First Cap ID]
FROM (SELECT Year([Date]) AS [Year], [Data].IndividualID, [Data].Date,
[Data].Visit, [Data].ID
FROM [Data]
WHERE ((([Data].IndividualID) Is Not Null))
ORDER BY Year([Date]), [Data].IndividualID, [Data].Date, [Data].Visit)
GROUP BY Year, IndividualID
ORDER BY Year, IndividualID;