Steve, from what I understand, you might want to have a separate table to track Positions. Have one table for Employees, and another to track their Positions, since their Position can change from time to time. Here's what I would do:

EmployeeT: EmployeeID, FirstName, LastName, etc.

PositionT: PositionID, EmployeeID, Description, StartDate, EndDate

Now, your data could look like this:

EmployeeT
-----------
1, Joe, Smith
2, Sue, Jones
3, Bill, Walters

PositionT
-----------
1, 1, Programmer, 1/1/09, 12/1/09
2, 3, Analyst, 4/6/08, NULL
3, 1, Trainer, 2/1/06, 12/31/08
4, 2, Sales Rep, 1/1/05, 12/1/06

So now you know that Joe Smith (ID 1) started out as a Trainer on 2/1/06 and then switched to a Programmer on 1/1/09. He left the company on 12/1/09.

Sue Jones (ID 2) was a Sales Rep from 1/1/05 to 12/1/06.

Bill Walters (ID 3) was an Analyst from 4/6/08 and is still with the company (no end date).

That gives you enough information to track each person and their history of positions. You could also add a SALARY field to the position table if you want to track changes in their pay.

If at any time you want to look up their current position, just do a DLOOKUP for their record in the position table where the EndDate is null. Or, if you want to search for their data on a specific date, you can those dates in the DLOOKUP function call.

If you want to find the last position that a terminated employee held, just use DMAX on the date field.

Does this help push you in the right direction?