Setup of tables - Employees, Positions, Training 1

[sdollen]

Just a little background real quick - I’m an accounting person by trade and tinker with access. The HR director in the state agency I’m working in, has to maintain 22 reports that are done in Word and Excel. The reports all contain variations of data pertaining to the Employees (personal data, affirmative action data) and also pertaining to the Employee’s position (pay, fulltime?, title, Jobcode, etc. etc.). So, I was hoping to make it easier for her to be able to keep the data in access and then create the reports she is required to do on a monthly basis.

I have read Paul Litwin’s Fundamentals of Relational Database Design but am struggling with this probject and hope someone can give me some guidance, help, direction.

Here is what I have so far.


tblEmployee
EmployeeID (pk)
FirstName
LastName
SocialSecurity
Birthdate
Etc. etc.



tblPosition
PositionID (pk)
PIN ****************
ClassificationID fk) (1)
Division (2)
ExemptStatus (3)
Title
PayBandID (fk) (4)


tblTraining
TrainingID (pk)
ConferenceName
ConferenceLocation
AttendenceDate
Etc. etc. etc.


Quickly some, but not all, of the things I think I need to do include:

The fields that I labeled with numbers are fields which have several options. As an example, in tblPosition, there are four different classifications available: Unclassified Official, Unclassified Admin. Assistant, etc. I thought I could make the “Classification” field into a combo box using a table holding the classifications as the source. Hopefully I’m saying that correctly. I think these are referred to as lookup tables?

For “Paybands” I would make a new table that would have the paybands (I, J, K etc.) along with the max, mid, min. salary ranges for that payband. Then, I’d use that table as the source for the Payband combo box in tblPosition.

I think I’ll also need to create join tables: tblEmployeePosition, tblEmployeeTraining


The “PIN” number in the tblPosition is tied to the position, not the employee. So, if someone leaves a job, the next person coming in will have that PIN number.

This to me raises the question of how I maintain a job history for an employee if they change jobs.

Also, looking way way way way down the road – how could all of this be on one form using tabs? I’ve played around with trying to add a tab whose source is a different table, and am not getting the hang of it. I’m picturing that it would be easier for the user to have a “personal info” and “position info” tab on an input form.

I’ve thrown a LOT of info out there and hopefully it makes some sense to someone.

Thanks in advance for any help.

 

[sdollen]

I've been thinking since I posted this - and think I know one of the answers. I wouldn't want a form that is going to be used to input info regarding a job position, included as a tab on the input form for the employee info. Since the two are totally seperate.

I know that there will be a join table or link table between the employee and the position.

What I'm hoping to be able to do, and not sure how to do it, is make it so the HR director can just look at an employee and also be able to see their position info at the same time without actually being required to print a report.

Thanks in advance for any info/advice/direction.

 

[Lilliabeth]

Since an employee can have multiple positions and a position can have many employees, you do need tblEmployeePositions. If you add a field such as dtmTerminationDate you can maintain historical data.

The HR person could use a Employees form that has a employeepositions subform. If they want to see just the current position, draw the subform data from a query where dtmTerminationDate is null.

Yes, lookup tables. Yes, you need tblEmployeeTraining.

Looks like you are doing a great job. Read up on forms and subforms.

 

[sdollen]

thanks for that comfirmation and info, Lilliabeth!

Term date never crossed my mind. One thing about that really quickly? The HR person was hoping that I could add a field "Active Status" on the Employee that could be switched on/off. It would have "Retired, Active, Non-Active" as options. The Non-active would be if someone resigned or was fired. This should probably be done in addition to the "TerminationDate"?

Thanks again!

 

[Lilliabeth]

I think a status field in tblEmployees is a must. When people leave, you want to quit displaying their name in drop-downs, right? A combo box can draw data from a query where Status is yada yada. In addition, you might want a list of everyone who retired, etc.

The Term date in tblEmployeePositions allows you to change the position the employee has. They will have one record for each position they have ever held.

So, yes, I think you need both if you do want to record positions over time (and if there is much changing of positions, I am sure you do want to record it).