Help with classification tables

[asc4john]

I am trying to build a set of tables to allow "types" to be assigned
to specific items in a plant.
The grouping of these items would follow this kind of pattern.
Division - the major craft group dealing with the item, Group - the
craft subgroup, Category - the kind of thing and Type the specific
kind of thing.
There are thousands of different kinds of items but they fall roughly
into this scheme, not prefect but close.
I have look-up tables tblDivision, tblGroup, tblCategory and tblType.
I have tried several different arrangements using these tables but
none seem to be satisfactory. What I need is some ideas on how this
kind of thing is implemented. The one I think would work best is:
tblGroup with a DivisionID as a foreign key, tblCategory with GroupID,
tblType with CategoryID. The specific item would then have TypeID as
a field. I have tried using all four IDs in the specific item table
but have found no way to "constrain" the look-ups, i.e. I pick a
Division but should only be able to pick certian Groups and then only
certian Categories and so on .

 

[Carl Rapson]

I am trying to build a set of tables to allow "types" to be assigned
to specific items in a plant.
The grouping of these items would follow this kind of pattern.
Division - the major craft group dealing with the item, Group - the
craft subgroup, Category - the kind of thing and Type the specific
kind of thing.
There are thousands of different kinds of items but they fall roughly
into this scheme, not prefect but close.
I have look-up tables tblDivision, tblGroup, tblCategory and tblType.
I have tried several different arrangements using these tables but
none seem to be satisfactory. What I need is some ideas on how this
kind of thing is implemented. The one I think would work best is:
tblGroup with a DivisionID as a foreign key, tblCategory with GroupID,
tblType with CategoryID. The specific item would then have TypeID as
a field. I have tried using all four IDs in the specific item table
but have found no way to "constrain" the look-ups, i.e. I pick a
Division but should only be able to pick certian Groups and then only
certian Categories and so on .

Your table structure sounds appropriate, especially storing only the TypeID
since the other values can be determined from that.

What you are describing needs to be done with a form, not in the table
directly. On your form, place a combo box representing each table -
Division, Group, Category, and Type. The Division combo box should have its
Row Source based on tblDivision, since it won't change, but the others
should be empty. In the AfterUpdate event of the Division combo box, alter
the Row Source of the Group combo box to display only those groups for the
selected Division. Something like:

cboGroup.RowSource = "SELECT field1,field2 FROM tblGroup WHERE DivisionID="
& cboDivision

Proceed in this manner until the user has selected the Type, which is the
value that would be stored in the table.

Carl Rapson