How to count numhbers of different columns and distinct values

[Nouf]

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?

[Nouf]

http://tinypic.com/view.php?pic=257qamw&s=8

[Rabbit]

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.