View Single Post
  #2  
Old June 4th, 2010, 10:25 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Linking two tables - a bit different

You don't need a LocationID field in the employee table; you are recording
that data in the EmployeeLocation table. The only relationship you need is
to link EmployeeID in TblEmployee to EmployeeID in TblEmployeeLocation.

When an employee changes location all you need do is add a new record to the
EmployeeLocation table.

For data entry you need a form/subform. Base the main form on TblEmployee
and base the subform on TblEmployeeLocation. Set the LinkMaster and
LinkChild fields to EmployeeID. When you go to the form/subform, an employee
will appear in the main form and the subform will display a list of
locations the employee has worked and the start date for each location.

Steve



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