How do parent-child hierarchy relations ?

[David]

I have a need to set up a table or tables for departments
and sub-departments.
The design I came up with is a single table with 2 columns:
Department
Parent department

This allows all departments to be defined in the first
column and child departments to be defined by specifying a
parent. This simple arrangement allows hierarchies of any
complexity.
However in 'table relationships' it does not allow me to
link parent department to department and so enforce
referential integrity within the table.
Is there a better way to do this. I'd prefer to avoid
multiple tables if possible.
Any ideas appreciated.
- David

 

[Tim Ferguson]

However in 'table relationships' it does not allow me to
link parent department to department and so enforce
referential integrity within the table.

Yes it does: but you have to put two instances of the table into the
relationships window. Just use Add Table again. The new instance will be
called Departments_1 but that doesn't matter. You can then drag the Parent
field over to the DepartmentNumber field and make the relationship.

Hope that helps


Tim F

 

[John Vinson]

However in 'table relationships' it does not allow me to
link parent department to department and so enforce
referential integrity within the table.

It does if you add the Departments table to the relationships window
TWICE - Access will alias the second table icon as Departments1, but
you can establish the relationship.

 

[David]

Wonderful. Thanks.

-----Original Message-----


Yes it does: but you have to put two instances of the table into the
relationships window. Just use Add Table again. The new instance will be
called Departments_1 but that doesn't matter. You can then drag the Parent
field over to the DepartmentNumber field and make the relationship.
Tim F