DATABASE

[Pat]

I have a database set up with an Employee table which is extremly cumbersome.
We run a child care org. with 19 centres and 150 current employees. It
contains all personal data for current and past employees including all work
history including start dates and salary for each one (12 possible
positions), dependents, benefits, training. I use this table daily, find it
huge and make many queries from it, forms with it, track vacation time and
print reports for each employee several times per year. I make a copy at the
end of the year and rename it for the following year so that I am always
working with a current employee database. I want to effectively use this
data and reduce the size of the main table. When I analyze the table the
suggestions are really inappropriate. I attempted to put the dependent and
benefit info into a separate table and the work history into a table and make
a relationship to them but when I tested it and added date to the dependent
section of the main table it did not add it to the dependent table and this
cannot work effectively. LOOKING FOR SUGGESTIONS TO KEEP AN EFFECTIVE
WORKABLE DATABASE.

 

[Rick B]

Why would you copy a table and make a new one each year?

Do you have a 'termination date' in the table? Or an 'inactive' checkbox?

If so, then leave all those records in the table. Just limit your various
reports, queries, and forms to exclude terminated employees.

Further, you may want to purge records where the termination date is more
than XX years old. I would do this at the end of each year, and purge
records older than whatever your statutory requirements are. My personal
application deletes terminated employees after 2 years because we do not
need to see data older than that for our particular application. For a HRM
type of application, I would expect you'd need to retain the records 7 or
even 10 years.

I WOULD try to break all of this into smaller tables. Work history, salary
history, etc. should be separate tables. An emoployee will have one
employee record in the employee table, but he may have five or ten salary
changes during his career. This would mean that the employee table would
have one entry and the salary table would have multiple entries for the
given employee. Same thing with Job Titles.

I'm sure you will get other responses, post bak if you need further
guidance.

 

[Sharkbyte]

Pat:

You really should break that table up. I count no less than 8 potential
tables. Here are some samples below:

tblSites
SiteID (PK)
SiteAddress1
SiteAddress2
SiteCity
SiteState
SiteZip
SitePhone
SiteFax
SiteEmail
SiteManager (FK to tblEmployees.EmpID)

tblEmployees
EmpID (PK)
EmpName
EmpAddress1
EmpAddress2
EmpCity
EmpState
EmpZip
EmpPhone
EmpEmail
StartDate
ExitDate
SSN
WouldRehire
PositionID (FK to tblPositions.PositionID)

tblPositions
PositionID (PK)
PositionName
Salary (This assumes salary is position dependent and there are no
individual raises. If so, salary should move to tblEmployees.)

tblWorkHistory
PositionID (PK) (FK)
EmpID (PK) (FK)
PositionBeginDate
PositionEndDate
SupervisorAssessment

tblDependents
DependentName (PK)
EmpName (PK) (FK)
Date (Not sure of what this is for...)

tblBenefits
BenefitID (PK)
BenefitName
BenefitDesc

tblEmpBenefits
BenefitID (PK) (FK)
EmpID (PK) (FK)

tblTraining
TrainingID (PK)
TrainingName
TrainingDesc
Required
TrainingLocation
InstructorName

tblEmpTraining
TrainingID (PK) (FK)
EmpID (PK) (FK)
DateCompleted
DateExpires
CertificateOnFile

These are only suggestions, but would be a good place to start. As for
Vacation time, it would depend how the time is accrued. If it is by hand,
then probably track it in the Employee table.

HTH

Sharkbyte

 

[John Vinson]

I attempted to put the dependent and
benefit info into a separate table and the work history into a table and make
a relationship to them but when I tested it and added date to the dependent
section of the main table it did not add it to the dependent table and this
cannot work effectively.

You may be misunderstanding how relationships work.

A relationship will NOT automagically enter data into the dependent
table when you create a record in the main table. That's not what it's
*for* - the relationship is to *prevent* entry of inappropriate
records (i.e. a dependent of a nonexistant employee). It cannot, and
should not, create an empty "placeholder" record. You also should not
have a "dependent section" of the main table - dependent data should
exist in the dependent table, and noplace else!

You should certainly normalize the table into (probably) several
tables; the Employees table would have a unique EmployeeID, LastName,
FirstName, and other personal details; this table would contain
NOTHING about work history, or dependents.. Instead it would be
related one to many to (say) a Dependents table with an EmployeeID as
a foreign key, and DepLastName, DepFirstName, etc. fields. It would
also be related one to many to a WorkHistory table with EmployeeID,
JobID (linked to a Jobs table), DateStarted, DateEnded, and maybe
other info about this employee's history in this job.

To view and enter the data you should create a Form based on the
Employees table, with Subforms based on the related tables; use the
EmployeeID as the master/child link fields of the Subforms. When you
type something into the Dependents subform, the linking fields will
automatically fill in the EmployeeID, maintaining the linkage.


John W. Vinson[MVP]