Help needed - want to count categories within a category on a repo

[Laura]

I have demographic information captured in a demographic table(5 categories
of ages - 24 to 34, 35 to 44, 45 to 54, etc., 5 categories of educational
level - high school, some college, etc., 5 categories of household income
level - under 25K, 26 to 50K, etc., 5 ethnic groups - caucasian, asian, etc.)
I have one field for age, one field for educ. level, one for ethnic group and
one for hh income within my demographic table. Drop-down boxes hold the
various values for EACH of these fields (see above).

I have several courses for which I need to show registrations (from my
registration table). I've grouped the report to break registration
information down by course (ENGL102, etc). For each course, I want to show
the TOTAL number of people whose age value is 24 to 34 (in the age field in
my demo table), TOTAL number of people ages 44 to 45, etc. and do this for
all of my demographic information (educ. level, ethnic group, hh income). I
can't figure out how to do this?! If I group it by age and try to count, it
gives me a total count on number of registrations BUT I want it broken down
(say 10 registrations, 3 of them are in 24 to 34 age range, 3 are in next age
range, etc.). So...I want to be able to count the number of people in each
choice within my demographic categories. Any help would be appreciated!!

 

[Duane Hookom]

I would use subreports based on totals queries. Set the Link Master/Child
properties to the Course primary key field.

If you need more direction, please come back with your significant table and
field names.

 

[LauraJane]

Duane:
Thanks for trying to help me!! Here is some additional information:

I have a query written as follows:

Field: RegTerm (from my registration table) - prompts user to enter a term -
- then displays results for that term only

Field: CourseNum (from course table)
Field: RegStatus (from registration table) - criteria is "E" only (for
enrolled)
Field: CourseDesc (from course table)
Field: SID (from registration table)
Field: FullName (from student demographic table)
Field: Gender (from student demographic table)
Field: Age (from student demographic table)
Field: EducLevel (from student demographic table)
Field: HHIncome (from student demographic table)
Field: EthnicGroup (from student demographic table)

Joins are as follows:

REGISTRATION & COURSES TABLE:
Left column: RegTerm (left table is registration)
Right column: CrsTerm (right table is courses)

Left column: CrsNum (left table is registration)
Right column: CrsNum (right table is courses)

REGISTRATION & STUDENT DEMOGRAPHIC TABLES:
Left column: SID (left table is registration)
Right column: SID (right table is student demographic)

I rarely use subforms but I'm open to trying it. As I said, I want to show
registrations by course and then totaled for each category within my
demographic values. For example, I'd like it to look like:

ENGL312
30 Total registrations
Ages 25 to 34 - 10
Ages 35 to 44 - 3
Ages 45 to 54 - 3
Ages 54 to 65 - 6
Ages 66+ - 3
5

High School - 10
Some College - 3
Associates Degree...

25K and under - 3
26 to 35 K - 4
etc., etc.

I need to be able to do this for ethnic group, household income, education
level, age & gender. NOTE: The information isn't mandatory so there will be
some people for whom we don't have ages (that's why I put a 5 above under the
age breakdown after the 66+ category - those 5 people didn't give us an age).
Does this make sense to you? Also, I can give you all of the actual
values/breakdowns of choices if you need them. I've been pulling my hair out
trying to get this done so I'd greatly appreciate ANY help from you!! Thanks!

 

[Duane Hookom]

First, IMO, don't use "prompts user to enter a term". All user interface
should be through controls on forms, never parameter prompts.

To get a display of Education, create a totals query like:

SELECT RegTerm, EducLevel, Count(*) as NumOf
FROM YourUnNamedQuery
GROUP BY RegTerm, EducLevel;

Use this query as the Record SOurce of a subreport. Insert the subreport
into the Report (or RegTerm) Footer of your main report.

Create similar totals queries to use a Record Sources of other subreports.

 

[LauraJane]

Duane:
Please explain further. I tried to write a totals query but I'm really
confused now. Can you be more specific? Where do I put the Count (*) as NumOf
information? I don't normally use these types of queries so I have no idea
what you are talking about. Should I put the reg term as the field name,
educlevel as the other field name and group by both of them?

Thanks.

 

[Duane Hookom]

Can you/have you created a totals query that displays

AgeGroup NumOf
======== ====
Ages 25 to 34 10
Ages 35 to 44 3
Ages 45 to 54 3
Ages 54 to 65 6
Ages 66+ 3

If you need this by RegTerm, then add this as another Group By column.

Then create a new report with this as the record source. You can get the
total NumOf in the Report/Group Header/Footer.

Then add the subreport to the appropriate section of the main report.