Access Many to Many relationship
in [Tables]
|
Prev: Relationship Help
Next: Budget Table
From: MJ on 27 Dec 2009 08:20 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.
From: Steve on 27 Dec 2009 11:04 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 santus(a)penn.com "MJ" <MJ(a)discussions.microsoft.com> wrote in message news:C86F31CC-9DAD-4300-A20E-6FB4D37513AD(a)microsoft.com... > 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. >
From: Allen Browne on 27 Dec 2009 20:58 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" <MJ(a)discussions.microsoft.com> wrote in message news:C86F31CC-9DAD-4300-A20E-6FB4D37513AD(a)microsoft.com... > 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. >
From: MJ on 28 Dec 2009 00:11 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" <MJ(a)discussions.microsoft.com> wrote in message > news:C86F31CC-9DAD-4300-A20E-6FB4D37513AD(a)microsoft.com... > > 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. > > > . >
From: John W. Vinson on 28 Dec 2009 01:16
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] |