Combine Reports

[Little Penny]

I have a table that consists of 10 fields. Six of the fields are
populated from a drop down list. Some of the lists have as many as 12
choices to choose from and some, as little as 5. I want to create a
report that will show me the number of time each one of the possible
choices in that particular field has been chosen. All six fields are
text. Currently I have six different reports each based on a different
query. What I have done for each of the reports is created a header
group and put the field I want to count into the header group plus a
=Count("*") control text box. This gets me the data but only for one
field. I then export each report to an excel file and combine the
results from all six. Is there a way to combine this into one query to
populate one report?



Any help would be greatly appreciated

Thanks




Little Penny

 

[Duane Hookom]

I expect you could use a union query to normalize your data like:
SELECT 1 as TheFld, Field1 as TheValue, Count(*) As NumOf
FROM tblNoName
GROUP BY 1, Field1
UNION ALL
SELECT 2, Field2, Count(*)
FROM tblNoName
GROUP BY 2, Field2
UNION ALL
SELECT 3, Field3, Count(*)
FROM tblNoName
GROUP BY 3, Field3
UNION ALL
--- etc ---
SELECT 6, Field6, Count(*)
FROM tblNoName
GROUP BY 6, Field6;

Then, you can build a report from this union query and sort/group by TheFld.

Duane Hookom
MS Access MVP