Reference Fields within the same Record

[beat1078]

I have a table with information about an individual and their supervisory chain and what job title the employee is.

If done correctly the top of the supervisory chain will be entered in first and then the employees which they supervise after that. When I print out the form I list the employee and their information as well as the supervisor information. How can I list the supervisor position on the report as well without breaking open a new table?

I see the method, I am just unable to come up with the code for putting the referenced field in another record if it is equal to the current field in this record.

Thanks

--Beat

[jkantner]

I have a table with information about an individual and their supervisory chain and what job title the employee is.

If done correctly the top of the supervisory chain will be entered in first and then the employees which they supervise after that. When I print out the form I list the employee and their information as well as the supervisor information. How can I list the supervisor position on the report as well without breaking open a new table?

I see the method, I am just unable to come up with the code for putting the referenced field in another record if it is equal to the current field in this record.

Thanks

--Beat

-------------------------------

I hate to tell you, but you really should already have at least 3 tables. One for the departments, one for supervisors and one for the employees.
The department table would have a department id, department name and whatever else you would like to know about the department. This allows for flexibility of adding/changing/deleting supervisors and employees.
Then the supervisor table would include the department id so there would be a link between the two. This would allow for ease of changing supervisors in and out of departments.
The employee table would also have a department id that it would use as a link to the department and supervisor tables.
With this set up, you don't have to change each employee record each time a supervisor is changed.
To handle the maintenance in these tables you need a main form and a subform/s that connect on department id.
For reporting, you would use queries to connect the tables. This would allow you to report your info in any grouping you choose to.
Microsoft has helpful info on how to create forms and subforms at: http://office.microsof t.com/en-ca/assistance/HP051883951033. aspx

jkantner