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
|
|||
|
|||
Linking two tables - a bit different
I understand how I need to link two tables in general. However, I have a bit
different situation. I have two tables, an Employee table and an EmployeeLocation table (1 to Many). In the Employee table I have an EmployeeID (primary key) and a LocationID field. The employee may, over time, change locations. The EmployeeLocation table has an EmployeeID, LocationID, and StartDate for the employee at that location. If the employee changes location I want to record the start date at this new location in the EmployeeLocation table. If I try to do a join of these two tables on these two fields the fields in the EmployeeLocation table are read-only. If I create a unique index on the two fields in the employee table then I'm able to read-write. However, if I change the employee location then it also changes the location in the EmployeeLocation table. Instead, I want a new record created since this is a new location in the EmployeeLocation table, or if the record already exists then display that record information. I'm trying to do via a query. So far, either I cannot update the EmployeeLocation Start date, or the location changes in the EmployeeLocation table when I change location in the Employee table. I think I probably could do this via a lot of coding of the StartDate field, but I was hoping there is a solution just using a query. Has anyone encountered this type of situation before? Thanks, Leif |
Thread Tools | |
Display Modes | |
|
|