Access Many to Many relationship

[MJ]

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.

 

[Steve]

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
(e-mail address removed)

 

[Allen Browne]

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.

 

[MJ]

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.

 

[John W. Vinson]

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?

 

[MJ]

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]

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.

 

[David W. Fenton]

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]

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.

 

[MJ]

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

 

[MJ]

Hi John
I setup the subform as you suggested. However, if I can't use lookups or
combo boxes, how do I get the data into the field? Do I have to manually
enter the data. This seems wrong as errors will occur.
Many thanks for your help...progressing along.
MJ

 

[David W. Fenton]

I setup the subform as you suggested. However, if I can't use
lookups or combo boxes, how do I get the data into the field? Do I
have to manually enter the data. This seems wrong as errors will
occur. Many thanks for your help...progressing along.

No one said not to use combo boxes *on the form* -- it's only in
TABLE DESIGN that you should never use lookups.

 

[John W. Vinson]

Hi John
I setup the subform as you suggested. However, if I can't use lookups or
combo boxes, how do I get the data into the field? Do I have to manually
enter the data. This seems wrong as errors will occur.

Sorry for being so dogmatic. I absolutely agree with David - you should use
Combo Boxes on your Forms whenever it's appropriate (which will be very
common). It's just the Lookup Wizard putting combo boxes into Tables, where
they do NOT belong, that is objectionable. It is *not* necessary to use the
Lookup Field feature in order to create a combo on a form (though I'll admit
it makes it a little bit easier, though not enough to outweigh the
disadvantages of the table lookup).

 

[Douglas J. Steele]

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.

While I'm a proponent of natural keys, you could use an Autonumber PK as
long as you also had a unique index defined on the composite of the two
foreign keys.

Unfortunately, given the direction Microsoft is insisting we move to
congruence with SharePoint, it's almost going to be mandatory to have
Autonumber PKs on each table...

 

[Armen Stein]

While I'm a proponent of natural keys, you could use an Autonumber PK as
long as you also had a unique index defined on the composite of the two
foreign keys.

I'm not. In our shop we use surrogate Autonumber/Identity keys almost
exclusively. As Doug says, we enforce uniqueness using indexes. All
of our relationships are simple one-field joins, no matter how far
down a hierarchy, which can be very handy in a database of hundreds of
tables. In the case of this many-to-many table, it's hard to know
whether you'll need a child table in the future. If you just use a
surrogate, you won't need to modify the structure in the future - just
add the child table.

Another reason for surrogates is that auto-generating middle tier
objects is simpler with consistent integer surrogate key fields.
Access front-ends don't benefit, but we have some projects that have
(or may soon have) a web application front-end too.

At the end of the day, it's a style preference. Either way will work,
each with pros and cons.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[MJ]

Thanks all for you input. Point taken and noted on the issues with primary
key autonumber for join tables along with composite keys. However, my initial
question was since Allen categorically stated not to use lookup tables how
can the user input data listed in another table eg manually? That can't be
correct due to data input error.
Any suggestions on making the form work better?
MJ

 

[Steve]

For data entry, create a form/subform. Base the main form on TblProject and
base the subform on TblProjectEmployee. Set the LinkMaster and LinkChild
properties to ProjectID. You will be able to enter projects in the main
form, go to previously entered projects in the main form and enter employees
on the project in the subform.

Steve
(e-mail address removed)

 

[David W. Fenton]

While I'm a proponent of natural keys, you could use an Autonumber
PK as long as you also had a unique index defined on the composite
of the two foreign keys.

I don't see the value of a surrogate PK in a join table unless the
join table is itself a parent table in a parent/child relationship.
That's not at all an unheard-of scenario, but most join tables
really only have the two foreign keys.

Unfortunately, given the direction Microsoft is insisting we move
to congruence with SharePoint, it's almost going to be mandatory
to have Autonumber PKs on each table...

I think they're going to have to add composite indexes sooner or
later. It's too important for data integrity.

 

[John W. Vinson]

However, my initial
question was since Allen categorically stated not to use lookup tables how
can the user input data listed in another table eg manually?

Neither Allen nor any of us have ever said "not to use lookup tables".

What we have said is "don't use the Lookup Field datatype in Table design".

Lookup tables are absolutely vital. Every database I've ever developed
contains lookup tables.

The objection is not to "lookup tables" - it's to Microsoft's misguided
decision to include Combo Boxes ("lookup fields") in Tables. Doing so is the
source of great confusion and bad design, and it is *not* necessary in order
to (properly!!!) use Lookups (combo boxes, listboxes) on Forms.

 

[David W. Fenton]

In the case of this many-to-many table, it's hard to know
whether you'll need a child table in the future. If you just use
a surrogate, you won't need to modify the structure in the future
- just add the child table.

I would say child tables of join tables are pretty rare (though not
at all unheard-of).

I would also say that adding a surrogate key that is not the PK is
not that difficult. It might seem that this would be insufficient,
but RI can be enforced on any field with a unique index -- it need
not be the PK (I was actually surprised to see that this was the
case, as I specifically set up a test to be sure that it was doable,
and as I was putting the tables together, thought for sure that RI
had to have a PK on the parent side).

So I'm not convinced by the argument of adding it on the front end
just in case. It will require maintaining another index and an
Autonumber seed, and I don't think the overhead of this (little as
it may be) is worth it just to avoid the remote possibility of
adding a child table later on (which I think is a pretty remote
possibility for the vast majority of join tables), particularly
given how easy it is to add the non-PK surrogate key.

I also think I'd always choose the non-PK surrogate key with
composite 2-column PK over the alternative (making the surrogate key
the PK and keeping the unique index on the 2-column key), because
then the surrogate key is really functioning as a literal surrogate
for the actual PK, and has no purpose other than to link the join
table to its child table(s).