A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old February 16th, 2007, 07:47 PM posted to microsoft.public.access.queries
Marc Eckhert
external usenet poster
 
Posts: 2
Default Merging related records when there are multiple one-to-one relationships between two tables

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

  #2  
Old February 16th, 2007, 08:09 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Merging related records when there are multiple one-to-one relationships between two tables

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" wrote in message
ups.com...
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



  #3  
Old February 17th, 2007, 12:36 AM posted to microsoft.public.access.queries
Marc Eckhert
external usenet poster
 
Posts: 2
Default Merging related records when there are multiple one-to-one relationships between two tables

On Feb 16, 11:09 am, "Jeff Boyce" wrote:
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" wrote in message

ups.com...

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, 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

  #4  
Old February 20th, 2007, 06:08 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Merging related records when there are multiple one-to-one relationships between two tables

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.