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

How to count numhbers of different columns and distinct values

P: 2
I have one table that has 27 columns, each column contains a value of 0, 1, or 2 AS (Met, Not Met, Not Applicable)
I want to do a query to calculate the total number of Met, Not Met, Not Applicable for each column..

like this:

item1 item2 ....... item27
Met 20 17 55
Not Met 3 35 ....... 4
Not Applicable 0 74 ....... 10


Any suggestions?
Mar 23 '15 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Our firewall blocks image sharing sites. It would be better if you attached the image to the thread itself.

Your data is highly unnormalized and that makes this type of query very unwieldy. You should think about fixing your data structure to save yourself a lot of headache in the future. Here is our tutorial on normalization: http://bytes.com/topic/access/insigh...ble-structures

If you can fix your data, then the query is a simple crosstab where you pivot by the item, group by the status, and do a count.

If you can't fix the data, then you need to fake the normalization by using an intermediary query where you union 27 subqueries, one for each item. Then you do the query above.
Mar 23 '15 #3

Post your reply

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