ms access relationships junction table

[trexsteve]

Hi i am new to ms access databases, really struggling with my tables relationships


my database has more than one many-to-many relationships, for example one instructor can have many classes, one class can have different instructors, one client could have different instuctors, one instructor could have many clients and all at different times, on different days


eg: 18/06/2013 Time: 8:00, Instructor: Steven, Class :Pilates, Clients: Bob,
Andrew,
Jess,
Alex


Time: 9:30, Instructor: Thomas, Class :Yoga, Clients: Alexis,
Kerry,
Alex,
Bob

22/06/2013 Time: 10:00, Instructor: Greg, Class :Reformer, Clients: Bob,
Harry,
Jess,
Alex


Time: 16:30, Instructor: Fred, Class :Yoga, Clients: Alexis,
Kerry,
Alex,
Alexandra
(Each day can have many of these at any times)


really struggling on this one, its probably really simple though any help on relationship diagram would be greatly appreciated

i know that i have to use one or more junction tables not sure how to correctly do that, coz i wanted to form with subforms to display the above examples and one just for instructors, and build queries to display one for each for the entire month and weekly

 

[HiTechCoach]

Curious, Is this a class project/homework?


I like to think of a junction table as a way to define a relationship between two records in the same table or two different table.

Example of Same table: People. Create an addition table, People Relations, that links to records together. Like Father, brother, etc.

Example of Two tables: People and Organizations. Create an addition table, Organization Relations, that link to a peple rcord to a Organization record. Like Employee, Owner, etc.

... for example one instructor can have many classes, one class can have different instructors ...

The basic idea is:
You have two objects, classes and people.. One relationship (junction table) between them the objects, instructor.

Tables:
1) Classes
2) People
3) Instructors - This is your junction table between Classes and people.

Example Instructors table design

-InstuctorID (Primary Key - auto number)
-ClassID (foreign key - long - link to Classes table Primary Key)
-PeopleID (foreign key - long - link to People table Primary Key)
-InstructorTypeID (foreign key to lookup table.)