https://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&action=history&feed=atomUsing the Group Function in a Query - Revision history2024-03-28T11:30:39ZRevision history for this page on the wikiMediaWiki 1.10.1https://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&diff=13826&oldid=prevLorinda: /* Other Notes */ Add section on using the CAST function; added note about multiple OR statements issue.2008-05-09T02:29:23Z<p><span class="autocomment">Other Notes -</span> Add section on using the CAST function; added note about multiple OR statements issue.</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 02:29, 9 May 2008</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 32:</strong></td>
<td colspan="2" align="left"><strong>Line 32:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#In the window that appears, give your query the name '''Life_List'''</td><td> </td><td style="background: #eee; font-size: smaller;">#In the window that appears, give your query the name '''Life_List'''</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">==Using CAST in SQL Direct Mode==</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">The CAST function allows you to "convert" a field from one type to another. In our example, we want to CAST an integer (the MIN(DATE) result) as a Date. But this function can also be used to CAST a numeric field as text, etc. Unfortunately, the CAST function cannot be used in Query Design mode (the mode where you see columns for each field and rows for Alias, Table, Criteria, etc.). CAST can only be used in SQL mode, and specifically in SQL direct mode. '''Queries using SQL direct mode cannot be viewed in Design mode.'''</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#With the Query built above still open in editing mode, click on the {{Button|Design View On/Off}} Button</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#: The Query building section of the window shifts to SQL mode and you will see several lines of SQL code that look similar to this:</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:{{preBox|<nowiki>SELECT "AOU_Birdlist"."AOU_ID", "Sightings"."Bird_Name", MIN( "Field_Trips"."Date" ) AS "DateFirstSeen", </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">COUNT( "Field_Trips"."Date" ) AS "Count" FROM "Sightings", "AOU_Birdlist", "Field_Trips" </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">WHERE ( "Sightings"."Bird Name" = "AOU Birdlist"."EnglishName" AND "Sightings"."Field Trip ID" = "Field Trips"."ID" ) </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">GROUP BY "AOU Birdlist"."ID", "Sightings"."Bird Name"</nowiki>}}</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Click on the {{Button|Run SQL command directly}} button on the toolbar. It is a data sources icon with a green arrow pointing down.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Find the MIN("Field_Trips"."Date) and edit it to read:</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:{{preBox|<nowiki>CAST(MIN("Field_Trips"."Date) AS DATE)</nowiki>}}</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:'''Do not change anything else in the Query unless you understand SQL!'''</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#Click on the {{Button|Run Query}} button on the toolbar to test the Query.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#You can now close the Query</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">==Other Notes==</td><td> </td><td style="background: #eee; font-size: smaller;">==Other Notes==</td></tr>
<tr><td colspan="2" align="left"><strong>Line 37:</strong></td>
<td colspan="2" align="left"><strong>Line 53:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*You cannot add fields to a Group query that are not assigned a function.</td><td> </td><td style="background: #eee; font-size: smaller;">*You cannot add fields to a Group query that are not assigned a function.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">*If a Grouping Query is based on a Query that contains multiple OR statements, NeoOffice (and OpenOffice.org) will hang when you attempt to run the Query. See [[Using a Union Statement in a View]] for an alternative method for building the underlying Query in these cases</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">{{botlangbarEN|[[Fr:Utiliser la Fonction Groupe dans une Requête|Français]]}}</td><td> </td><td style="background: #eee; font-size: smaller;">{{botlangbarEN|[[Fr:Utiliser la Fonction Groupe dans une Requête|Français]]}}</td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&diff=13825&oldid=prevLorinda: Add methods for fixing the MIN(Date) display as integer issue2008-05-09T02:04:31Z<p>Add methods for fixing the MIN(Date) display as integer issue</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 02:04, 9 May 2008</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 24:</strong></td>
<td colspan="2" align="left"><strong>Line 24:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#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. </td><td> </td><td style="background: #eee; font-size: smaller;">#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. </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#:*The rows will be sorted by '''AOU_ID''' order, and there will only be one entry for each bird species. </td><td> </td><td style="background: #eee; font-size: smaller;">#:*The rows will be sorted by '''AOU_ID''' order, and there will only be one entry for each bird species. </td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#:*Note that the '''DateFirstSeen''' column will contain integers, not dates. <del style="color: red; font-weight: bold; text-decoration: none;"> </del>There <del style="color: red; font-weight: bold; text-decoration: none;">does not appear </del>to <del style="color: red; font-weight: bold; text-decoration: none;">be a way to get </del>this column to <del style="color: red; font-weight: bold; text-decoration: none;">appear as a date in a </del> <del style="color: red; font-weight: bold; text-decoration: none;">Query view</del>. <del style="color: red; font-weight: bold; text-decoration: none;">But </del>the <del style="color: red; font-weight: bold; text-decoration: none;">numbers can be "converted" to dates </del>by <del style="color: red; font-weight: bold; text-decoration: none;">creating a form and </del>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.</td><td>+</td><td style="background: #cfc; font-size: smaller;">#:*Note that the '''DateFirstSeen''' column will contain integers, not dates. <ins style="color: red; font-weight: bold; text-decoration: none;"> </ins>There <ins style="color: red; font-weight: bold; text-decoration: none;">are three ways </ins>to <ins style="color: red; font-weight: bold; text-decoration: none;">deal with </ins>this<ins style="color: red; font-weight: bold; text-decoration: none;">:</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#:#Control-click or right-click on the '''DateFirstSeen''' </ins>column <ins style="color: red; font-weight: bold; text-decoration: none;">of the query results and choose {{Menu|Column Format...}} from the contextual menu. Set the {{Section|Category}} </ins>to <ins style="color: red; font-weight: bold; text-decoration: none;">{{prefName|Date}} and choose the Desired {{Section|Format}}. </ins> <ins style="color: red; font-weight: bold; text-decoration: none;">Click {{Button|OK}}</ins>. <ins style="color: red; font-weight: bold; text-decoration: none;">(This works even if </ins>the <ins style="color: red; font-weight: bold; text-decoration: none;">Query hasn't been opened in design mode)</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#:#Use the CAST function as described [[#Using Cast in SQL Direct Mode|below]]</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#:#Correct the issue only in forms using the query </ins>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.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#:*The '''SightingsCount''' column indicates how many times a given bird has been identified.</td><td> </td><td style="background: #eee; font-size: smaller;">#:*The '''SightingsCount''' column indicates how many times a given bird has been identified.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}.</td><td> </td><td style="background: #eee; font-size: smaller;">#Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}.</td></tr>
<tr><td colspan="2" align="left"><strong>Line 30:</strong></td>
<td colspan="2" align="left"><strong>Line 33:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">==Notes==</td><td>+</td><td style="background: #cfc; font-size: smaller;">==<ins style="color: red; font-weight: bold; text-decoration: none;">Other </ins>Notes==</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&diff=12354&oldid=prevJgd: botlangbar -> Fr page2007-10-15T11:53:52Z<p>botlangbar -> Fr page</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 11:53, 15 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 34:</strong></td>
<td colspan="2" align="left"><strong>Line 34:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*You cannot add fields to a Group query that are not assigned a function.</td><td> </td><td style="background: #eee; font-size: smaller;">*You cannot add fields to a Group query that are not assigned a function.</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">{{botlangbarEN|[[Fr:Utiliser la Fonction Groupe dans une Requête|Français]]}}</td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&diff=12328&oldid=prevLorinda: /* Notes */ fix grammar of a sentence2007-10-13T13:04:25Z<p><span class="autocomment">Notes -</span> fix grammar of a sentence</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 13:04, 13 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 33:</strong></td>
<td colspan="2" align="left"><strong>Line 33:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td><td> </td><td style="background: #eee; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">*You cannot add <del style="color: red; font-weight: bold; text-decoration: none;">any </del>fields to a Group query <del style="color: red; font-weight: bold; text-decoration: none;">except those fields to which you assign </del>a function.</td><td>+</td><td style="background: #cfc; font-size: smaller;">*You cannot add fields to a Group query <ins style="color: red; font-weight: bold; text-decoration: none;">that are not assigned </ins>a function.</td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&diff=12317&oldid=prevLorinda: Add count column and alias rows to query; add Notes section2007-10-13T01:14:13Z<p>Add count column and alias rows to query; add Notes section</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 01:14, 13 October 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 1:</strong></td>
<td colspan="2" align="left"><strong>Line 1:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">{{TBBA Header}}</td><td> </td><td style="background: #eee; font-size: smaller;">{{TBBA Header}}</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">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. <del style="color: red; font-weight: bold; text-decoration: none;">To do so:</del></td><td>+</td><td style="background: #cfc; font-size: smaller;">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. </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"> </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">==Creating the Query==</ins></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#In the main database document, click on the {{prefName|Queries}} icon in the left hand column.</td><td> </td><td style="background: #eee; font-size: smaller;">#In the main database document, click on the {{prefName|Queries}} icon in the left hand column.</td></tr>
<tr><td colspan="2" align="left"><strong>Line 9:</strong></td>
<td colspan="2" align="left"><strong>Line 11:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Close the {{Window|Add Table of Query}} window by clicking {{Button|Close}}</td><td> </td><td style="background: #eee; font-size: smaller;">#Close the {{Window|Add Table of Query}} window by clicking {{Button|Close}}</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Adjust the {{Window|Sightings}} mini-window as necessary to see the full list of fields.</td><td> </td><td style="background: #eee; font-size: smaller;">#Adjust the {{Window|Sightings}} mini-window as necessary to see the full list of fields.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#Add the following <del style="color: red; font-weight: bold; text-decoration: none;">three </del>fields to the Query, in the order given:</td><td>+</td><td style="background: #cfc; font-size: smaller;">#Add the following fields to the Query, in the order given:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#:'''AOU_ID'''</td><td> </td><td style="background: #eee; font-size: smaller;">#:'''AOU_ID'''</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#:'''Bird_Name'''</td><td> </td><td style="background: #eee; font-size: smaller;">#:'''Bird_Name'''</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#:'''Date'''</td><td> </td><td style="background: #eee; font-size: smaller;">#:'''Date'''</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#:'''Date''' (you will have two columns headed with the '''Date field''')</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#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.)</td><td> </td><td style="background: #eee; font-size: smaller;">#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.)</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#In the {{prefName|Sort}} row, select {{prefName|ascending}} under the '''AOU_ID''' column.</td><td> </td><td style="background: #eee; font-size: smaller;">#In the {{prefName|Sort}} row, select {{prefName|ascending}} under the '''AOU_ID''' column.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#In the {{prefName|Function}} row, under the '''Date''' column, select {{prefName|Minimum}} from the dropdown.</td><td>+</td><td style="background: #cfc; font-size: smaller;">#In the {{prefName|Function}} row, under the <ins style="color: red; font-weight: bold; text-decoration: none;">first </ins>'''Date''' column, select {{prefName|Minimum}} from the dropdown.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">#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<del style="color: red; font-weight: bold; text-decoration: none;">, however, </del>that the '''<del style="color: red; font-weight: bold; text-decoration: none;">MIN("Date")</del>''' 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 '''<del style="color: red; font-weight: bold; text-decoration: none;">MIN ("Date")</del>''' field. This is left as an exercise for the student.</td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#In the {{prefName|Alias}} row, under the first '''Date''' column, type <tt>DateFirstSeen</tt></ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#In the {{prefName|Function}} row, under the second '''Date''' column, select {{prefName|Count}} from the dropdown.</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#In the {{prefName|Alias}} row, under the second '''Date''' column, type <tt>SightingsCount</tt></ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">#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. </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#:*</ins>The rows will be sorted by '''AOU_ID''' order, and there will only be one entry for each bird species. </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#:*</ins>Note that the '''<ins style="color: red; font-weight: bold; text-decoration: none;">DateFirstSeen</ins>''' 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 '''<ins style="color: red; font-weight: bold; text-decoration: none;">DateFirstSeen</ins>''' field. This is left as an exercise for the student<ins style="color: red; font-weight: bold; text-decoration: none;">.</ins></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">#:*The '''SightingsCount''' column indicates how many times a given bird has been identified</ins>.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}.</td><td> </td><td style="background: #eee; font-size: smaller;">#Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#In the window that appears, give your query the name '''Life_List'''</td><td> </td><td style="background: #eee; font-size: smaller;">#In the window that appears, give your query the name '''Life_List'''</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td><td> </td><td style="background: #eee; font-size: smaller;">#Click {{Button|OK}}</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">==Notes==</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">*The Maximum, Average, and Sum functions work similarly. They all require you to group your data first, as we have done above. </td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">*You cannot add any fields to a Group query except those fields to which you assign a function.</td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=Using_the_Group_Function_in_a_Query&diff=12316&oldid=prevLorinda: write article2007-10-13T00:57:51Z<p>write article</p>
<p><b>New page</b></p><div>{{TBBA Header}}<br />
<br />
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:<br />
<br />
#In the main database document, click on the {{prefName|Queries}} icon in the left hand column.<br />
#Click on the {{prefName|Create Query in Design View}} icon in the {{Section|Tasks}} section. The {{Window|Query Design}} window opens and the {{Window|Add Table or Query}} window opens.<br />
#In the {{Window|Add Table of Query}} window, click on the {{prefName|Queries}} radio button<br />
#Select the {{prefName|Sightings List}} query and click {{Button|Add}}<br />
#Close the {{Window|Add Table of Query}} window by clicking {{Button|Close}}<br />
#Adjust the {{Window|Sightings}} mini-window as necessary to see the full list of fields.<br />
#Add the following three fields to the Query, in the order given:<br />
#:'''AOU_ID'''<br />
#:'''Bird_Name'''<br />
#:'''Date'''<br />
#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.)<br />
#In the {{prefName|Sort}} row, select {{prefName|ascending}} under the '''AOU_ID''' column.<br />
#In the {{prefName|Function}} row, under the '''Date''' column, select {{prefName|Minimum}} from the dropdown.<br />
#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.<br />
#Save your query by clicking on the {{Button|Save}} button on the toolbar or hitting {{key|Command-S}}.<br />
#In the window that appears, give your query the name '''Life_List'''<br />
#Click {{Button|OK}}</div>Lorinda