A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

First function returning min



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old May 13th, 2010, 07:39 PM posted to microsoft.public.access.queries
esn
external usenet poster
 
Posts: 34
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.