Advice on Table Structure (Agencies & Sub-Agencies)

Hi Everybody-

I am trying to create a Table structure to represent the relationship
between Agencies and their Sub-Agencies.


There are Agencies and Sub-Agencies.

Some Agencies have Sub-Agencies and some don't.

For Example: The University of California.

The Univ of California has different campuses, each with its own unique
code.

101 Univ of California @ Berkeley
102 Univ of California @ Los Angeles
103 Univ of California @ San Diego
104 Univ of California @ Davis
etc...

BUT the "Univ of California University System" also has a "Central
Office."

So...

100 Univ of California CENTRAL OFFICE

The "Univ of California CENTRAL OFFICE" is the "Parent Agency" of all
of the different Campuses.

So, I've set up a preliminary Agency table with the following fields:

Agency# (Text & Primary Key)
Agency_Name (Text)
Parent_Agency (Text)

So, my AGENCY table looks kind of like this

Agency# Agency_Name Parent_Agency

100 Univ of California CENTRAL OFFICE 100
101 Univ of California @ Berkeley 100
102 Univ of California @ Los Angeles 100
103 Univ of California @ San Diego 100
104 Univ of California @ Davis 100
120 Stanford 120
121 USC 121
122 Pepperdine 122


Stanford, USC, and Pepperdine are their own Parent Agencies SO their
Parent_Agency Equals their Agency#.


I also created a table called PARENT_AGENCY. This table only lists the
Parent Agencies.

So my table has the following fiels:

Parent_Agency (Primary Key & Text)
Agency_Name (Text)

Parent_Agency Agency_Name

100 Univ of California CENTRAL OFFICE
120 Stanford
121 USC
122 Pepperdine

I have created a "one-to-many" relationship between the table,
PARENT_AGENCY, and the table, AGENCY.

Each PARENT_AGENCY can have 1 or more AGENCIES.
Each AGENCY can have 1 and only 1 PARENT_AGENCY.

The reason why I'm doing this might not be that apparent now, but I
have Parent Agencies such as the Department of Corrections that have
many Sub-Agencies.

I'm trying to create a data-entry screen where the user can select a
Parent Agency in one combo-box, then have a second combo-box display
only those Sub-Agencies under that Parent Agency.

For example: If the user selects "Univ of California CENTRAL OFFICE" in
the first combo-box, then I only want those
Sub-Agencies that have the "Univ of California CENTRAL OFFICE" as a
Parent Agency to populate the second combo-box.

I'm not sure if this is the best way to do it or not so I'm asking you
guys and gals with more experience for input.

The one problem I keep thinking of is what if an Agency is its own
Parent Agency?

For example, I have many Agencies that are their own Parent Agencies,
i.e. Stanford, USC, Alcoholic Beverage Control.

What should I do?

If I select the "Parent Agency" from the Parent_Agency table in the
first combo-box, then what do I do with the second combo-box?

Agencies that are their own Parent Agencies (i.e. Stanford, USC,
Alcoholic Beverage Control) will be listed in both tables, AGENCY and
PARENT_AGENCY.

Should I code the second combo-box to be invisible if an Agency is its
own Parent Agency? How would I do that?

Can anybody suggest a better way of doing this? Any advice would be
greatly appreciated!

Thanks a bunch!

Megan

re: Advice on Table Structure (Agencies & Sub-Agencies)

Hi Megan! I think your PARENT_AGENCY table is unnecessary. Also, I
would leave the Parent_Agency value Null when the agency is the root
node. I think you can do everything you need to do without the second
table. The syntax for the RowSource SQL is slightly simpler if you
don't use the text values for the primary key. One way would be to do
something like (for two sub levels):

tblAgency
AgencyID AutoNumber PK
AgencyNum Text
AgencyName Text
ParentAgency Long

1 100 Univ of California CENTRAL OFFICE Null
2 101 Univ of California @ Berkeley 1
3 102 Univ of California @ Los Angeles 1
4 103 Univ of California @ San Diego 1
5 104 Univ of California @ Davis 1
6 120 Stanford Null
7 121 USC Null
8 122 Pepperdine Null

cbxParentAgency.RowSource = "SELECT AgencyID, AgencyName FROM tblAgency
WHERE ParentAgency IS NULL;"

In cbxParentAgency_AfterUpdate,

If Not IsNull(cbxParentAgency.Value) Then
cbxSubAgency.RowSource = "SELECT AgencyID, AgencyName FROM tblAgency
WHERE ParentAgency = " & cbxParentAgency.Column(0)
Else
cbxSubAgency.RowSource = ""
End If
cbxSubAgency.Value = Null
cbxSubSubAgency.Value = Null
cbxSubSubAgency.RowSource = ""

In cbxSubAgency_AfterUpdate,

If Not IsNull(cbxSubAgency.Value) Then
cbxSubSubAgency.RowSource = "SELECT AgencyID, AgencyName FROM
tblAgency WHERE ParentAgency = " & cbxSubAgency.Column(0)
Else
cbxSubSubAgency.RowSource = ""
End If
cbxSubSubAgency.Value = Null

You can also use the comboboxes' ListCount property to '.Visible =
False' any comboboxes that don't have any choices and '.Visible = True'
any that have choices. For this example the ColumnCount is two, the
BoundColumn is zero and the ColumnWidths look something like 0";3.1"

When you select 'Univ of California CENTRAL OFFICE' you should get four
choices in the dropdown for cbxSubAgency. This drill down idea does
force you to start out initially at the root nodes but following the
heirarchy down is very intuitive. Once the nodes are in the comboboxes
you can start the next traversal by continuing from any level you want.

James A. Fortune