Re: Capturing an employee in a team at a point in time
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Tue, 13 Feb 2007 23:28:49 +0900
You have a classic many-to-many relation between teams and employees. You therefore need 3 tables, like this:
Team table (one record for each team), with fields:
TeamID primary key
TeamName Text name of this team
TeamStart Date when this team began
TeamEnd Date when this team disbanded.
Employee table (one record for each employee), with fields:
EmployeeID primary key
Surname Text
Firstname Text
...
TeamEmployee table
TeamID which team the employee joined
EmployeeID who joined this team
JoinDate Date when the employee joined the team.
DepartDate Date when the employee left the team.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<raphozs@xxxxxxxxxxx> wrote in message
news:1171376011.832065.152780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi, I would really appreciate some help on the following query:
I would like to design a db that captures employee data. At the moment
in the Employees table I have an entry for a team. However, it only
captures the "Current" team the employee belongs to. If the employee
changes teams then changing it in the current table will change it for
all previous entries and associated date.
The question is therefore, how would I go about to create a table (or
tables) whereby I can associate an employee a to a specific team at a
given point in time. For example if Employee1 was in team A for the
first half of the year and team B for the 2nd half of the year, then a
query associated with 01 Feb will return that the employee was in team
A but a query on 10 Oct will indicate the employee belonged to Team B.
Any help will be greatly appreciated. I am not sure what the apporach
I should be taking?
Thannks in advance.
.
- References:
- Capturing an employee in a team at a point in time
- From: raphozs
- Capturing an employee in a team at a point in time
- Prev by Date: Capturing an employee in a team at a point in time
- Next by Date: Re: Event Mgmt 2000
- Previous by thread: Capturing an employee in a team at a point in time
- Next by thread: RE: Capturing an employee in a team at a point in time
- Index(es):
Relevant Pages
|
