Create Heirarchy

[hansdbase]

Hello, I am trying to create an organizational hierarchy. It is easy enough to create the table that lists all the departments in the company. But I need then to then assign the parent and all children to each of them. And I need to ensure that only one department is at top of the organizational structure, the corporate HQ, such that no one can assign it a parent. I am not sure the best way to do this. Thanks.

[CJ_London]

In your table include a parentFK field. This is populated with the PK of the parent record. To indicate the top parent, either leave as null or populate with the PK (I prefer the latter)

Depends on what you are going to do with the data but you may need to review what is called a recursive function

[hansdbase]

Thank you. So I understand that I should create a parentFK field in my table. So my table will now look like this:
OrganizationID PK
OrganizationName
ParentFK

And I understand that I would populate it with the organizationID of the parent record.

I like the idea that the top parent would have a parent id of itself. But I guess I would have to add some type of unique filter or constraint so that only one department has itself as the parent because I can only have one top? I am trying to prevent someone for instance making two different departments reporting to themselves because only one, the top department, should do that. Is there an easy way to do that?

[June7]

This is a common topic. Look at Similar Threads list at bottom of this page.

Also review
https://www.sfmagazine.com/articles/...archical-data/
https://answers.microsoft.com/en-us/...8-c92c27b7e196
https://stackoverflow.com/questions/...ional-database

Access does not handle recursion well. If you want to migrate data to SQLServerExpress, managing this might be easier.

"Easy" depends on your POV and experience.
Maybe could use combobox or textbox Validation Rule and Validation Text properties to control the ParentFK input. Or use VBA procedure behind form to validate data entry of the ParentFK input. Or a combobox RowSource that excludes OrganizationID of current record (this would require VBA).

[CJ_London]

But I guess I would have to add some type of unique filter or constraint so that only one department has itself as the parent because I can only have one top?

yes - easy enough to do - for example a validation rule might be

if orgpk=parentfk and dcount(“•”,”departments”,”orgpk=parentfk”)>0 then msgbox(“can’t do this”)

[madpiet]

[QUOTE=June7;522440]This is a common topic. Look at Similar Threads list at bottom of this page.

Also review
https://www.sfmagazine.com/articles/...archical-data/
https://answers.microsoft.com/en-us/...8-c92c27b7e196
https://stackoverflow.com/questions/...ional-database

Access does not handle recursion well. If you want to migrate data to SQLServerExpress, managing this might be easier.

At least querying it. Use a Common Table Expression and make it recursive. (Learning that was super fun!) here's an article: How Recursive Common Table Expressions Work (learnsql.com)