How to maintain historical personnel data 1

[mslennb]

Kind of long question - I am new and need help designing a database to track current employees and turnover postions. Background info: Every employees is in a unique position number and there are multiple sites. Employees transfer from site to site creating constant turnover. A position # can be vacant and filled multiple times and an employee may transfer multiple times to different sites and pos#. At any given time, I need to track number of filled positions, # of vacant positions, # of times a position became vacant during the year, # of times an employee transferred between positions, and duration of time a position # remained vacant before being filled for cost purposes which I plan to link to Excel spreadsheet that has employee pay rate). So far I have tblPosition#, tblSiteID), tblPersonnel. I am using "Vacant Date" and "Filled Date" fields in the tblPersonnel table, but I lose the turnover history when I replace the date when an employee change position multiple times. Need to keep history. Any help would be greatly appreciated. Thanks.

 

[Remou]

I think you need a Position-Employee table:

[tt]PositionID )
EmployeeID ) PrimaryKey
DateStarted )
DateFinished
<...>[/tt]

* Filled positions are those with no DateFinished
* Vacant positions are those where an open record cannot be found in the Position-Employee table
* Number of times an employee transferred between positions is a count of EmployeeID
* Duration is DateStarted - DateFinished
* Number of times a postion became vacant is a count of PositionID
*Number of times a postion remained vacant before being filled is a count the 'gaps' in the Position-Employee table

And so on.

 

[willir]

mslennb

Hmmm. Hypothetically, if you own four different fast food joints, such as Tim Hortons (coffee and sandwiches), and employees may "roam" between the locations, they may be promoted / demoted, and they may move to part-time or to full-time. Does this fit your type of problem?

As a template, you may consider something along the following design...

tblEmployee
EmployeeID
LastName
FirstName
SitePositionID
etc...

Primary key = EmployeeID

Eg.
ID LastName FirstName SitePositionID[tt]
1 Smith John 5
2 Smith Jane 1
3 Doe Malcom 2
4 Lee Nancy 3
5 Lee Henry 4
6 Jackson Mark
[/tt]

tblPosition
PositionCode
Description

Primary key = PositionCode

Eg.
PositionCode Descriptions[tt]
LCook1 Line cook #1
LCook2 Line cook #2
DyMngr Day shift manager
NtMngr Night shift manager
Cash01 Cashier #1
[/tt]

tblSite
SiteCode
SiteLocation

Primary key = SiteCode

Eg.
SiteCode SiteLocation[tt]
Main Main St
TFox Terry Fox Rd
MTer Mother Teresa Ln
[/tt]

tblSitePosition
SitePosID
SiteCode
PositionCode

Primary key = SitePosID
SiteCode + PositionCode indexed as unique

Eg.
SitePosID SiteCode PositionCode[tt]
1 Main LCook1
2 Main LCook2
3 Main DyMngr
4 Main NtMngr
5 Main Cash01
6 Main Cash02
7 TFox LCook1
8 TFox DyMngr
[/tt]

tblEmployeeHistory
EmployeeID
TransactionDate
TransactionCode
SitePositionCode
Comments

Primary key = EmployeeID + TransactionDate + TransactionCode

Eg.
EmployeeID TransactionDate TransactionCode SitePositionID[tt]
4 1/15/2001 Hire 1
4 7/30/2001 Transfer 7
4 2/14/2002 Change 5
4 3/21/2003 Promote 8
6 5/12/2006 Hire 1
6 11/5/2006 Transfer 7
6 4/12/2007 Terminate
[/tt]

So, Nancy Lee was hired 01/15/01, and subsequently was transferred, changed positions and promoted. Likewise, Mark was hired on 5/12/06 and subsequently let go on 4/12/07.

I tried to keep it on the simple side, but it could get a little more complicated. For example, instead of using a different code for each position and location, you may want to keep the total number of positions required. For example, instead of LineCook #1, #2, etc, the number of line cooks required at the Main Street Store is 6. This would be a more normalized solution, but using LCook1, etc would help you visualize things a bit better, perhaps.

Hopefully, this gives you an idea in how you may wish to proceed.
Richard

 

[mslennb]

Thanks for the replies which helps me track the Employee History. How do I track the Position History. Example,
Nancy was LCook1 at Site2 and promoted to LCook2 at Site3 on 2/1/05. Now the LCook1 position at Site 2 is vacant as of 2/1/05 and the LCook2 position at Site 3 is filled as of 2/1/05.
John Smith Cash01 at Site1 promoted to LCook1 at Site2 on 4/1/05. Now Cash01 at Site1 is vacant as of 4/1/05 and LCook1 at Site2 is filled as of 4/1/05.
LCook1 at Site2 was vacant between 2/1/05 and 4/1/05. LCook1 could become vacant again on 10/1/05 if John Smith resigns or transfer to another position or site.
How do I track the position turnover history and duration of vacancy (2/1/05 to 4/1/05)?
Thanks.
Lenn

 

[lespaul]

[off topic]
Richard,
How nice to "see" you again!! And your usual well thought out response is a treat to read!
Have a great day!
Leslie
[/off topic]

 

[mslennb]

How do I create "indexed as unique?"

I have three tables:
1. tblSite with SiteID as the Primary Key
2. tblPosition with PositionID as the Primary Key
3. tblSitePosition with SitePositionID as the Primary key and SiteID and PositionID within the table.

How do I set SiteID + PositionID indexed as unique?
Also, can three fields be designated as primary keys in the same table?

Thanks
Lenn

 

[PHV]

I think you don't need a SitePositionID field in tblSitePosition.
When in design view for the tblSitePosition table you simply have to select both SiteID and PositionID and click the PK button to create a composite primary key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886