Relating 3 tables

[goslincm]

I am having trouble setting up my relationship between three tables. I have a Meetings table with a MeetingID pk, another table for Task being assigned during the meeting with a TaskID as the pk, finally a Attendee table for listing those in attendance of the meetings with an AttendeeID pk.

For any meeting there can be several to only one person in attendance. While at that meeting several to one task may be assigned out.

Appreciate any help with this.

 

[Remou]

How about a table:
MeetingAttendeeTask
MeetingID
AttendeeID
TaskID

I would think than at any meeting an attendee can only be assigned a particular task once.

 

[Golom]

If you are going to relate Meetings, Attendees and Tasks then your tables will need to be structured as
[tt]
tblMeeting
MeetingID (PK)
Other Meeting Fields

tblAttendees
AttendeeID (PK)
MeetingID (FK to MeetingID in tblMeeting)
Other Attendee Fields

tblTask
TaskID (PK)
AttendeeID (PK - FK To AttendeeID in tblAttendees)
MeetingID (PK - FK To MeetingID in tblMeetings)
Other Task Fields
[/tt]
This assumes that a task can be assigned to only one attendee at a meeting AND that unassigned tasks are not allowed AND tasks are assigned only in meetings. If those assumptions are untrue then remove ate appropriate fields (AttendeeID and/or MeetingID) from the Primary Key in tblTasks.

 

[goslincm]

Golom and Remou...thanks for the help with the three tables. So my next step is to go into the relationship area and relate pk to fk? I am just being very thick on getting relationships clear and knowing when I'm working with a one-to-many vs. many-to-many, etc.

For a beginner I can't say enough how helpful this forum is!

 

[Remou]

Yes, that is the way to go.

 

[goslincm]

Remou, so I am taking all three Pk fields from TblTask and relating each to TblMeeting, MeetingID?

 

[Remou]

With Golom's set up you would have:
[tt]tblMeeting MeetingID -> tblAttendees MeetingID
-> tblTask MeetingID

tblAttendees AttendeeID -> tblTask AttendeeID[/tt]

In tbltask you would select TaskID, MeetingID and AttendeeID and mark them as the primary key.

With my suggestion, the idea is similar, except there is an extra table:

[tt]tblMeeting MeetingID -> MeetingAttendeeTask MeetingID
tblAttendees AttendeeID -> MeetingAttendeeTask AttendeeID
tblTask TakID -> MeetingAttendeeTask TaskID[/tt]

Once again, take all three IDs in MeetingAttendeeTask and mark them as the primaty key.

 

[goslincm]

With Golom's set up it works good with one exception. I have to manually enter my attendeeid into the task table. Unlike the meetingid being fed into it automatically, the attendeeid is not out there unless I type it in. Shouldn't it show up automatically like the meetingid?

 

[Remou]

Have you set up both AttendeeID and MeetingID as Link Child and Link Master Fields?

 

[goslincm]

Remou-that is what was missing. Thanks for all the help and patience.