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
|
|||
|
|||
Many-to-Many Resolver/Junction tables
Hi,
I have been reading a book that talks about using a many- to-many resolver or junction table but I just don't get it. I have two tables: tblProjects and tblEmployees. A Project can have many Employees and Employees can work on many projects. Do I need one of these tables for this scenario? I tried it out in our database but when I do a query (just testing this concept out), it doesn't work. If I remove the "resolver" table, it does. Can anyone explain (simply!) this concept to me? Thanks in advance, Karen |
#2
|
|||
|
|||
Many-to-Many Resolver/Junction tables
Yes, it sounds like that would be a good example for that kind of situation.
Basically, you would have a table -- let's call it tblProjectEmployees -- and it would have 2 fields -- the key field from tblProjects and the key field from tblEmployees. You will need to create a form that will update the junction table with the correct values from those 2 tables when you do the data entry. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Karen" wrote in message ... Hi, I have been reading a book that talks about using a many- to-many resolver or junction table but I just don't get it. I have two tables: tblProjects and tblEmployees. A Project can have many Employees and Employees can work on many projects. Do I need one of these tables for this scenario? I tried it out in our database but when I do a query (just testing this concept out), it doesn't work. If I remove the "resolver" table, it does. Can anyone explain (simply!) this concept to me? Thanks in advance, Karen |
#3
|
|||
|
|||
Many-to-Many Resolver/Junction tables
"Karen" wrote in
: I have two tables: tblProjects and tblEmployees. A Project can have many Employees and Employees can work on many projects. .... then you need another table called WorksOn to record each _fact_ of someone working on a project: Employee Project ======== ======= Eric Port Wing Eric Fuel Tanks Eric Fuel Pump Fred Fuel Pump Fred Carburation Fred Electronic Throttle Gavin Carburation Gavin Air Speed Sensors Gavin Visual Displays and so on... Often there may be other bits of information applicable to the WorksOn entity, like DateStarted, PayRate, IsTeamLeader, SessionsPerWeek, and so on. Hope that makes sense Tim F |
#4
|
|||
|
|||
Many-to-Many Resolver/Junction tables
In real-life situations, we have M-to-M relationships as you described (an
Employee can work on a number of Projects and a Project can be worked on by a number of Employees). However, the Relational Database Structure doesn't allow us to establish the M-to-M relationship between 2 Tables, only 1-to-M (most common) and 1-to-1 (rare). In Database Structure, to establish / model the M-to-M relationship we need to break the M-to-M to TWO 1-to-M relationships and to do this, we need to use the 3rd (Link/Resolver) Table. The Link/Resolver Table has at least 2 Fields which are the ForeignKey, e.g. frg_EmployeeID and frg_ProjectID from your other 2 Tables. This allows you to establish two 1-to-M relationship (tblEmployee to tblResolver and tblProject to tblResolver). This allow you to link a particular Employee to many Projects since you can have a number of Records in tblResolver with the same value for frg_EmployeeID. The same applies to Projects. This way, we can model the real-life M-to-M relationship using 2 separate 1-to-M relationships. Regarding your Query, I can't advise since it depends on what you try to get out of your Query. Perhaps, you should post (in descriptive words) of what you want the Query to do. However, the Resolver Table is THE correct set-up to model the M-to-M relationship in your database. -- HTH Van T. Dinh MVP (Access) "Karen" wrote in message ... Hi, I have been reading a book that talks about using a many- to-many resolver or junction table but I just don't get it. I have two tables: tblProjects and tblEmployees. A Project can have many Employees and Employees can work on many projects. Do I need one of these tables for this scenario? I tried it out in our database but when I do a query (just testing this concept out), it doesn't work. If I remove the "resolver" table, it does. Can anyone explain (simply!) this concept to me? Thanks in advance, Karen |
#5
|
|||
|
|||
Many-to-Many Resolver/Junction tables
Thanks for the replies everyone! At least I know I'm on
the right track. Lynn, I don't understand what you mean by creating a form that will update with the correct values from those 2 tables. Could you explain? Thanks again, Karen -----Original Message----- Yes, it sounds like that would be a good example for that kind of situation. Basically, you would have a table -- let's call it tblProjectEmployees -- and it would have 2 fields -- the key field from tblProjects and the key field from tblEmployees. You will need to create a form that will update the junction table with the correct values from those 2 tables when you do the data entry. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Karen" wrote in message ... Hi, I have been reading a book that talks about using a many- to-many resolver or junction table but I just don't get it. I have two tables: tblProjects and tblEmployees. A Project can have many Employees and Employees can work on many projects. Do I need one of these tables for this scenario? I tried it out in our database but when I do a query (just testing this concept out), it doesn't work. If I remove the "resolver" table, it does. Can anyone explain (simply!) this concept to me? Thanks in advance, Karen . |
#6
|
|||
|
|||
Many-to-Many Resolver/Junction tables
Karen,
Well, put very simply, the junction table will not be automagically populated with the right values. You need to create a data entry form that has the junction table fields as part of it's record source. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Karen" wrote in message ... Thanks for the replies everyone! At least I know I'm on the right track. Lynn, I don't understand what you mean by creating a form that will update with the correct values from those 2 tables. Could you explain? Thanks again, Karen -----Original Message----- Yes, it sounds like that would be a good example for that kind of situation. Basically, you would have a table -- let's call it tblProjectEmployees -- and it would have 2 fields -- the key field from tblProjects and the key field from tblEmployees. You will need to create a form that will update the junction table with the correct values from those 2 tables when you do the data entry. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Karen" wrote in message ... Hi, I have been reading a book that talks about using a many- to-many resolver or junction table but I just don't get it. I have two tables: tblProjects and tblEmployees. A Project can have many Employees and Employees can work on many projects. Do I need one of these tables for this scenario? I tried it out in our database but when I do a query (just testing this concept out), it doesn't work. If I remove the "resolver" table, it does. Can anyone explain (simply!) this concept to me? Thanks in advance, Karen . |
Thread Tools | |
Display Modes | |
|
|