esn wrote:
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;


The First function is mostly useless because it uses the
first value retrieved from disk, which has nothing to do
with the smallest value or the order records were created.
Normally, you should use the Min function instead of First,
but if the ID field is an AutoNumber, there is no guarantee
the AutoNumbers are created in a monotonically increasing
order.

That means that you should find the minimum date for each ID
and use that to find the desired record. A relatively
simple way of doing that is something like:

SELECT Year, IndividualID,
[Data].Date As [First Cap ID],
[Data].Visit, [Data].ID
FROM [Data]
WHERE [Data].IndividualID) Is Not Null
And [Data].Date = (SELECT Min(X.Date)
FROM [Data] As X
WHERE Year(X,Date) = Year([Data].Date)
And X,IndividualID = [Data].IndividualID )
ORDER BY Year, IndividualID;

--
Marsh
MVP [MS Access]