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.

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.

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.

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.

.

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]

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]
.

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]

"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/

=?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/

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]
.