Access Many to Many relationship
in [Tables]
|
Prev: Relationship Help
Next: Budget Table
From: MJ on 28 Dec 2009 02:29 Hi John, thanks for the prompt response. I have taken my knowledge from Northwind database. The join table does not have an autonumber, just two primary keys from the two related tables. I did not know you had to enter data into the join table. I thought it was automatically entered when you entered data into the emloyee and projet table. Do I need to create a lookup to employee and project from the join table? Cheers MJ "John W. Vinson" wrote: > On Sun, 27 Dec 2009 21:11:01 -0800, MJ <MJ(a)discussions.microsoft.com> wrote: > > >Thanks Allen > >I did this and here is what I setup. However, I still can't get any queries! > > > >tblProject > >ProjectID - Autonumber > >ProjectTitle - Text > >ProjectCode - Text > > > >tblEmployee > >EmployeeID - Autonumber > >Fname - text > >Lname - text > >fkProjectID - Number and created a lookup back to tblProject > > > >tblEmployee_Project > >Employee_ProjectID - Autonumber > >fkEmployeeID - Number > >fkProjectID - Number > > > > Your tables appear to be correct. What do you mean, though, that you "can't > get any queries"? If tblEmployee_Project is empty (as it of course will be > when it's first created) then you should be able to get a Query joining > tblProject to tblEmployee_Project on ProjectID linked to fkProjectID; > tblEmployee to tblEmployee_Project on EmployeeID; or all three tables. But of > course since there are no records to join, you'll get no records in the > result! What (if anything) have you done to populate tblEmployee_Project? If > you can't get any of these queries created... why not? What happens when you > try? > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 28 Dec 2009 12:28 On Sun, 27 Dec 2009 23:29:01 -0800, MJ <MJ(a)discussions.microsoft.com> wrote: >Hi John, thanks for the prompt response. I have taken my knowledge from >Northwind database. The join table does not have an autonumber, just two >primary keys from the two related tables. I did not know you had to enter >data into the join table. I thought it was automatically entered when you >entered data into the emloyee and projet table. That would mean that every employee is automatically assigned to every project... and you'll have a major fight with the employee union! <g> No, the whole PURPOSE of the junction table is to allow you to - selectively! - assign each employee to zero, one, or more projects, and vice versa. The normal way to do so is with a Form based on Employees with a Subform based on EmployeeProject, using the EmployeeID as the subform's master/child link. Or you can start with a form based on the Projects table instead. The relevant Northwind example is the Orders form. The main form is based on Orders; the subform on OrderDetails, which is the junction table between Orders and Products. Each Order can include many products, each Product can be in many Orders; each such pairing is represented by a record in OrderDetails. > Do I need to create a lookup >to employee and project from the join table? ABSOLUTELY NOT. See http://www.mvps.org/access/lookupfields.htm for a critique of this misleading, misdesigned, obnoxious feature. -- John W. Vinson [MVP]
From: David W. Fenton on 28 Dec 2009 16:26 "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in news:#OemKF2hKHA.5608(a)TK2MSFTNGP05.phx.gbl: > In your 3rd table, why have you used the combination of EmployeeID > + ProjectID as primary key? Are you trying to insist that no > employee can ever be involved in more than one project? Since the > primary key must be unique, that will be the effect. > > For a many-to-many relation, you would normally have fields like > this in the 3rd table: > EmployeeProjectID AutoNumber primary key > EmployeeID Number relates to Employee > table ProjectID Number relates to > Project table JoinDate Date/Time date > this employee joined this > proj. > LeaveDate Date/Time date this emp. left > this proj. I don't understand your recommendation, Allen. A join table needs to have the composite key on the foreign keys being joined, as the original poster described. That is, unique composite key on EmployeeID + ProjectID. That doesn't restrict the employee to a single project, it just limits the join table to one record per employee project. That makes sense, as what value would there be to have the same project joined to the employee twice? Now, if an employee can join and leave a project multiple times, then it seems to me that belongs in a different table. If, on the other hand, the employee joins and leaves the project only once, then those attributes are part of the employee/project record. The Autonumber surrogate key you've added serves no useful purpose when the business rule is to allow only one instance of each project per employee. but if you are linking a table of project dates to this join record, then the surrogate key becomes very useful. In that case, the surrogate Autonumber would be the PK, with a unique composite key on the EmployeeID + ProjectID. Do you disagree? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 28 Dec 2009 16:29 =?Utf-8?B?TUo=?= <MJ(a)discussions.microsoft.com> wrote in news:3468ED0A-D353-49C2-959B-D4B5B7A7CB4E(a)microsoft.com: > tblEmployee_Project > Employee_ProjectID - Autonumber > fkEmployeeID - Number > fkProjectID - Number I disagree with Allen's recommendation to add an Autonumber field here. The proper PK is the composite of the two foreign keys. The only scenario in which an additional Autonumber would be useful is if this join table is involved in a relationship with a child table, e.g., if you recorded in a separate table the dates in which someone was assigned to a project. Absent a relationship to another table, there is no utility at all to the Autonumber field in your join table. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: MJ on 28 Dec 2009 19:41
Hi John This now all makes sense. Everything now is falling into place. I can now understand and create a many-to-many simple database. this has been great. Thanks again. MJ "John W. Vinson" wrote: > On Sun, 27 Dec 2009 23:29:01 -0800, MJ <MJ(a)discussions.microsoft.com> wrote: > > >Hi John, thanks for the prompt response. I have taken my knowledge from > >Northwind database. The join table does not have an autonumber, just two > >primary keys from the two related tables. I did not know you had to enter > >data into the join table. I thought it was automatically entered when you > >entered data into the emloyee and projet table. > > That would mean that every employee is automatically assigned to every > project... and you'll have a major fight with the employee union! <g> > > No, the whole PURPOSE of the junction table is to allow you to - selectively! > - assign each employee to zero, one, or more projects, and vice versa. The > normal way to do so is with a Form based on Employees with a Subform based on > EmployeeProject, using the EmployeeID as the subform's master/child link. Or > you can start with a form based on the Projects table instead. > > The relevant Northwind example is the Orders form. The main form is based on > Orders; the subform on OrderDetails, which is the junction table between > Orders and Products. Each Order can include many products, each Product can be > in many Orders; each such pairing is represented by a record in OrderDetails. > > > Do I need to create a lookup > >to employee and project from the join table? > > ABSOLUTELY NOT. See http://www.mvps.org/access/lookupfields.htm for a critique > of this misleading, misdesigned, obnoxious feature. > > -- > > John W. Vinson [MVP] > . > |