troubleshooting Question

Access - Table Design related to creating a Classification Type and Classification SubType

Avatar of wlwebb
wlwebbFlag for United States of America asked on
Microsoft Access
6 Comments2 Solutions386 ViewsLast Modified:
Hello All

As most all of you know I am "Hacking" around at this whole Access thing.  With your all's Expert help I have learned a tremendous amount.  Thank you.

Now then I am considering/pondering how to break down some information and am contemplating using a Classification Type and a Classification Sub Type.

For the purpose of this discussion, I will use an Example framed like say Suppliers....

So let us say I have a Table called
dta_Suppliers

In that Table I would have fields
SupplierID - PK Autonumber
SupplierCode - Text
SupplierName
etc....

Now then, in order to Classify Suppliers into various groups I envision a Table called
SupplierType

In that Table I envision fields
SupplierTypeID - PK - Autonumber
SupplierType - Text

HOWEVER I would also like to have SubType Classification ability.  So therefore I envision creating a Table Called
SupplierTypeSub

In that Table I envision fields
SupplierTypeSubID - PK - Autonumber
SupplierTypeSub - Text


That all is simple enough.  I could just put a field in the Supplier Table for each Type and TypeSub.  However, if I do that I know I'll end up with all kinds of Combinations of Type and TypeSub which would probably render the info useless in the long run.

SO, I thought about Controlling that by creating a Many To Many table called
ctl_SupplierTypeToTypeSub

In that table I would have:
SupplierTypeID - PK
SupplierTypeSubID - PK

HOWEVER if I do it that way, I presume that I would then either I have to put a record called {None} or {N/A} in my SupplierTypeSub table and every time a new SupplierType classification gets created I would have to populate the ctl_SupplierTypeToTypeSub table with that new SupplierType and the {None} TypeSub. (Again, my presumption but not 100% confident I'm right in this presumption)

If I don't use this Many-Many type table method but just simply have two fields within the Supplier Table 1 for SupplierType and then 1 for SupplierTypeSub Is there a way in the SQL of the SELECT statement for SupplierTypeSub that would limit the user to selecting a TypeSub based on what Management has defined as the way they want the SupplierType to SupplierTypeSub to relate?

So my question is this:
Based on what I am attempting, from your all's experience is there one method preferrable long-term over another. And is my thinking correct here at all?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros