Query of 4 tables (control source) for a report - duplicates

[L. Petchell]

Hello,

I wouldn't say that I'm new to Access, but would say that I've got a lot to
learn and get very frustrated when I can't get it to do what I think it can
do. And I know it can do alot!

My goal is to merge to a Word document (our resource directory) or export a
report in RTF format that I can pull into Word. Here is my frustration:

I have put together a, sort of, co-op preschool school membership database.
Tracking 59 schools, their teachers, and their board members and class
schedules (not the students just class dates and times). I've set-up these
tables:

CoopSchools (contains general school info; school id, phone #s, addresses,
websites, date joined, etc.) >>59 school<<

CoopTeachers (contains teacher info; link to school id, phone #s, addresses,
email, etc.) >>1 or more teachers per school<<

CoopBoardMembers (contains board member info; link to school id, position,
phone #s, addresses, email, etc.) >>more than 1 board member per school<<

CoopSchoolsClassesTimesTuition (contains school schedule info; link to
school id, age range, class time, tuition) >>1 or more class schedule per
school<<

Also I have some smaller tables that the above use as lookups for certain
fields: SchoolLocations, SchoolTypes (in area/out of area), BoardPositions.

I need to create a report that will group by school id, all the various
information. All the CoopSchools info, CoopSchoolsClassesTimesTuition info,
and the phone #'s, email & addresses for CoopTeachers and CoopBoardMembers
(board member positions too).

I've created a query to pull all the fields from the above tables that I
need. I've created an Access report, grouping on the school id, then sorting
alpha by the school names. I thought this would group all the school info,
teachers, members and schedules together within the report layout. But I get
numerous duplicates.

I searched the help sections everywhere. I know I can do this. I just need a
good jump start from a smarter person than I!

PS. I thought it would just be easier to export to RTF, than to merge into
Word. But if a way is known, please share! Layout would need to be, like, one
box of info per school. Not a line by line directory style.

All help is greatly appreciated and needed soon!

 

[L. Petchell]

I actually think I got it!?? After searching through this user group, I found
out more about sub-reports. I tried it and so far it's worked. I am having
problems with one subreport based on:

CoopBoardMembers (contains board member info; link to school id, position,

phone #s, addresses, email, etc.) >>more than 1 board member per school<<

It won't group on the school id, which is in the table and I created the
sub-report with the group on school id. It's just lumping all the 59 school's
members together.

Any ideas would help. But, must go to bed now!

Thank you so much for all the great post and responses here! Very helpful!

 

[L. Petchell]

Hi again,

Well no one has responed, but hey, I've figured out the answers to all my
questions below - on my own. Now I have a new one, still related to the first.

I have a main report with 3 sub-reports. I would like to have more than one
group of data to print on a page. What am I missing?