By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Count Function Across Multiple Columns

P: 2
I am trying to count the number of Pass and Fails across several different columns.
For example, I have 10 columns that each result in a value Pass or Fail. I want a query that gives the count of all of each of those columns broken down by # of Pass and # of Fails.

I used this code to count all of the Fails on 1 column:
SELECT COUNT(CO_mtm) AS CO_mtm_FAIL FROM FXCompare
WHERE CO_mtm='FAIL'

This gives me all of the fails for column CO_mtm. However, how can I continue the SQL code to give me the # of fails for all the other columns i.e. CO_log, CO_otm, CO_date, etc. etc.

THANKS.
Mar 16 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,334
Your data is poorly structured and should be normalized. As it is, it's going to make your queries more complex.

Don't use a count, use a sum. And use the IIf function to assign a 1 for a pass and a 0 for a fail, if you're counting passes. Use the opposite for a fail count column.
Mar 16 '12 #2

P: 2
Thanks for reply. Unfortunately I'm a newbie with Access SQL and coding so appreciate your patience.
Can you please explain in more detail (maybe code example?) how this would work.
The problem I am having is doing and displaying this across multiple columns. I.e. I want to see the count of all the fails for ONLY the CO_mtm column, all the fails for ONLY the CO_log column, etc. etc.

So my end result would be something like this:
CO_mtm CO_mtm Count CO_log CO_log Count
FAIL 54 FAIL 28
PASS 7 PASS 53


Thanks so much for your help.
Mar 16 '12 #3

Rabbit
Expert Mod 10K+
P: 12,334
Since you're new to SQL, the first thing you should do is find a good tutorial and learn SQL. Once you have that under your belt, you can implement my suggestion and post back here if you run into any errors.
Mar 16 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.