Counting values in a field (1 Viewer)

[Neal]

I have a number of fields in a table that contain values ranging from 1-5. What I need to do is count the number of times each value is used in each field.
I can do it for one field in a totals query by putting the field in twice and setting one field as "count" and the other as "group by."
And, I can do it in a report by putting text boxes and setting the control source as a dcount expression.
But, there are several dozen of these fields and I am hoping for an easier way.
I keep thinking a crosstab query would work, but haven't had much luck. Ultimately, that is the format I am looking for:
Field names as Row Headers, values as column headers and totals as the data.
Anybody have any ideas?
Thanks,
Neal

 

[Pat Hartman]

If you are storing the same data in multiple columns, your table structure is incorrect. It sounds like you have a one-to-many relationsip that you have arbitraily limited to 5 occurances. In any event, to do the count you want, you'll have to create a union query and then a count query that takes the union query as input.

Select Fld1 AS CommFld From YourTable
Union All Select Fld2 AS CommFld From YourTable
Union All Select Fld3 AS CommFld From YourTable
Union All Select Fld4 AS CommFld From YourTable
Union All Select Fld5 AS CommFld From YourTable;

Select CommFld, Count(*) As CountOfCommFld
From UnionQuery
Group By CommFld;

 

[Neal]

Pat,
This is another problem I am having with the evaluation database you helped me on before.
There are a series of qestions about employees that their bosses must answer with Outstanding, Very Good, Good, Adequate, Weak.
The responses are stored as numbers: Outstanding = 1, Very Good = 2, etc.
What I need to do in my report is have a graph that shows the total number of employees who got 5 or 4 or 3, etc., for each of the questions.

 

[Pat Hartman]

You'll need 5 queries. One to count the various values of each Question.

Select Q1Value, Count(*) As CountOfQ1
From YourTable
Group By Q1Value;

Each query will produce the following type of list:

1 25
2 38
3 95
4 45
5 27