Count Problem

[campo88]

Hi

I have a table with 10 different options.

I also have a table which contains multiple records for users with SIX option columns. These option columns will each have one of the 10 different options selected by a user.

How can I count the number of times an option has been chosen? Is there a quick query I can do. I can't seem to get anything to work

 

[Rabbie]

Hi

I have a table with 10 different options.

I also have a table which contains multiple records for users with SIX option columns. These option columns will each have one of the 10 different options selected by a user.

How can I count the number of times an option has been chosen? Is there a quick query I can do. I can't seem to get anything to work

It doesn't sound if your table with 6 option columns is normalised. If you don't have normalised data then you make things difficult for yourself.

 

[campo88]

tell me about it ...problems. Dont know how to sort it whatsoever!

 

[Brianwarnock]

I'm not sure that I follow, I have this picture of a table of say 10 colours, and then a table of 6 rooms each painted one of the colours and you want to know how many times each colour has been used.

I'd export the result of the query to Excel and use Countif on the array.

Brian

 

[campo88]

Ok...Picture this

A table with 10 different sports

Another table with 6 people each choosing 3 different sports.

I want to count how many times a sport is chosen.

Hope this helps. I cant use excel, must use access to calculate or show results.

 

[Brianwarnock]

What I am about to propose stinks but works with the numbers involved.
Call the sports table Table1 and the fields s1-s10, call the other table Table2 and the fields fsp1-fsp3 then
but I will just use 3 fields from the sports table but you will get the idea

SELECT Sum(IIf([fsp1]=[s1] Or [fsp2]=[s1] Or [fsp3]=[s1],1,0)) AS counts1, Sum(IIf([fsp1]=[s2] Or [fsp2]=[s2] Or [fsp3]=[s2],1,0)) AS counts2, Sum(IIf([fsp1]=[s3] Or [fsp2]=[s3] Or [fsp3]=[s3],1,0)) AS counts3
FROM Table1, Table2;


Brian