Using the Group Function in a Query

From NeoWiki

Avid birders keep a life list. A life list is a list of bird species the birder has identified. To be an adequate tool for birders, our database needs to include a means for creating a life list. We took a step in that direction when we created the All Sightings query. If you have entered the test data provided in the article Creating A Data Entry Form With A Subform, however, you will notice that certain bird species (e.g. the Tufted Timouse) appear twice in the list. For a serious birder who makes regular field trips, there would quickly be even more entries for each species in this list. We need to construct a query that will list each bird species only once. To do so:

1. In the main database document, click on the Queries icon in the left hand column.
2. Click on the Create Query in Design View icon in the Tasks section. The Query Design window opens and the Add Table or Query window opens.
3. In the Add Table of Query window, click on the Queries radio button
4. Select the Sightings List query and click Add
5. Close the Add Table of Query window by clicking Close
6. Adjust the Sightings mini-window as necessary to see the full list of fields.
7. Add the following three fields to the Query, in the order given:

   AOU_ID

   Bird_Name

   Date

8. In the Function row of the AOU_ID and Bird_Name columns, select Group from the dropdown. (You need to click in the cell before the dropdown appears.)
9. In the Sort row, select ascending under the AOU_ID column.
10. In the Function row, under the Date column, select Minimum from the dropdown.
11. Click the Run Query button on the toolbar. (It is the data sources icon with a green arrow pointing down). After a pause, you should see the query results. The rows will be sorted by AOU_ID order, and there will only be one entry for each bird species. Note, however, that the MIN("Date") column will contain integers, not dates. There does not appear to be a way to get this column to appear as a date in a Query view. But the numbers can be "converted" to dates by creating a form and using a date or formatted control for the MIN ("Date") field. This is left as an exercise for the student.
12. Save your query by clicking on the Save button on the toolbar or hitting Command-S.
13. In the window that appears, give your query the name Life_List
14. Click OK