Problem solve Get help with specific problems with your technologies, process and projects.

Number of values across a row

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
    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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.