fbpx
Logo
Mail us
ben@accessdatabasetutorial.com
accessdatabasetutorial
Home » MS Access » Microsoft Access Queries: Difference Between The Where Clause And the Having Clause

Microsoft Access Queries: Difference Between The Where Clause And the Having Clause

Microsoft Access Queries: Difference Between The Where Clause And the Having Clause


Within the framework of using Ms Access queries, the HAVING clause can only be used with the GROUP BY type SQL statement which is also referred as the Groups and Totals query. The HAVING keyword when not used with the GROUP BY statement acts as a standard WHERE clause.

The HAVING clause specifies a condition that is similar to the purpose of a WHERE clause when  applying criteria but the two clauses are not interchangeable.

Ms Access

The key difference between these two keywords is down to one keyword; Aggregation!

Ms Access Queries: Difference Between The Where Clause And the Having Clause

The WHERE clause specifies the criteria which individual records must meet to be selected by the query. It can be used with or without the GROUP BY clause. However, the HAVING clause cannot be used without the GROUP BY clause.

Also, the WHERE clause selects rows before it applies any grouping where as the HAVING keyword selects rows after grouping takes place.

The WHERE clause cannot contain any aggregate functions but the HAVING keyword can contain aggregate functions like Sum, Count, Avg etc.

The following SQL statement will group all customers in the ‘UK’ using the GROUP BY keyword even which will detail individual records due to the unique ID included:

SQL Statement…

SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.City, Customers.Country 
FROM Customers 
GROUP BY Customers.[Customer ID], Customers.[Company Name],
Customers.City, Customers.Country 
HAVING   ((Customers.Country)="uk"));

The same effect excluding the GROUP BY clause using the WHERE clause:

SQL Statement…

SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.City, Customers.Country FROM Customers 
WHERE (((Customers.Country)="uk"));

When using one of the aggregate functions, you can pass criteria too using the HAVING clause only. See example below which only returns a count of customers that exceed 5 hits for each country:

SELECT Count(Customers.[Customer ID]) AS [CountOfCustomer ID],
Customers.Country FROM Customers  
GROUP BY Customers.Country 
HAVING (((Count(Customers.[Customer ID]))>5));

This wouldn’t work with the WHERE clause as the GROUP BY is needed to first collect and group records then apply an aggregate function (count in this example) and optionally pass a filter; ‘>5’ to it after the grouping of rows or records.

This is a simple example but hopefully the point is made. Another variation of comparing the GROUP BY and WHERE clause can also expand your knowledge.

To learn more about queries, consider one of my eBooks on A Guide to More Access Database Queries which covers different types of queries.

Tags: , , , ,

2 Responses so far.

  1. simon says:

    Hi Ben
    I have an assignment to do using access and one the tasks requires the avg function to get the average of software cost. so thats one field in the second field i need to create a subquery using sql to create greater than the avg cost figure used in the first field. i have tried various syntax but i cannot get the code to work. could you help me by creating a detailed example
    kind Regards f