Summary break down and grand total

[Billiam]

I have Instructors in 8 regions. Each Instructor can posses up to three
qualifications (called H, R, and W).

I have a report by region showing:

How many instructors are in that region, and
How many instructors have qualification H, how many have R, and how many
have W.

I need to have a seperate report that simply shows by region the number of
instructors total in each region, and within each region how many hold each
qualification. Finally, there should be a Grand total of instructors, and
instructors per qualification.

How would I do this as i am using the count function in my original report
which also show's each individual occurence and instructor name...when all I
need is a summary chart.

Thank you for any help!
Billiam

 

[Duane Hookom]

How about providing your table and field names as well as sample records and
desired display in the report?

 

[Billiam]

Hi Duane,

What I am trying to accomplish is a chart like this:

Qualification R1 R2 R3 R4 R5 R6 R7 R8
GRAND TOTAL
__________

H 21 14 36 71 49 86 27 51
355

R 4 5 6 0 2 7 9
6 39

W 2 4 6 8 10 12 14
16 72

Instructors 45 55 100 150 50 100 200 100
800

Does that help? or do I still need to supply tables, fieldnames and sample
records?

Billiam

 

[Duane Hookom]

This looks like a crosstab query with Region as the Column Heading,
Qualification as the Row Heading, and count of Instructor as the Value. You
can use this query as the record source of a report and add report totals.

Let us know if you have questions.

 

[Billiam]

Hi Duane,

I thought the crosstab query would work, too, as it was what I was trying at
first, however, qualification only lists as Qualification1 q2 and q3...I have
these as seperate fields in the table as a yes/No data type...in other words,
i can only get H1 H2 H3 OR R1 R2 R3, OR W1 W2 W3, when I want H, W and R
only...any advice? I expect you will say they should be in a seperate
qualifications table, which is the plan for the new design, I just need to
live with the current design for now...

The report I have does provide these value summaries per region at the end
of each regions list, , however, I do not want the enduser to have to scroll
through the entire report to get them...perhaps there is another way to do
this from within my existing report that I am not aware of?

Thanks again for your help!

Billiam

 

[Duane Hookom]

Your structure is exactly why I asked earlier about your table and field
names and sample records. You structure is wrong. You can fix it temporarily
with a normalizing union query. If you don't know what this is, google it.

 

[Billiam]

Your structure is exactly why I asked earlier about your table and field
names and sample records. You structure is wrong.

Now you KNOW why I didn't tell you, LOL!

I will give the union query a try...any hints on the best way to proceed...I
am assuming i would have a seperate table with QualID as an autonumber PK,
and Qualification field, as well as the InstructorID as a foreign key...the
union query would join based on the InstructorID?
Billiam

You can fix it temporarily