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
|
|||
|
|||
Access Many to Many relationship
If someone could please help me get my head around this it would be much
appreciated. I have three tables: tblEmployee with the Primary Key being EmployeeIDID and an autonumber, TLname and Fname, etc. TblProject with the Primary Key being projectid, ProjectName, etc. I have created a third table eg a join table and have called it Employee_Project. I have used the two primary keys as composite primary keys and the data type is Number. However, I can't create any queries from the data. What am I doing wrong? Any help would be much appreciated. |
#2
|
|||
|
|||
Access Many to Many relationship
EmployeeIDID Spelling (???)
Add Employee_ProjectID (autonumber) to your Employee_Project table. Make this field your primary key. Create a query that includes all three tables. Steve "MJ" wrote in message ... If someone could please help me get my head around this it would be much appreciated. I have three tables: tblEmployee with the Primary Key being EmployeeIDID and an autonumber, TLname and Fname, etc. TblProject with the Primary Key being projectid, ProjectName, etc. I have created a third table eg a join table and have called it Employee_Project. I have used the two primary keys as composite primary keys and the data type is Number. However, I can't create any queries from the data. What am I doing wrong? Any help would be much appreciated. |
#3
|
|||
|
|||
Access Many to Many relationship
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "MJ" wrote in message ... If someone could please help me get my head around this it would be much appreciated. I have three tables: tblEmployee with the Primary Key being EmployeeIDID and an autonumber, TLname and Fname, etc. TblProject with the Primary Key being projectid, ProjectName, etc. I have created a third table eg a join table and have called it Employee_Project. I have used the two primary keys as composite primary keys and the data type is Number. However, I can't create any queries from the data. What am I doing wrong? Any help would be much appreciated. |
#4
|
|||
|
|||
Access Many to Many relationship
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 I have a 1-many relationship between tblProject and tblEmployee_Project, i have a 1-many relationship between tblEmployee and tblEmployee_Project. i.e. all relationships going back to Join Table. No relationship directly between employee and project table. "Allen Browne" wrote: 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "MJ" wrote in message ... If someone could please help me get my head around this it would be much appreciated. I have three tables: tblEmployee with the Primary Key being EmployeeIDID and an autonumber, TLname and Fname, etc. TblProject with the Primary Key being projectid, ProjectName, etc. I have created a third table eg a join table and have called it Employee_Project. I have used the two primary keys as composite primary keys and the data type is Number. However, I can't create any queries from the data. What am I doing wrong? Any help would be much appreciated. . |
#5
|
|||
|
|||
Access Many to Many relationship
On Sun, 27 Dec 2009 21:11:01 -0800, MJ 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] |
#6
|
|||
|
|||
Access Many to Many relationship
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 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] . |
#7
|
|||
|
|||
Access Many to Many relationship
On Sun, 27 Dec 2009 23:29:01 -0800, MJ 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] |
#8
|
|||
|
|||
Access Many to Many relationship
"Allen Browne" wrote in
: 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/ |
#9
|
|||
|
|||
Access Many to Many relationship
=?Utf-8?B?TUo=?= wrote in
: 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/ |
#10
|
|||
|
|||
Access Many to Many relationship
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 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] . |
Thread Tools | |
Display Modes | |
|
|