Results 1 to 6 of 6
  1. #1
    Boltman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    7

    Table and Lookup Questions

    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

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    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.

  3. #3
    rivereridanus is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    71
    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!

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    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.

  5. #5
    rivereridanus is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    71
    Quote Originally Posted by TheShabz View Post
    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.

  6. #6
    Boltman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    7

    category and sub category

    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Lookup Table
    By BigCat in forum Access
    Replies: 2
    Last Post: 05-19-2011, 09:53 AM
  2. Using lookup data for table 1 mutible times in table 2
    By mbjazz in forum Database Design
    Replies: 5
    Last Post: 04-26-2011, 01:18 AM
  3. Replies: 1
    Last Post: 06-01-2010, 12:19 PM
  4. VBA code - questions table onto forms
    By Tman in forum Programming
    Replies: 3
    Last Post: 04-26-2010, 05:47 AM
  5. Replies: 1
    Last Post: 04-08-2010, 02:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums