If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Join has reinvented itself
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? |
#3
|
|||
|
|||
Join has reinvented itself
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? |
Thread Tools | |
Display Modes | |
|
|