Table and Lookup Questions

[Boltman]

Good morning. I am new to access. I am attempting to build a database to help with my business and have run into the first of probably several problems and hopefully someone here can help. My questions are as follows. I have built tables for my part numbers and want to assign categories and sub categories to each part. Should each category have its own table or should I group them into one table? Likewise should each sub category have its own table? (All sub categories are unique to each specific categories). I will be using the categories and sub categories for looking up part numbers and reporting.



After I conquer that, when part numbers are being entered for the first time, I want to have a lookup menu for the category. After the category is assigned, I want a lookup for only the sub categories that are applicable to the specific categories.

I appreciate any and all help.

Thanks in advance

Boltman

[TheShabz]

For the table structure, you want 3 tables. One for the parts, one for the categories, and one for the subcategories. The relationship of those tables depend on whether a part can have many categories and/or subcategories.

For the lookup, you'll probably want to use cascading combo boxes.

[rivereridanus]

Hi Boltman,

I would not separate your parts by category in different tables (this is a very excel-like thing to do, but doesn't work well in access). What you can do is add two more fields to your main parts table (one for category and one for subcategory), and keep track of that within your single large table for all parts.

I would make two more tables called "category" and "subcategory", and have the name of the category/ subcategory be the only field in each table. This is simply for easy lookup using combo boxes and stuff later on. Then make a third table called "categorymatch" (or something), that lists all the categories with all the possible subcategories you will allow in separate fields. (So for an example of household items you might have the following:
kitchen, cooking things
kitchen, cleaning things
kitchen, food stuffs
bathroom, cleaning things
bathroom, shower things
etc...

This third table will allow you to lookup for only the sub categories that are applicable to the specific categories. Come back later for that bit when you have more specifics. Good luck!

[TheShabz]

River, that's assuming each part can only have one category or subcategory, which is why I asked. If a part can be in multiple categories and subcategories, then you need 2 additional tables to facilitate the many to many relationships.

[rivereridanus]

River, that's assuming each part can only have one category or subcategory, which is why I asked. If a part can be in multiple categories and subcategories, then you need 2 additional tables to facilitate the many to many relationships.

You're right about that. I has assumed otherwise.

[Boltman]

I appreciate all your response. Each part will have a specific category and a specific subcategory. There will never be an instance where that will vary. I will proceed and let you know of my progress.

Many Thanks
Boltman