Counting column values

[andrew_sheffer]

I have a table with 45 columns(c1, c2, c3....) and about 200 rows (which are names). Each column has three possible values p,a,n/a. How can I get a count of values types per column?

Andy

 

[Andrew Fergus]

Hello and welcome.

I managed to get your example to work by creating a new table which had just the one field (in my case I called it "possible") and three rows of data (i.e. "a", "p" & "n/a") - you may already have this table in your database (being the possible values for the 45 columns). It may also be possible to do this wihtout creating this table, but I set this up for simplicity purposes.

Create a new query, add the new table (with the 3 possible values) and the original table (with the 45 columns), do not link the tables, view totals, add the "possible" variable and the 45 columns to your query.

Change c1 to :

Field = 1 : IIf([c1]=[possible],[c1],Null)
Total = Count

Change c2 to :

Field = 2 : IIf([c2]=[possible],[c2],Null)
Total = Count

...and so on for the 45 columns.

Save and run.

HTH, Andrew.

 

[andrew_sheffer]

Thanks that worked. I'll be sure to keep it as a reference.

Andy