Confused about relationships and tables

[MGJP]

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.

[Access_Blaster]

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.

[MGJP]

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.

[ssanfu]

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......

[MGJP]

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?