CODEWITHSUNDEEP
sqlms-access
Barry Connolly
Barry Connolly

Reputation: 663

Microsoft Access Count if

I have an access table with several columns that is populated from ms access form.

I need to count the total of each column where the value is Y or N and not NA

Here is my query 

SELECT 
Count(tableName.[ColumnName] ='y' ) AS MyTotal
FROM tableName;

The query is returning the total number of rows not just the ones with a Y in them.

Can anyone see what I am doing wrong

thanks in advance

Upvotes: 0

Views: 1070

Answers (1)

Mark C.
Mark C.

Reputation: 6450

You're putting your WHERE clause criteria in the wrong spot. Really, you want a COUNT of your column, WHERE the column equals some value.

Try this:

SELECT Count(YourColumn) AS MyTotal
FROM YourTable
WHERE YourColumn='y'

I suppose if you were looking for another solution, this should work as well.

SELECT SUM(IIf(YourColumn = 'y', 1, 0)) AS MyTotal
FROM YourTable

What the IIf does is it gives a value of 1 to each record where your column equals 'y', and a 0 to the columns that don't fit that criteria. Then to replicate the COUNT functionality, we simply SUM them up.

Upvotes: 6

Related Questions