A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Linking two tables - a bit different



 
 
Thread Tools Display Modes
  #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
  #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



  #3  
Old June 5th, 2010, 12:22 AM posted to microsoft.public.access.tablesdbdesign
Leif
external usenet poster
 
Posts: 82
Default Linking two tables - a bit different

Steve,

Thanks for your reply.

I think I do need the location ID in the employee table. It is the
employee's current location. For example say the employee ID is 1 and he is
currently at location 1. Also lets say he has been in two other locations in
the past, location 2 and 3. We have (each row below is a record):

Employee table:
ID: 1 Location: 1

EmployeeLocation Table:
ID: 1 Location: 1 Start Date: 1/1/2010
ID: 1 Location: 2 Start Date: 1/1/2009
ID: 1 Location: 3 Start Date: 1/1/2008

I cannot take the latest date from the EmployeeLocation table and assume
that is his current location, since he could switch back to another location
and that would not change his start date.

So adding a recording to the EmployeeLocation table you suggest below is a
programming thing? I was trying to see if I could avoid that, perhaps I
cannot.

I don't want/need a subform for location. It only contains information on
the employee form that will be tried to the employee ID and current location,
therefore it is only a single value, the start date. I don't need to see the
history, I only need to see the start date change if the current employee
location is changed to location where the employee has already been.

Thanks again,
Leif

"Steve" wrote:

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



.

  #4  
Old June 5th, 2010, 12:53 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Linking two tables - a bit different

Leif,

From whay you are saying, you have two definitions for Start Date. First you
are saying it is the date an employee moves to a new location; second you
are saying if an employee goes back to a location where he's been before, he
keeps the original start date when he went to that location. In terms of
your database, that won't work! You need to either redefine start date to
one definition or add another field.

Take another look at your example. You have a one-to-many relationship
between employee and employee location. Any time yo have a one-to-many
relationship, data entry is via a subform.

Steve



"Leif" wrote in message
...
Steve,

Thanks for your reply.

I think I do need the location ID in the employee table. It is the
employee's current location. For example say the employee ID is 1 and he
is
currently at location 1. Also lets say he has been in two other locations
in
the past, location 2 and 3. We have (each row below is a record):

Employee table:
ID: 1 Location: 1

EmployeeLocation Table:
ID: 1 Location: 1 Start Date: 1/1/2010
ID: 1 Location: 2 Start Date: 1/1/2009
ID: 1 Location: 3 Start Date: 1/1/2008

I cannot take the latest date from the EmployeeLocation table and assume
that is his current location, since he could switch back to another
location
and that would not change his start date.

So adding a recording to the EmployeeLocation table you suggest below is a
programming thing? I was trying to see if I could avoid that, perhaps I
cannot.

I don't want/need a subform for location. It only contains information on
the employee form that will be tried to the employee ID and current
location,
therefore it is only a single value, the start date. I don't need to see
the
history, I only need to see the start date change if the current employee
location is changed to location where the employee has already been.

Thanks again,
Leif

"Steve" wrote:

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



.



  #5  
Old June 5th, 2010, 02:44 AM posted to microsoft.public.access.tablesdbdesign
david
external usenet poster
 
Posts: 34
Default Linking two tables - a bit different

I think I do need the location ID in the employee table.
It is the employee's current location.


but I was hoping there is a solution just using a query.


You can't query the employee's CURRENT location
if you want to ADD a new location.

You want a query that returns a blank location,
not the current location.

Try this:
Unique index on location id and start date

Inner join on location table with location table (returns no records)

Left join between employee table and (location table no records)

If it works, that may return an employee record with a blank location, which
you can update to a new current location.

But queries like that are very difficult to use in practice.


"Leif" wrote in message
...
Steve,

Thanks for your reply.

I think I do need the location ID in the employee table. It is the
employee's current location. For example say the employee ID is 1 and he
is
currently at location 1. Also lets say he has been in two other locations
in
the past, location 2 and 3. We have (each row below is a record):

Employee table:
ID: 1 Location: 1

EmployeeLocation Table:
ID: 1 Location: 1 Start Date: 1/1/2010
ID: 1 Location: 2 Start Date: 1/1/2009
ID: 1 Location: 3 Start Date: 1/1/2008

I cannot take the latest date from the EmployeeLocation table and assume
that is his current location, since he could switch back to another
location
and that would not change his start date.

So adding a recording to the EmployeeLocation table you suggest below is a
programming thing? I was trying to see if I could avoid that, perhaps I
cannot.

I don't want/need a subform for location. It only contains information on
the employee form that will be tried to the employee ID and current
location,
therefore it is only a single value, the start date. I don't need to see
the
history, I only need to see the start date change if the current employee
location is changed to location where the employee has already been.

Thanks again,
Leif

"Steve" wrote:

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



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.