How do I find the number of specific values in a row in Access? For example, how many 0's in Bill's row:
Name Jan Feb Mar Apr May Dec No of 0's Bill 0 1 2 0 1 0 ? Sam 1 2 1 0 1 2 ?
You must be the same person who submitted the question in my previous answer Cumulative sum across the row, not down the column (29 November 2004).
I did not mention this then, but I'll mention it now: you really need to redesign your table. If you don't, the query that you want to count the number of 0's across the row is just as ungainly (if not more so) as the query in that answer, especially if there are 12 columns, one for each month (your example above seems to be missing Jun, Jul, Aug, Sep, Oct, and Nov).
Now imagine if your table looked like this:
Name Mth Amt Bill Feb 1 Bill Mar 2 Bill May 1 Sam Jan 1 Sam Feb 2 Sam Mar 1 Sam May 1 Sam Dec 2
Your query to find the 0's -- which are not actually stored at all! -- would be as simple as this:
select Name , 12 - count(*) as missing from yourtable group by Name
And if you need that nice year-at-a-glance layout for a report, Access has a fine crosstab feature built in (although adding "No of 0's" as the rightmost column in a crosstab would be a bit of a challenge).
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.