The WHERE clause filters out records BEFORE the grouping occurs. The HAVING
clause filters records AFTER they have been grouped. In some cases, it
makes no difference, in others it makes a very big difference.
For instance, suppose I had an Employee table which listed (among other
things) their Department and JobTitle. If I wanted to find out how many
clerks I had by department, I would use a Where clause because I want to
restrict anyone else from being counted. My SQL would look like this:
SELECT Department, Count(*) as NumberEmployed FROM Employee
WHERE JobTitle = "Clerk"
GROUP BY Department
This query will weed out all of the employees that are NOT clerks and count
them by Department.
By contrast, if I wanted to find out which departments had more than 20
employees, I would use a Having clause like this:
SELECT Department, Count(*) AS NumberEmployed
FROM Employee
GROUP BY Department
HAVING Count(*)>20;
This will count up all the employees in each department, but only display
those departments whose grouped value is >20.
To create the Where clause above in the query builder, do this:
Field: JobTitle
Table: Employee
Total: Where
Criteria: "Clerk"
NOTE: In order to have a WHERE clause, you must list the field name, and the
word 'Where' in Total row
To Create the Having clause above, in the query builder, do this:
Field: NumberEmployed:Count(*)
Table:
Total:
Criteria: >2
Another type of Having Clause involves putting a criteria in a GroupBy
field:
SELECT Department, Count(*) AS NumberEmployed
FROM Employee
GROUP BY Department
HAVING Department="sales";
To Create the Having clause above, in the query builder, do this:
Field: Department
Table: Employee
Total: Group By
Criteria: "Sales"
This type of HAVING will produce an identical result as a WHERE clause
(although the WHERE would be faster).
Now, your second question is altogether different. It DOES NOT WORK to add
additional field to the query in an aggregate query. All field MUST either
be grouped by or have an aggregate function.
I call this the Max Query Problem. Suppose you have a table with CustName,
TransactionDate, and Amount. Suppose further you want to know the amount of
the latest (Max) Transaction Date. It is not as simple as Grouping on
CustName, finding the Max date, and listing Amount field. This will give
you the error you mentioned. You must either group on Amount (which ruins
your grouping) or add some other aggregate function, like First. HOWEVER,
this WILL NOT return the amount corresponding to the max transaction date.
Explaining this (and showing how to solve it) works better with an example.
On my website (
www.rogersaccesslibrary.com) there is a small sample database
called "MaxQueryProblem.mdb", which illustrates the problem and provide 2
solutions.
--
--Roger Carlson
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L