Problems with Count(*)

[ceebee]

Hello

This is a very basic question, but I have to ask it.

I am trying to do a count(*) using the where clause.

For example:

SELECT count(*)
FROM table
WHERE id = 5

I do not think I can use a count(*) with a where clause.



Can someone please advise?

Thank You

[aytee111]

Create the query in query design, Access will do the SQL for you, which you can then copy and paste.

[ceebee]

Yes I tried that, but that is where I got the error message stating I can't use a 'where' clause when using a count(*)

[June7]

Use HAVING in place of the WHERE because filter is applied to an aggregate calc field. Query Designer should do this for you, not SQL View.

[John_G]

It must be something else, because I can use

SELECT Count(*) AS Expr1
FROM competency_instance
WHERE (((competency_instance.MA_cde)=1));

in the query definition SQL and it works fine.

In what context are you using that SQL?

[June7]

Ooops, if it had been something like Sum([field]) and wanted to apply filter to that calc, use HAVING.

[ceebee]

OK Thank You

I tried using the same count/where clause as in your example, and it worked.

I did not have the ((())) the last time. I am guessing that is what was wrong?!

[JoeM]

I did not have the ((())) the last time. I am guessing that is what was wrong?!

Access goes a little crazy with the parentheses. Many times, it adds a lot more than it actually needs.
For example, in this line here:

WHERE (((competency_instance.MA_cde)=1));

since there is only one simple condition, it doesn't really need any and can simplified to:

WHERE competency_instance.MA_cde=1;

It doesn't hurt to have them in there, I just hate the way it looks.