Having Clause in Access Query


SQL Having Clause

SQL having clause is a SQL function used in conjunction with the Group By clause to restrict the records retrieved based on a criteria applied to the aggregate function result values defined in the query.

The basic syntax of the SQL Having clause is:

SQL Having some criteria

The SQL having clause is optional and is entered as the last clause of a query, after the group by clause.

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

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

Now an SQL Having example with real column and table names:

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

The above query will retrieve unique employees having daily absences days totaling more than 3 during the 1st quarter of 2006 and displays the average daily work hours during the 1st Q.

More Access Having Clause Examples:

Access Having Function with Group By

Access Having Clause: Update Master Records Based on Detail Record Fields

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


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