×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Designing Employee Master Table

thread700-1695736

Designing Employee Master Table

Designing Employee Master Table

(OP)

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.

RE: Designing Employee Master Table

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
tblENames
EmployeeID   FirstName    LastName    EffectiveDate
   123       Bill          Brown         01/01/1900
   123       Hagar         Horrible      01/01/2005
   123       James         Bond          01/01/2008

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.

RE: Designing Employee Master Table

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 smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close