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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|