Join has reinvented itself

[BruceM]

I have an Employee table that is linked to three instances of an Inspection
table.

tblEmployee
EmployeeID (PK - Integer)
LastName
etc.

tblInspection contains records of individual inspections.
tblInspection
InspID (PK)
InspDate
InspectedBy (FK)
RepairedBy (FK)
ApprovedBy (FK)

There is more to the database, but I think this is all that matters for the
question at hand.

I used a linked table (in a copied database, not the live one) for the
Employee table. In the Relationships window I dragged EmployeeID on top of
InspectedBy, clicked Create, and all was well. When I did the same with
RepairedBy I was told by the interface that a relationship exists, and was
given an opportunity either to edit the existing relationship or to create a
new relationship (Cancel was in there too). When I clicked No (to create a
new relationship) a second instance of tblInspection (tblInspection1) was
created. Same thing with ApprovedBy, creating tblInspection2.

I decided to use a copied table rather than a linked one for development, so
I deleted the relationships, removed the linked tblEmployee, and imported
tblEmployee instead. I re-created the relationships, using tblInspection1
and tblInspection2 as before. All seemed to be well, but when I closed,
then re-opened the Relationships window I saw that there were no
relationship lines from tblEmployee to tblInspection1 and tblInspection2,
but rather there was now tblEmployee1 and tblEmployee2, each linked to
tblInspection.

EmployeeID is an integer field. InspectedBy, RepairedBy, and ApprovedBy
were Long Integer. I discovered this error while trying to figure out what
was going on, so I deleted the relationships, changed the data type to
Integer to match EmployeeID, and re-created the relationships, but again
there were two extra instances of tblEmployee.

I went back to a linked table, but the result was the same: two extra
instances of tblEmployee.

When I originally created the relationships there was no data in
tblInpsection, so maybe that was why I was able to create incorrect
relationships, if that is what happened. The one thing I didn't try was
deleting all of the test data before creating the relationships, because
even if that gave me the original result it probably would not be the
correct thing to do.

One Employee could be the InspectedBy person for many inspections. Same for
RepairedBy and ApprovedBy. There is a one-to-many between EmployeeID and
these fields, although with a linked table I can't enforce referential
integrity, so I didn't use referential integrity for the copied table either
(I will be using a linked table when the database is in use).

I am unfamiliar with having several instances of a table as described here,
so I wonder what is the correct way to go about this. Are there supposed to
be several instances of the table on the One side of the relationship, or of
the table on the Many side? If the former, are there things I need to keep
in mind? If the latter, what am I doing wrong?

 

[George Nicholson]

I have an Employee table that is linked to three instances of an Inspection

table.

Sounds like you have that backwards.

tblInspection includes 3 FK links to tblEmployees, so it seems to me you
will have one Inspection table and 3 instances of the Employees table.

The InspectedBy, RepairedBy, ApprovedBy fields in a single Inspection record
refer to 3 different employees (counting duplicates), so 3 separate
tblEmployee instances are required. (Each tblEmployee instance can only
return ONE employee.)

 

[BruceM]

OK, that makes sense. As I said, I created the original join before there
were any records, and the interface set it up with 2 extra instances of
tblInspection. I assumed that was correct without giving it a lot of
thought, so when it behaved differently I was puzzled, but as I said it came
together in my head when you pointed out that there are three separate
employees (allowing for duplicates) and therefore there must be three
separate instances of tblEmployee.
Thanks.