Need help relating two tables that could have multiple relationships

[inovermyhead]

I am creating a database in Access 2003 to track volunteers at a library - programs they helped with, hours worked, etc. I am not a complete Access newbie, but it's definitely not my main job, so I'm finding myself cruising forums and occasionally beating my head against a wall. I really need help, PLEASE!

I have a table for Volunteers, with VolunteerID, FirstName, LastName, other details. I have another table for Programs, with ProgramID, ProgramName, etc.; programs may have from 1 -3 volunteers helping with them, so this table includes fields named Volunteer1, Volunteer2, and Volunteer3.

My difficulty is in relating the tables, as the VolunteerID from the Volunteers table can relate 1-to-many with Volunteer1, Volunteer2, and /or Volunteer3. (Any 1 volunteer may be listed as Volunteer 1, 2, or 3 for any 1 program, and any volunteer may serve at many programs.)

I've tried just leaving them unrelated, which I can do for most purposes, but that seriously throws a kink into some of my queries that I would like to show data from both tables - I keep getting multiples of the same data. Very confusing to me.

I need to get this up and running pretty quickly. I'm running out of time before we need to start using it! Any help would be greatly appreciated!

 

[Jeanette Cunningham]

Instead of creating fields called Volunteer1, Volunteer2, Volunteer3, create a separate table that matches the program ID with the volunteer ID.
You can call this table tblProgramVolunteers.
This table can have an autonumber primary key called ProgramVolunteerID.
The 2 important fields are ProgramID and VolunteerID.

The program table is related one-to-many to this table on the ProgramID field.
The volunteer table is related one-to-many to this table as well.

For every volunteer, a record of the volunteer and the program they volunteered in, gets added to tblProgramVolunteer.

Have a look here
http://www.rogersaccesslibrary.com/forum/topic342.html
to see samples showing how to set up the forms to enter data for a setup with 2 main tables and a table that joins that those 2 tables.