HAVING Clause - Access - Microsoft Office Online

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

HAVING Clause

Applies to: Microsoft Office Access 2007

Specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause. After GROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.

Syntax

SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
[HAVING groupcriteria]

A SELECT statement containing a HAVING clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases (alias (SQL): An alternative name for a table or field in expressions. Often used to shorten the table or field name for subsequent references in code, to prevent possible ambiguous references, or to provide a more descriptive name in query output.), SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP ), or other SELECT statement options.
table	The name of the table from which records are retrieved.
selectcriteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Access database engine groups values after applying the WHERE conditions to the records.
groupfieldlist	The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
groupcriteria	An expression that determines which grouped records to display.

Remarks

HAVING is optional.

HAVING is similar to WHERE, which determines which records are selected. After records are grouped with GROUP BY, HAVING determines which records are displayed:

SELECT CategoryID,
Sum(UnitsInStock)
FROM Products
GROUP BY CategoryID
HAVING Sum(UnitsInStock) > 100 And Like "BOS*";

A HAVING clause can contain up to 40 expressions linked by logical operators, such as And and Or.