Access Group By Clause

Group By SQL clause is a query command used to combine data based on the value(s) in the field(s) of a table(s).  The Group By clause is most often used in combination with one or more aggregate functions.

The basic syntax of the Group By statement is:

Group By TableName.Field1, TableName.Field2

The Access group by clause comes after the where clause of a query and before the optional having clause in the query:

Select TableName.FieldName1, Count(TableName.Field2),Avg(TableName.Field3) From TableName Where your criteriaGroup By TableName.FieldName1 Having some criteria.

Note: The ending semicolon is a required component of all SQL statements.

Now see the Access group by clause with real column and table names:

Select M_Employees.Emp_Name, Sum(iif(Daily_Absence=true,1,0)), Avg(Daily_Hours) From M_Employees Where M_Employees.Work_Date>=#01/01/05# and M_Employees.Work_Date<=#03/31/05# Group By M_Employees.Emp_Name Having Sum(iif(Daily_Absence=true,1,0))>3;

The above query will select distinct employee names whose daily absences total more than 3 during the 1st quarter of 2005 and will display the average of the hours worked per day during the same time period grouped by emp_name.

More Group By Query Examples and Discussion:

Aggregate Functions
Aggregates are most often used in combination with an SQL Group By clause. … Note there is no Group By which is normally associated with an aggregate function…

Having Clause SQL
SQL Having clause: The having clause is used in conjunction with the SQL group by clause and aggregate functions to categorize and summarize data into groups…

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
Contact Information

Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016