Query count for all entries in a combo box

[cjones]

I need a count for every option in my combo box even if the count is 0. Is this possible? I tried changing the
"output all fields" under the query properties but get an error. I'm fairly new to advanced reporting & have just finished an SQL book but not sure what to do with the knowledge.

Below are the entries in my combo box. We usually only have 3-4 of the following selections BUT this goes into a federal report & I need every item to show up even if it has never been selected.

Race
a. American Indian/Alaskan Native
b. Asian
c. Black or African American
d. Native Hawaiian or other Pacific Islander
e. White
Multi-Race f. American Indian/Alaskan Native & White
Multi-Race g. Asian & White
Multi-Race h. American Indian/Alaskan Native or Black or African American
Multi-Race I. Other multiple race

needed outcome
4 a. American Indian/Alaskan Native
1 b. Asian
0 c. Black or African American
0 d. Native Hawaiian or other Pacific Islander
7 e. White
etc...

current outcome
4 a. American Indian/Alaskan Native
1 b. Asian
7 e. White
etc...

Any help suggestions are appreciated!!
Thanks!
Christine

 

[PHV]

Can you please post the SQL part of the query you have so far ?
I guess you have to do a LEFT JOIN with the table containing all the races ('a' thru 'i')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

I would combine your current query into a UNION query that merges in the missing combo items. Are you items a value list or have you created small tables with the items in them?

Post back with your SQL for your rollup query and I will try to give you something close to what you need.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[lespaul]

you have a table: tblRace

you have a table: tblSurveyAnswers that includes a FK to RaceID in tblRace

SELECT tblRace.RaceDescription, Count(tblSurveyAnswers.RaceID) As Number of Answers from tblSurveyAnswers LEFT JOIN tblRace on tblRace.RaceID = tblSurveyanswers.RaceID
GROUP BY RaceDescription

Something like that should work.

HTH

leslie