Lookup Wizard to a Field within the same table

[gheyman]

If I have a table with multiple fields, can I have a field that is a list based on another field?


I am creating a list of departments. Some departments are "Children" of other departments

Department
200 Finance
201 Accounting
202 Program Finance

"200 Finance" department has two departments that report to it; 201 Accounting & 202 Program Finance.

In my table I have a field where they select a department and then a field after that one where they select the departments "Parent" or they report up to.

Field Title: Department 201 Accounting
Field Title: Directorate 200 Finance

Since the "Directorate" will be a sublist of Departments, I want the directorate field to be a Listbox from the Department field.
Can you do that in access? have a Lookup Wizard field based on another record in the same table?

Let me restate, I know you can do it from the perspective that the Wizard lets you choose that field, my question is, will this work and/or will his cause problems in the database?

 

[Micron]

As long as you can make the relationship between a chosen child and it's parent this bottom up approach should work OK. It might get very difficult if you ever end up with multiple parent choices for any child.