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.
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:
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:
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: having clause, microsoft access queries, ms access queries, using the groupby query, where clause
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
Please provide the syntax you have attempted so I can check it out?