Using the Group Function in a Query

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 11:53, 15 October 2007 (edit)
Jgd (Talk | contribs)
(botlangbar -> Fr page)
← Previous diff
Revision as of 02:04, 9 May 2008 (edit) (undo)
Lorinda (Talk | contribs)
(Add methods for fixing the MIN(Date) display as integer issue)
Next diff →
Line 24: Line 24:
#Click the {{Button|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. #Click the {{Button|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. #:*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 [[Using Date Time and Formatted Controls in Forms|date or formatted control]] for the '''DateFirstSeen''' field. This is left as an exercise for the student.+#:*Note that the '''DateFirstSeen''' column will contain integers, not dates. There are three ways to deal with this:
 +#:#Control-click or right-click on the '''DateFirstSeen''' column of the query results and choose {{Menu|Column Format...}} from the contextual menu. Set the {{Section|Category}} to {{prefName|Date}} and choose the Desired {{Section|Format}}. Click {{Button|OK}}. (This works even if the Query hasn't been opened in design mode)
 +#:#Use the CAST function as described [[#Using Cast in SQL Direct Mode|below]]
 +#:#Correct the issue only in forms using the query by using a [[Using Date Time and Formatted Controls in Forms|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. #:*The '''SightingsCount''' column indicates how many times a given bird has been identified.
#Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}. #Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}.
Line 30: Line 33:
#Click {{Button|OK}} #Click {{Button|OK}}
-==Notes==+==Other Notes==
*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. *The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above.

Revision as of 02:04, 9 May 2008

This article is part of the To Base and Back Again tutorial series.

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 are three ways to deal with this:
    1. Control-click or right-click on the DateFirstSeen column of the query results and choose Column Format... from the contextual menu. Set the Category to Date and choose the Desired Format. Click OK. (This works even if the Query hasn't been opened in design mode)
    2. Use the CAST function as described below
    3. Correct the issue only in forms using the query by 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

Other 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.


This article in other languages: Français
Personal tools