Tables Cascading Combo Boxes and Empty Options (1 Viewer)

I've built my first attempt at this idea. I have a 4 level cascade set up and its working as intended, but one issue I have is some 3rd level options don't require a set of 4th level options, and some do. My table structure is
tblFirstLevel
FirstLevelID
FirstLevelName

tblSecondLevel
SecondLevelID
FirstLevelID
SecondLevelName
and so on

So for any third level name that has no fourth level options, the fourth level table record looks like:
FourthLevelID = Autonumber
ThirdLevelID=Foreign Key
FourthLevelName= Unassigned.

I have a bunch of these "Unassigned" records, and it doesn't seem to me like proper design, but I don't know how to fix it. Any ideas are appreciated.

Why is there a fourth level record if there is no fourth level data? What happens if you just delete the unassigned records?

Also, you can do this all in one self-referencing table, like...

tItem
ItemID (Primary Key)
ParentItemID (Foreign Key - points to a parent row in the same table)
ItemName

Click to expand...

hth
Mark

Some 3rd level records have 4th level options, some don't. A self referencing table is a new one for me, I'll have to study that.

This reference may be helpful in understanding self-reference table.

It may be helpful to readers if you gave use some samples of your data.

If you have unassigned names--do you really need those records in your table?

DeanFran said:

Some 3rd level records have 4th level options, some don't.

Click to expand...

The structure you are working with here is called a tree, also known as a hierarchical data structure. In a tree, a node with child nodes is sometimes called a branch node, and a node with no children is sometimes called a leaf node. It is fine to have a tree in which some 3rd level nodes are branches and others are leaves, but leaf nodes should not have child nodes.

A common tree structure is a file system on a disk, in which the branch nodes are folders, and the leaf nodes are files. It is also possible in this case that a folder is a leaf node too.

The most efficient way to navigate a tree is using a programming technique called recursion, in which a sub-routine calls itself. Anyway, there are some search terms for you.

hth
Mark

Thank you for that explanation. Looks like I have some work to do. In answer to the question regarding data, I am creating this for our quality department to use to manage the root causes of things that require an investigation. For instance, the first level "cause" in this case isn't really a cause, but more of an assignment, i.e. Manufacturing, Laboratory, Shipping, etc. After that, if say "Manufacturing" is chosen, then the next level starts to drill down, maybe "labeling" is the next level, then the next might be "unit label", or "shipper label", finally level 4 would be "illegible", "misapplied" etc. Some categories end at the "unit label", "shipper label" level, with no further breakdown.