A Keyword Counting Query

[Jeepers321]

This is going to sound like a simple query and it may be but its giving me a headache. In one of the rows, the information is being added to the table is coming in now piticular order. An example would be: There are 3 choices a 1, a 2, or a 3. This is entered by many different people and this is what you usually get.
Record # Choices
1 123
2 1,3
3 32
4 1,3,2
5 2,3
6 3
Well, I think you get the picture. My problem is I need to do a count query to count the number of records with a 1, the number of records with a 2, and the number of records with a 3. Where is this case there would be 3 records with a 1, 4 Records with a 2, and 6 records with a 3.
I have been able to get this with a seperate query for each number but is there a way i can get it done in won query because I need to run a chart on it.
Thanks for anyones help.

 

[culshaja]

Try the following:

SELECT sum(iif(instr(,"1",[Choices]),1,0)) AS NumOnes,
sum(iif(instr(,"2",[Choices]),1,0)) AS NumTwos,
sum(iif(instr(,"3",[Choices]),1,0)) AS NumThrees FROM [MyTable]

The purpose of the IIF is to check if the value exists within the string. If it does return 1, else return 0. Sum these values and you get the number of occurences. This is useful as it does the calculation for each value only once per row.

I might be wrong and you might not need the " around the 1,2, or 3.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305