Designing Employee Master Table

[JOEYB99]

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]

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]

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

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]

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