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


This was last published in February 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close