View Single Post
  #1  
Old June 4th, 2010, 09:48 PM posted to microsoft.public.access.tablesdbdesign
Leif
external usenet poster
 
Posts: 82
Default 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