How to create a 'Directory' in Access... or Categories & Sub-Cagegories & Sub-Sub-Categories & etc

[Will]

Can anyone point me to some examples of how to do categories and
sub-catagories and sub-sub-categories, etc?

Maybe like a directory tree.

Right now I want to create categories for "Contacts" like...

Vendor
Customer
Other

- If you selected Vendor you would chose from a list of sub-categories.
- After you selected the sub-category you would chose a 3rd level
category... etc

If you selected Customer your sub-category choices would be different and
depending on the sub-category choice you would get a differenent
sub-sub-category list.

Example 1 - Vendor:
Jones Inc | Metal Supplier | Steel | Milled Parts | Small Parts | Backup
Supplier

Example 2 - Phone Directory:
Transportation | Land Vehicles | Trucks | Domestic | Used | 4-Wheel Drive |
Crew Cab

thanks for any help on this.

 

[Guest]

in the most general of terms; you should think of these each as tables and
then you link them together when you create your form or report.

if you are inputting new data then in general you would then use a form to
organize these tables.....while if you were presenting info (rather than
inputting) one would tend to use a report to organize the tables...

very very general advice....but hope it is helpful....

 

[Larry Linson]

if you are inputting new data then in
general you would then use a form to
organize these tables.....

And, to obtain the correct subcategories for a category, you would normally
use a series of Combo Boxes, each referring to the previous one(s) in
Criteria. Select in the first Combo, and in its AfterUpdate event, Requery
the next one down the line, and repeat until you have covered all the levels
needed in your application.

I can't remember ever doing more than three (or maybe 4) levels. But it is
just a matter of repeating what you did in the higher level combo boxes'
AfterUpdate.

Larry Linson
Microsoft Access MVP

 

[Dirk Goldgar]

Can anyone point me to some examples of how to do categories and
sub-catagories and sub-sub-categories, etc?

Maybe like a directory tree.

Right now I want to create categories for "Contacts" like...

Vendor
Customer
Other

- If you selected Vendor you would chose from a list of
sub-categories.
- After you selected the sub-category you would chose a 3rd level
category... etc

If you selected Customer your sub-category choices would be different
and depending on the sub-category choice you would get a differenent
sub-sub-category list.

Example 1 - Vendor:
Jones Inc | Metal Supplier | Steel | Milled Parts | Small Parts |
Backup Supplier

Example 2 - Phone Directory:
Transportation | Land Vehicles | Trucks | Domestic | Used | 4-Wheel
Drive | Crew Cab

thanks for any help on this.

In a theoretical sense, if all you need to store about any category or
subcategory is its name and which higher category it belongs to, you
could use a single table to store all these categories, using a
structure like this:

Categories
------------------------------------------------
CategoryID (autonumber, primary key)
CategoryName (text)
ParentCategory (number, FK to tblCategories)

This table would be related to itself, you see, because the
ParentCategory field contains the CategoryID of this record's "parent",
or Null if the record has no parent.

So your first combo box, cboMainCategory, would have a rowsource query
like this:

SELECT CategoryID, CategoryName FROM Categories
WHERE ParentCategory Is Null

The next combo, cboSubCategory, would have a rowsource like this:

SELECT CategoryID, CategoryName FROM Categories
WHERE ParentCategory = [Forms]![YourForm]![cboMainCategory]

and you'd requery it whenever you update cboMainCategory.

The next combo after that, cboSubSubCategory, would have a rowsource
like this:

SELECT CategoryID, CategoryName FROM Categories
WHERE ParentCategory = [Forms]![YourForm]![cboSubSubCategory]

and you'd requery it whenever you update cboSubCategory. And so on.