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.

Creating the Query

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 fields to the Query, in the order given:

   AOU_ID

   Bird_Name

   Date

   Date (you will have two columns headed with the Date field)

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 first Date column, select Minimum from the dropdown.
11. In the Alias row, under the first Date column, type DateFirstSeen
12. In the Function row, under the second Date column, select Count from the dropdown.
13. In the Alias row, under the second Date column, type SightingsCount
14. 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 that the DateFirstSeen 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 DateFirstSeen field. This is left as an exercise for the student.
    • The SightingsCount column indicates how many times a given bird has been identified.

15. Save your query by clicking on the Save button on the toolbar or hitting Command-S.
16. In the window that appears, give your query the name Life_List
17. Click OK

Notes

• The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above.
• You cannot add fields to a Group query that are not assigned a function.