querying first record for each day

[kcg5]

Hello all...I have data of sightings of killer whale types and would like to
query only the first record of each day of each unique ecotype of killer
whale. For example, I would like to pull up the first record for each day for
the sighting of each unique pod of killer whales from a column called
"whalecodedescription". Does anybody know how I would run such a select query
in Access?

 

[louisjohnphillips]

Hello all...I have data of sightings of killer whale types and would liketo
query only the first record of each day of each unique ecotype of killer
whale. For example, I would like to pull up the first record for each dayfor
the sighting of each unique pod of killer whales from a column called
"whalecodedescription". Does anybody know how I would run such a select query
in Access?

Can we presume that the data records date and the time of the siting
and that the first siting per day is not duplicated--that is two
observers do not simultaneously record the siting of the pod?

If you can meet these assumptions, this query should show the earliest
siting of each day of each pod.


SELECT A.DateSiting, A.TimeSiting, A.whalecodedescription
from WhaleSitings as A
inner join ( SELECT DISTINCT DateSiting, whalecodedescription
FROM WhaleSitings ) as B
on ( A.whalecodescrition = B.whalecodedescription ) and
( A.DateSiting = B.DateSiting ) )
WHERE TimeSiting = ( SELECT Min( TimeSiting )
FROM WhaleSitings
WHERE DateSiting = A.DateSiting
AND TimeSiting = A.TimeSiting
AND Whalecodedescription =
A.whalecodedescription )

Am I understanding the problem correctly?

 

[John Spencer]

Assumption: You have one field that contains the date and time of the
sighting

SELECT WhaleCodeDescription, DateValue(SightingDateTime) as TheDate,
Min(TimeValue(SightingDateTime)) as EarliestTime
FROM YourTable
GROUP BY WhaleCodeDescription, DateValue(SightingDateTime)

If you have two fields - one for the date and one for the time then you
won't need to use DateValue and TimeValue functions.

Once you have the above data, you can use the query and your table
linking on the whalecode description and the other two fields


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================