Designing Employee Master Table

JOEYB99 · Oct 14, 2012

I am using Access 2007, and just starting to design a database.

I am curious as to how other programmers/techicians account for maiden names and name changes of employees. My primary key will be the employee number obviously but when I produce queries and reports I would like to be accurate for the employee first name and last names.

Do you create/maintain a sub-table that is linked to the main employee table, or do special coding on-the-fly?

Any help would be greatly appreciated, even references to other Q and A, or web-sites.

PHV · Oct 14, 2012

A very good article to read before starting the design of a database:

http://r937.com/relational.html

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

Golom · Oct 14, 2012

The obvious thing would be to change "Murgatroid Milktoast" to "Barry Beefcake" upon receiving notification of the name change directly in the employee table.

Why do you care about what their name was historically since it is not a key into your table nor, presumably, in any related tables?

If, for some reason, you do care then
[pre]
tblENames
EmployeeID FirstName LastName EffectiveDate
123 Bill Brown 01/01/1900
123 Hagar Horrible 01/01/2005
123 James Bond 01/01/2008[/pre]

Then

Code:

Select E.EmployeeID, N.FirstName, N.LastName, ... otherfields

FROM tblEmployees As E INNER JOIN tblENames As N
     ON E.EmployeeID = N.EmployeeID

WHERE N.EffectiveDate = (Select MAX(EffectiveDate) From tblENames As X
                         WHERE X.EmployeeID = E.EmployeeID)

Seems to be a lot more trouble than its worth.

assets · Oct 16, 2012

SELECT [EmployeeID], [FirstName] & ", " & [LastName] FROM Employees ORDER BY [FirstName] & ", " & [lastName];

Never give up never give in.

There are no short cuts to anything worth doing

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Designing Employee Master Table

JOEYB99

Technical User

PHV

MIS

Golom

Programmer

assets

Technical User

Similar threads

Part and Inventory Search

Sponsor