Setting up "count" functions for a categories field in a report

[Tmcgee923]

I have a categories field in several tables that have drop downs in them.
How do I create a report that will calculate each of the categories included
in the drop downs.

Below is a sample of what I get vs. what I want it to look like.

This is what I'm getting . . .

Attendance
Behavior
Performance


This is what I want . . .


Metrics Table
TOTALS
Terminations
Voluntary (This is in drop down on form) 10
Involuntary (in drop down on form) 10
Total terminations 20

Unemployment Claims
Win (in drop down on form) 10
Loss (in drop down on form) 10
Total Unemployment Claims 20

Performance
Attendance (in drop down on form) 10
Behavior (in drop down on form) 10
Performance (in drop down on form) 10
Total Performance Issues 30

Compliance
Compensation/salary (in drop down on form) 10
Policy Interpretation (in drop down on form) 10
Policy Development (in drop down on form) 10
Total Compliance Issues 30

NOTE: Being overly confident in my ability to get this figured out, I've
committed to having this in by tomorrow. If anyone can help me, I'd greatly
appreciate it. You can call my work number at 515-471-3122

Thank you so much - Tamra

 

[KARL DEWEY]

Use a union query followed by a totals query.
Then in the report use Group and Sorting with Group Header and Footers.
Union query named qryTableUnion --
SELECT "Metrics" AS [Table Name], Categories
FROM Metrics
UNION ALL SELECT "Terminations" AS [Table Name], Categories
FROM Terminations
UNION ALL SELECT "Unemployment Claims" AS [Table Name], Categories
FROM [Unemployment Claims]
UNION ALL SELECT "Performance" AS [Table Name], Categories
FROM [Performance]
UNION ALL SELECT "Compliance" AS [Table Name], Categories
FROM [Compliance];

Then totals query qryCategoriesTotals --
SELECT [Table Name], Categories, Count([Categories]) AS CategoriesCount
FROM qryTableUnion
GROUP BY [Table Name], Categories;