Counting records in a query?

[Kurtosdf]

I want to generate a report that will count about 20 different records. For instance I want it to show how many males there are, how many females there are, etc.

I can get it to work by having a count query for each count and then making every count into a subreport. This means having 20 queries and 20 subreports which to say isn't too efficient.

The problem I run into is if I count say gender and ethnicity in the same query I get rusults similar to:

Ethnicity CountofEthnicity Gender CountofGender
white 1 male 1
spanish 1 male 1
black 1 male 1

I need it to give me totals of each group - not broken down. I have played with grouping in my report but cannot get the desired results.

Thanks in advance
Kurto

 

[sdk]

Hi,

Have a little trouble understanding your outline of your problem, but here are some suggestions:

If you are trying to find the number of each sex in each group, your solution sounds like a cross-tab query. Make the (grouped) ethinic field a row heading, the (grouped)
sex field the column heading, and a count of your key field as the value. To provide totals overall may require a second query, or possibly use controls on your report - but be aware: as new records are added to your table, the output of the cross-tab query may change which will impact upon any reports which draw from it!

An alternative might be go without the queries, and use the group/sort features of the report itself. You can still include a count of records in the footer of each group of each sex etc. Sometimes drawing a report directly off a table is easier than mucking around with queries anyway.

Cheers & happy days