Difference between two queries

[MikeB]

I'm constructing a simple query and depending on how I do it in the
Access design window, I get different results. The SQL for the two
queries are hereunder, I don't understand the difference between the
"HAVING" and the "WHERE" clauses. can someone please enlighten me?

Query 1:
SELECT Count(Games.IsBye) AS CountOfIsBye
FROM Games
HAVING (((Count(Games.IsBye))=Yes));

Yields a count of 232. (that is all the records in the table,
regardless of the value of the IsBye field).

Query 2:
SELECT Count(Games.IsBye) AS CountOfIsBye
FROM Games
WHERE (((Games.IsBye)=Yes));

Yields a count of 32, which is the number of games that has "Yes" in
the IsBye field.

 

[MikeB]

I'm constructing a simple query and depending on how I do it in the
Access design window, I get different results. The SQL for the two
queries are hereunder, I don't understand the difference between the
"HAVING" and the "WHERE" clauses. can someone please enlighten me?

Query 1:
SELECT Count(Games.IsBye) AS CountOfIsBye
FROM Games
HAVING (((Count(Games.IsBye))=Yes));

Yields a count of 232. (that is all the records in the table,
regardless of the value of the IsBye field).

Query 2:
SELECT Count(Games.IsBye) AS CountOfIsBye
FROM Games
WHERE (((Games.IsBye)=Yes));

Yields a count of 32, which is the number of games that has "Yes" in
the IsBye field.

Oh crickey, now that I've posted it, I see it. Sorry everyone.

 

[MikeB]

Actually, no, I'm still a little confused.

These two queries yield identical results:

Query 1:

SELECT Count(Games.IsBye) AS CountOfIsBye
FROM Games
HAVING (Games.IsBye=Yes);

Query 2:

SELECT Count(Games.IsBye) AS CountOfIsBye
FROM Games
WHERE (((Games.IsBye)=Yes));

The help on the HAVING clause says:
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.

Does this mean that the WHERE clause is more efficient than the HAVING
clause since records are retrieved and calculations performed for the
statement using the HAVING clause that are ultimately discarded
whereas for the WHERE clause, only the relevant records are selected
from the table?

 

[Douglas J. Steele]

Yes. it's usually more efficient to use the WHERE clause to reduce the
number of records on which the accumulation will be done.

The only time you should need a HAVING clause is when you want to base the
criterion on the calculated value.