Using the Group Function in a Query

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 00:57, 13 October 2007 (edit)
Lorinda (Talk | contribs)
(write article)
← Previous diff		 Revision as of 01:14, 13 October 2007 (edit) (undo)
Lorinda (Talk | contribs)
(Add count column and alias rows to query; add Notes section)
Next diff →
Line 1: Line 1:
{{TBBA Header}} {{TBBA Header}}
-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 [[Using a Query to Combine Fields From Different Tables|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:+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 [[Using a Query to Combine Fields From Different Tables|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==
#In the main database document, click on the {{prefName|Queries}} icon in the left hand column. #In the main database document, click on the {{prefName|Queries}} icon in the left hand column.
Line 9: Line 11:
#Close the {{Window|Add Table of Query}} window by clicking {{Button|Close}} #Close the {{Window|Add Table of Query}} window by clicking {{Button|Close}}
#Adjust the {{Window|Sightings}} mini-window as necessary to see the full list of fields. #Adjust the {{Window|Sightings}} mini-window as necessary to see the full list of fields.
-#Add the following three fields to the Query, in the order given:+#Add the following fields to the Query, in the order given:
#:'''AOU_ID''' #:'''AOU_ID'''
#:'''Bird_Name''' #:'''Bird_Name'''
#:'''Date''' #:'''Date'''
 +#:'''Date''' (you will have two columns headed with the '''Date field''')
#In the {{prefName|Function}} row of the '''AOU_ID''' and '''Bird_Name''' columns, select {{prefName|Group}} from the dropdown. (You need to click in the cell before the dropdown appears.) #In the {{prefName|Function}} row of the '''AOU_ID''' and '''Bird_Name''' columns, select {{prefName|Group}} from the dropdown. (You need to click in the cell before the dropdown appears.)
#In the {{prefName|Sort}} row, select {{prefName|ascending}} under the '''AOU_ID''' column. #In the {{prefName|Sort}} row, select {{prefName|ascending}} under the '''AOU_ID''' column.
-#In the {{prefName|Function}} row, under the '''Date''' column, select {{prefName|Minimum}} from the dropdown.+#In the {{prefName|Function}} row, under the first '''Date''' column, select {{prefName|Minimum}} from the dropdown.
-#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. 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 [[Using Date Time and Formatted Controls in Forms|date or formatted control]] for the '''MIN ("Date")''' field. This is left as an exercise for the student.+#In the {{prefName|Alias}} row, under the first '''Date''' column, type <tt>DateFirstSeen</tt>
 +#In the {{prefName|Function}} row, under the second '''Date''' column, select {{prefName|Count}} from the dropdown.
 +#In the {{prefName|Alias}} row, under the second '''Date''' column, type <tt>SightingsCount</tt>
 +#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.
 +#:*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.
 +#:*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}}.
#In the window that appears, give your query the name '''Life_List''' #In the window that appears, give your query the name '''Life_List'''
#Click {{Button|OK}} #Click {{Button|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 any fields to a Group query except those fields to which you assign a function.

Revision as of 01:14, 13 October 2007

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 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 any fields to a Group query except those fields to which you assign a function.
Retrieved from "https://neowiki.neooffice.org/index.php/Using_the_Group_Function_in_a_Query"
Personal tools