Simple Bird List

[Guest]

I'm trying to set up a simple database in Access 2000 that allows me to
record and list bird observations. I have two tables. The first table is of
bird names, and has bird names and a four-digit bird name code (so I don't
have to write out yellow-bellied sapsucker everytime I record an
observation). The second is of observations, and has the bird name code, the
date of the observation, the locale of the observation, and the U.S. State in
which the observation occured. I am trying to build a query that will return
the first observation for each species - the first observation for house
sparrow, first for cardinal, etc. Ultimately I would like to sort them,
earliest species seen first. However, I am going nuts trying to figure out
how to do this. Any suggestions?

 

[tina]

assuming that the "date of the observation" field is a Date/Time datatype,
you can create a Totals query to pull the first observation of each species.
in the query design view, click the Totals button (looks like a funny
capital E, or a capital M on it's left side). add the bird name code field
to the design grid, it should default to Group By on the "Total:" line. add
the observation date field to the grid, and change the default from Group By
to Min; that will pull the *earliest* date for each bird name code listed.

hth

 

[Guest]

Your suggestion worked perfectly! Thank you again for taking the time to
reply to my question. I was ready to give up and kludge an Excel list...

 

[tina]

you're welcome!