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
|
|||
|
|||
First function returning min
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; |
Thread Tools | |
Display Modes | |
|
|