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
|
|||
|
|||
querying first record for each day
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? |
#2
|
|||
|
|||
querying first record for each day
On Jul 11, 2:54*pm, kcg5 wrote:
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? 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? |
#3
|
|||
|
|||
querying first record for each day
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 '================================================= === kcg5 wrote: 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? |
Thread Tools | |
Display Modes | |
|
|