Many to Many with AutoNumber PK

[Huber57]

Hope someone can help.

I have two tables:
1) Employees (EmployeeNumber is PK and is set at AutoNumber) - This table
has all the info about each employee

2) Function - This table only has one field ("legal", "accounting", "admin",
etc). This field serves as the PK.

I need to create a Many-to-Many relationship as Employees can serve in more
than one function and one function can have many employees.

When I create a 'link table' with the following fields:
(PK) Number(AutoNumber)
(FK) Function (Text)
(FK) EmployeeNumber(AutoNumber)

It won't let me have two autonumbers in the same table. How do I get around
this?

Thanks for your help in advance!

 

[Pat Hartman]

The EmployeeNumber should not be defined as an autonumber in this table. It
should be defined as a long integer. Autonumbers for employees are only
generated when new employees are added to the employee table. Foreign keys
that point to autonumbers are ALWAYS defined as long integers.

I tend not to use autonumbers as the pk of a junction table unless the
junction table itself has child tables. I use a compound key comprised of
the two FKs. However, it is not wrong to do so but if you do use an
autonumber PK, you should also define a compound unique index to enforce the
business rule that the combination of function and employeeNumber can only
occur ONCE in the junction table.

 

[Huber57]

Pat,

'Many' thanks (sorry, couldn't resist).

One more question. I want to use a list box with a simple multi select.
When I write the query to bind to the form, which table do I use for the
query? Function or the FunctionEmployee junction table?

I really appreciate the help!

 

[John W. Vinson]

Hope someone can help.

I have two tables:
1) Employees (EmployeeNumber is PK and is set at AutoNumber) - This table
has all the info about each employee

2) Function - This table only has one field ("legal", "accounting", "admin",
etc). This field serves as the PK.

I need to create a Many-to-Many relationship as Employees can serve in more
than one function and one function can have many employees.

When I create a 'link table' with the following fields:
(PK) Number(AutoNumber)
(FK) Function (Text)
(FK) EmployeeNumber(AutoNumber)

It won't let me have two autonumbers in the same table. How do I get around
this?

Thanks for your help in advance!

Make the EmployeeNumber a Long Integer instead of an autonumber. An autonumber
won't work here at all; you can't control its value, so you would have no way
to link the record to a particular employee! Just use a Form based on
Employees with a subform based on this table, using EmployeeID as the
master/child link field.

Unless this table will be itself linked to further tables, I'd suggest
removing (or at the very least renaming, since Number is a reserved word) your
autonumber PK; instead, ctrl-click the Function and EmployeeNumber fields and
click the Key icon to make the two fields a joint primary key.


John W. Vinson [MVP]

 

[Pat Hartman]

I'm not sure I understand the question. Multi-select list boxes are NOT
bound. To bind them would violate first normal form which prohibits
repeating groups. If you use a multi-select list box and you want to store
the selections, you have a lot of coding ahead of yourself. You will need
to loop through the selected collection to determine which items were
selected. You will need to add or delete table rows (assuming that you have
properly normalized the table where the data will be stored) to reflect the
current selections. Also, since the list box cannot be bound, you will need
to mark the current selections with a code loop that reads the many-side
table and sets the appropriate row in the listbox's collection to selected.

My suggestion is to replace the listbox with a subform. Let the user choose
the selections by adding rows to the subform and selecting the value from a
combo box.

Personally, I only use the multi-select features of the list box for
selection criteria. Therefore, I never have to deal with making the list
reflect the entries in a many-side table.

Access 2007 has a new data type which appears to allow you to select and
store many items from a list. In fact, behind the scenes, Access does
create the necessary tables and relationships to conform to best practices
database design. be aware though that if you make use of this "feature" it
is a double-edged sword. What you gain in up front productivity, you loose
in scalability (you can't upsize this data to SQL server because the tables
are hidden). Plus you need to learn new SQL syntax to make use of the data
type and last I heard there were queries that simply could not be created
with the new syntax. Someone closer to this please pop in and comment.