Results 1 to 5 of 5
  1. #1
    MGJP is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    3

    Confused about relationships and tables

    I need to handle information collected on an Acrobat questionnaire with results that downloaded to an Excel spreadsheet. The spreadsheet proved to be unwieldy; collating the information so that I can merge it to Word will be a much bigger job than I had anticipated. I have started to learn Access because it might be an easier way to handle the data. I want to know whether in fact Access is a better way to go. If it is, how I should approach it?



    I am dealing with a niche market that is served by about 300 companies. I am creating a directory that will have, for each company, standard ID information plus information in about 8 categories. In a few categories, companies can choose only one of a handful of choices. In the other categories, each company can choose any or all of the possible choices. The smallest category has about 6 possible choices and the largest has 40.

    At first I thought that each category would be a separate table, but the more I read (and watch learning videos) the more confused I get. I can see making a list of choices in a table, but I don’t understand how that would be related to the companies.

  2. #2
    Join Date
    May 2010
    Posts
    339
    Each table should hold data about a single event. Customer table has customer information. Order table has order information.
    Take a look a the Northwind sample loaded with most versions of access.

  3. #3
    MGJP is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    3
    I understand your answer, but it doesn't appear to apply to my data. I don't have customers and orders. I have individual companies that have eight factors (one with up to 40 possible characteristics) that I want to be able to compare.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    At first I thought that each category would be a separate table, but the more I read (and watch learning videos) the more confused I get. I can see making a list of choices in a table, but I don’t understand how that would be related to the companies.
    Put the company info into a company table.
    Put the categories in a category. You don't have to use "Category 1", "Category 2", "Category 3". You could use "Fruit", "Car", "Color".

    Each company can have many Categories.
    Each Category can be selected by many companies.

    Use a junction table to relate the two tables (many to many relationship)

    See attached A2Kmdb.

    Maybe this will help......

  5. #5
    MGJP is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    3

    Thank you

    Thank you for taking the time to really understand my question and to make up a little database example for me. I have a much better idea of how to proceed.

    My next question is whether I should have one category table with all the variables or whether it makes more sense to have a few category tables, each dealing with a different type of information. In your sample Category table, each category was a different kind of product, with the individual products listed. Would I make that a Product table, and also have a SellingToCo table with specifications for how to approach the company on the supply end (one purpose of the directory being to make it easier for manufacturers of things for this niche market to find outlets)? These would all be many-to-many tables. In this case I would need to expand the company-to-company table, heading for the new column being SellingToCo_FK to the right of lngCategory_FK? Or is this making things too complicated?

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

Similar Threads

  1. Many to Many Relationships among 8 tables
    By Pilotwings_64 in forum Database Design
    Replies: 9
    Last Post: 10-30-2010, 03:12 AM
  2. Tables Vs Relationships
    By Bruzer in forum Database Design
    Replies: 8
    Last Post: 09-06-2009, 04:39 PM
  3. Help on Tables/relationships?
    By mistaken_myst in forum Database Design
    Replies: 3
    Last Post: 04-01-2009, 05:16 PM
  4. Relationships btw tables
    By metaDM in forum Queries
    Replies: 0
    Last Post: 03-05-2009, 12:15 PM
  5. I need help on relationships for tables.
    By justin.w in forum Access
    Replies: 0
    Last Post: 10-16-2006, 10:57 AM

Tags for this Thread

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