Join has reinvented itself

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?

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

--
HTH,
George


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

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.

"George Nicholson" wrote in message
...
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.)

--
HTH,
George


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