storing point in time values in a table

[Muppet]

What is a good way to approach a "point in time" design
for a lookup table and its relation to a transaction table?

I have a Call table and an Employee table (I have more but
I think this will suffice for the purposes of explantion).

tblCall
EmployeeID
TimeOfCall
'other details

tblEmployee
EmployeeID
FirstName
LastName
Department

Now let's say that when I take a call, I assign EmployeeID.

Further let's say that the employee moves to a different
depeartment in 6 months.

When I run a select query in 6 months, the call will
reflect the CURRENT department information. However, I
want the call to reflect the POINT IN TIME department.

What is the best way to handle this?

 

[Steve Schapel]

Muppet,

In essence, it would involve taking the Department field out of the
tblEmployee table, and put it in another table where you track the
employee's history...
Table: EmploymentHistory
EmploymentHistoryID
EmployeeID
StartDate
Department

That way, you will be able to work out in a query which department the
employee was in at the date of the Call in question.

 

[Muppet]

Thanks.

Muppet,

In essence, it would involve taking the Department field out of the
tblEmployee table, and put it in another table where you track the
employee's history...
Table: EmploymentHistory
EmploymentHistoryID
EmployeeID
StartDate
Department

That way, you will be able to work out in a query which department the
employee was in at the date of the Call in question.