Have supevisor populate a field based on the department

[AlanP]

I'm not the greatest with relationships so if anyone can help I'd greatly appreciate it!

WHAT: We have a personnel database with several tables. We would like it when a department an employee is assigned to is selected from a drop down list, the 'supervisor' field is populated with the supervisor.

How can this be done? I've already created a 'supervisor' table. The only problem I can see is that all the employees assignments are related to another table called 'assignment history'.

Thanks in advance!

[wmugrad28]

How many supervisors are in a department (minimum and maximum)?

Greg

[Ender]

You don't need a supervisor table for this, but a Department table may be useful. Having one, you can populate it with all the department names along with who the supervisor is (I'm assuming one supervisor per department.) Then relate between Personnel and Department by Department Name (or ID). With this relationship, you can show the supervisor for the employee's current Department. If you're storing the supervisor's ID in the Department table instead of the name, then you'll need another TO of Personnel linked to Department by Department::SupervisorID = Personnel 2::EmployeeID. Use this TO to show the Supervisor's name on the Personnel record.

It's not clear what your Assignment History table tracks, how it's related, or whether you need it to store the supervisor's then name.

[AlanP]

thanks guys!

basically the employees assignments are kept in a table called "Employment History". In the entry layout it is accessed via a portal (so we can have a running history of where they have been assigned).

On most other layouts a calculation is used to show where their most current assignment is.

Would a calculation be better to show the supervisor then?

[Ender]

A calc is not the answer. If you wish the Employment History to remember the Employee's supervisor at the time of the assignment, then use a text field with an auto-entered lookup to pull the supervisor's name from the Department table (or that Personnel 2 TO I described above.)

[AlanP]

Ender how do you do an auto-entered lookup (I've never done one before)?

I think that might be the answer...basically all the departments are in the (currently named) "supervisor" table along with the supervisor name.

In the "main" table there is a portal to the "assignment" table where a running history of all the departments the employee was assigned to.

So would a auto-entered lookup work in this instance?