Merging related records when there are multiple one-to-one relationships between two tables

[Marc Eckhert]

I am having trouble finding the right query to merge related records
into a single query when there are multiple one-to-one relationships
between two tables.

I have two tables, Members and Meetings.

Members:

[member_id] autonumber,
[first_name] text,
[surname] text


Meetings:

[meeting_id] autonumber
[meeting_date] date,
[presiding_president] number,
[speaker_1] number,
[evaluator_1] number,
[speaker_2] number,
[evaluator_2] number

Each of the number type fields in the Meetings table refers to a
record in the Members table. So, presiding_president may refer to
member_id 1, and speaker_1 may refer to member_id 2, etc.

I want to build a query that will retrieve a Meeting record with the
related Member information (first_name & " " surname) for each of
these columns. I know how to build a query that retrieves this
information for one of the columns, but not for all of the columns. I
want my query result set to look like:

presiding_president: John Smith
speaker_1: Bob Marley
evaluator_1: Simon Says
etc.

Can anyone explain the right approach and also provide a sample query
of how to do this?

Regards,

Marc

 

[Jeff Boyce]

Marc

Your table design is well-suited ... to be a spreadsheet! You have what is
called 'repeating fields' (i.e., person-in-role1, person-in-role2, ...).

In your world, it sounds like you have a many-to-many relationship. One
member could serve in many roles (across multiple meetings), and one role
could be found in many meetings (and held by many different members).

A data/table design that will allow you better use of Access' features and
functions will reflect the many-to-many relationships.

Without a bit more information, the following design is just a guess...

tblPerson
PersonID
FirstName
LastName
DOB
... (any other person-specific info)

tblMeeting
MeetingID
MeetingDate
MeetingLocation
... (any other ...)

tlkpRole
RoleID
Role (this would be your "Speaker", "Evaluator", and any other roles
used)

trelMeetingParticipation
MeetingParticipationID
MeetingID (which meeting, from tblMeeting)
PersonID (which person, from tblPerson)
RoleID (which role, ...)
SequenceNumber (since it appears from your description that the order
matters)
... (any other facts about this member's participation in this role in
this meeting)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marc Eckhert]

Marc

Your table design is well-suited ... to be a spreadsheet! You have what is
called 'repeating fields' (i.e., person-in-role1, person-in-role2, ...).

In your world, it sounds like you have a many-to-many relationship. One
member could serve in many roles (across multiple meetings), and one role
could be found in many meetings (and held by many different members).

A data/table design that will allow you better use of Access' features and
functions will reflect the many-to-many relationships.

Without a bit more information, the following design is just a guess...

tblPerson
PersonID
FirstName
LastName
DOB
... (any other person-specific info)

tblMeeting
MeetingID
MeetingDate
MeetingLocation
... (any other ...)

tlkpRole
RoleID
Role (this would be your "Speaker", "Evaluator", and any other roles
used)

trelMeetingParticipation
MeetingParticipationID
MeetingID (which meeting, from tblMeeting)
PersonID (which person, from tblPerson)
RoleID (which role, ...)
SequenceNumber (since it appears from your description that the order
matters)
... (any other facts about this member's participation in this role in
this meeting)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am having trouble finding the right query to merge related records
into a single query when there are multiple one-to-one relationships
between two tables.

I have two tables, Members and Meetings.

Members:

[member_id] autonumber,
[first_name] text,
[surname] text

Meetings:

[meeting_id] autonumber
[meeting_date] date,
[presiding_president] number,
[speaker_1] number,
[evaluator_1] number,
[speaker_2] number,
[evaluator_2] number

Each of the number type fields in the Meetings table refers to a
record in the Members table. So, presiding_president may refer to
member_id 1, and speaker_1 may refer to member_id 2, etc.

I want to build a query that will retrieve a Meeting record with the
related Member information (first_name & " " surname) for each of
these columns. I know how to build a query that retrieves this
information for one of the columns, but not for all of the columns. I
want my query result set to look like:

presiding_president: John Smith
speaker_1: Bob Marley
evaluator_1: Simon Says
etc.

Can anyone explain the right approach and also provide a sample query
of how to do this?

Marc

Jeff, thank you for your help.

I have revised my data model based on your suggestion, but now I have
another issue, this time with forms. What I need is to have an
Attendee record for each type of Role for each Meeting. I want my
Meeting form to have a ComboBox for each Role so that I can select a
user for a Role (say, John Smith for the president Role). I can
create a Form for Meetings and a SubForm for Attendees, but I am not
sure how, to create an Attendee record for each type of Role. I don't
expect the Roles to change frequently, so I could hard code them to
the main form, but that just doesn't seem right. Here is an updated
data model:


tblMeetings
MeetingID
MeetingLocation
(..other..)

tblUsers
UserID
FirstName
Surname
(..other..)

tblRoles
RoleID
RoleTitle

tblAttendees
MeetingID
UserID
RoleID


I definitely appreciate the help.

Marc

 

[Jeff Boyce]

Marc

See comments in-line...

Jeff, thank you for your help.

I have revised my data model based on your suggestion, but now I have
another issue, this time with forms.

To get more "eyes" on your issue, try posting it as a new issue in the forms
newsgroup.

What I need is to have an
Attendee record for each type of Role for each Meeting.

Are you saying that every meeting will have someone serving in every role
(that's a lot of "every's"!)? If so, it may not be necessary to have a
separate table for this, EXCEPT, if you will ever change the number/name of
Roles. If there's any chance of a change, keep the separate tables, so you
can simply add a Role to the tlkpRole.

I want my
Meeting form to have a ComboBox for each Role so that I can select a
user for a Role (say, John Smith for the president Role). I can
create a Form for Meetings and a SubForm for Attendees, but I am not
sure how, to create an Attendee record for each type of Role.

I may be missing something here. If a meeting can have John as President,
Jane as Vice President, and Jim as Sergeant-at-Arms, you would need a table
that held:
trelMeetingRoles
MeetingRoleID
MeetingID
PersonID
RoleID
and one record per Person/Role/Meeting.

To do this in a form, the main form has the meeting info, and the subform
has the person/role info. The subform would be based on the
trelMeetingRoles above, and would synchronize with the main form using the
MeetingID field.

I don't
expect the Roles to change frequently, so I could hard code them to
the main form, but that just doesn't seem right. Here is an updated
data model:


tblMeetings
MeetingID
MeetingLocation
(..other..)

tblUsers
UserID
FirstName
Surname
(..other..)

tblRoles
RoleID
RoleTitle

tblAttendees
MeetingID
UserID
RoleID


I definitely appreciate the help.

Marc

Regards

Jeff Boyce
Microsoft Office/Access MVP