One-to-Many Report

[ladawnd]

I maintain an Access database of Volunteers for our elementary school. Last
year I kept everything in a flat file to make things quick and simple.

This year I have a database with three tables. Teachers, Students and
Parents. There is a one to many relationship from teachers to students. The
student database has a field called teacher that is linked to the primary key
of the teachers. The form that this is filled out on has a drop down list of
possible teachers.

And there is a One to many relationship between parents and students. The
student table has a field called parent which is linked to the primay key of
the parent in the parent table.

Each parent has certain things they volunteered to do. I need to create a
list for each teacher that shows which parents have volunteered to do what.

Each parent may show up in more than one teacher's list.

I want it to include student and parent information. When I use the wizard
to create a table, the student information is all complete, but the parent
information is all blank.

Any suggestions on how to do this? I'm really to RTF, but I can't find what
to read. Thanks!

 

[Al Camp]

ladawnd,
Drop the Wizard, and create your own tblVolunteers.
Since we don't know your exact table setup, this will be a "generalized"
solution...

tblVolunteers should have a field to designate the ParentID and the
associated TeacherID, and any ancillary information you need about what the
volunteer did. (Date/Hours/Task/etc...)
If a volunteer works for several teachers, each of those individual
actions will need a seperate entry... with it's own associated TeacherID.
A subform based on tblVolunteers on the main Teacher form, linked by
TeacherID, should show only those volunteers who worked for that Teacher.

Also...

The student database has a field called teacher that is linked to the
primary key
of the teachers. The form that this is filled out on has a drop down list
of
possible teachers.

I would suggest entering those students into a Students subform of the
Teacher record. You would select Student from a dropdown of available
students (the reverse of your process). That way you can easily count the
students assigned to a teacher and more readily see the "class" at a glance.
Use the Teacher form as the main vehicle for all your information, with
secondary tables supporting it.