Access Many to Many relationship

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" wrote:

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

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Mon, 28 Dec 2009 19:27:01 -0800, MJ wrote:

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

John W. Vinson [MVP]

"David W. Fenton" wrote in message
36.100...
=?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.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

On Tue, 29 Dec 2009 15:32:24 -0500, "Douglas J. Steele"
wrote:

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

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

"Armen Stein" wrote:

On Tue, 29 Dec 2009 15:32:24 -0500, "Douglas J. Steele"
wrote:

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

.

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



"MJ" wrote in message
...
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

"Armen Stein" wrote:

On Tue, 29 Dec 2009 15:32:24 -0500, "Douglas J. Steele"
wrote:

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

.

"Douglas J. Steele" wrote in
:

"David W. Fenton" wrote in message
36.100...
=?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.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Tue, 29 Dec 2009 14:05:01 -0800, MJ wrote:

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

John W. Vinson [MVP]

Armen Stein wrote in
:

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/