Query - Counting multiple items in one query 2

[DAmoss]

I have a table which contains a field called 'Choice', this field is linked to another table containing 1-20 possible options. When the dropdown field is picked, it puts a value between 1=20 into the 'Choice' field, works fine.

What I want to do is be able to count howmany of each of the choices there are in my data table. How do I count all these choices from within one query?

At the moment I have a count query for each one, surely there must be a way to make this simpler.

 

[gmmastros]

Select Sum(IIF(Choice=1, 1, 0)) As Choice1,
       Sum(IIF(Choice=2, 1, 0)) As Choice2,
       Sum(IIF(Choice=3, 1, 0)) As Choice3,
       Sum(IIF(Choice=4, 1, 0)) As Choice4, 
       Etc...
From   Table

Warning: If you EVER add more choices to the list of choices, this code will have to change to accomodate it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Remou]

Why cannot Choice be used to Group By?
[tt]SELECT tblTable.Choice, Count(tblTable.ID) AS CountChoice
FROM tblTable
GROUP BY tblTable.Choice;[/tt]
What point have I missed?

 

[Golom]

Another possibility is the Integers table. Build a table named "Integers" with a field called "Num" (Type Long) and populate it as follows
[tt]
Num
1
2
3
:
18
19
20
[/tt]
then

SELECT A.Choice, Count(*) As NumberOfChoices
FROM myTable As A INNER JOIN  Integers AS I 
     ON A.Choice = I.Num
Group By A.Choice

 

[DAmoss]

Thank you, easy when you know how!

I was also wondering ...

Is there a way to link my lookup table so that it pulls in the actual choice descriptions instead of 'Choice1', 'Choice2' etc;

 

[DAmoss]

Remou, should have explained more.

I need to show a total for all my possible choices even if it has not been chosen ie. 0 (zero) total.

 

[Remou]

You mention that you have a Choices table so why not (?):
[tt]SELECT tblChoices.Choice, tblChoices.ChoiceName, Count(tblTable.ID) AS CountChoice
FROM tblChoices LEFT JOIN tblTable ON tblChoices.Choice = tblTable.Choice
GROUP BY tblChoices.Choice, tblChoices.ChoiceName;[/tt]

Which is similar (or the same as) Golom's suggestion, I think.

 

[gmmastros]

DAmoss,

Remou's and Golom's suggestions are better than mine. I've been buried in a Data Mining Tool lately, and have lost my senses. In my app, my suggestion made sense since I am creating reports from data that will never change. I was successful in creating access queries to return data exactly as I wanted in my report, which gives me the best performance possible.

As I suggested in my post, this method is ONLY good when you won't be adding any more choices. This applies to my situation, but may not apply to yours.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DAmoss]

gmmastros,

Your original suggestion was actually v.good, my lookup lists are fixed (so they never change), you also gave me a way of assigning the totals to a label, so allowing me to further manipulate the totals when used in my reports.

Thank you very much!