Setting up Customer ID as Primary Key

What is the benefit or advantage of putting each item into an Inventory or Product table prior to putting it into the order table?  I guess I just don't see the difference between putting it directly into the "OrderDetails" table versus putting it in a separate "ProductTable" with JUST the properties of that item and then linking it to the "OrderDetails" page.

In a word, integrity.  If there is no referenced row in a Products table to which the OrderDetails table is related in an enforced relationship, then  the integrity of the database is prejudiced because there is nothing to stop the same product being inserted into OrderDetails differently in two or more separate rows.  The possibility of any meaningful analysis of the sales data is then lost.

I have personal experience of 'update anomalies' of this sort, having found three versions of my own name in one database as author of technical articles in my own field of work.  As far as the database is concerned I'm three separate people.  If the database had included a referenced Authors table, with each author identified by a distinct AuthorID key this would not have happened.

This is just a fundamental principle of the database relational model, which is a formal model in which these 'rules' exist for very sound reasons.

Ok, First, your master table is still thinking spreadsheet. If you want to use a Master table like that, it should be like this:

tasterLookup

LookupID (PK autonumber)

LookupValue

LookupTypeID (FK)

And then use:

tluLookupType

LookupTypeID (PK Autonumber)

LookupType

With this structure, you would then filter the RowSource of your Comboboxes, etc, by the LookupTypeID, you want to display. Like I said, I've used this type of methodology in some of my apps, but found it too cumbersome and went back to individual lookup tables.

On the Product table. I'm really having a hard time understanding your business model. What happens when a customer calls you? What do they ask for? 

But the reason I think using a Product table is the way to go is to reduce redundancy. Product is ordered, the next time the same product is ordered you already have it on record. You also aren't repeating information in the order record that should go elsewhere. I also think your accounting reports will be easier to do this way. it is basically the standard way companies do business. Every order entry  app I've seen does it that way.

1. Okay, that makes much more sense now.  I will use the ComboBox and Lists on the Forms instead.
2. The table looks something like this where Column 1 is the AutoNumber, Column 2 is a list of 3 values, Column 3 is a continuing list of about 12 or so values, and Column 4 is a list of 19 values.  I want to limit the input into a certain column in my main Table to be limited to these various Columns I have.  So someone should only be able to input 5, 6, or 7 in a field.  You are saying it is better to put all of these columns into their own table for lookup purposes?  I would like a drop down box with those options only in them.

3. What is the benefit or advantage of putting each item into an Inventory or Product table prior to putting it into the order table?  I guess I just don't see the difference between putting it directly into the "OrderDetails" table versus putting it in a separate "ProductTable" with JUST the properties of that item and then linking it to the "OrderDetails" page.

Thanks!

First, I would not use lookup fields on the table level. Lookups should be done on forms using list controls, not at the table level. See http://access.mvps.org/access/lookupfields.htm

Second. Can you you explain more about this Master Lookup table? I'm not sure I understand the bit about 1 column have 5 rows and another 10 rows. I've used both a master and separate lookup tables and I've found using separate ones usually works better. They are also easier to maintain. You should be using an Autonumber key for your lookup tables. So that your foreign key can be related back.

Remember the RowSource of a combo or list box is a query (A SQL statement). You can filter the SQL to return whatever records you need. 

Finally, on the issue of Inventory. You aren't looking at this from the right angle. My company is similar in that we manufacture parts to specification. So everything is a custom order. But whenever we accept an order for a part, that part and its details are entered into out master parts table first. And then the part number is entered into the order. 

I'm also not following this: "We purchase based on needs and our purchases are different every time since we sell clothing. ". If you sell clothing what do customers order? Don't they order 100 shirts? You may have to go out an order those shirts from a manufacturer, but you must have something of a product line!  My point is that anything you order should go into your products table. A product table (like I suggested earlier)  is not, necessarily, an inventory. Instead its a list of what you sell or what customer will order. Even if you have only sold an item once to one customer, it should be first entered into a Products table, then linked to in your Order.

Thanks for the help everyone.  I've made progress and managed to set up all th ekeys and relationships.

The thing about the company is we don't really have any inventory right now.  We purchase based on needs and our purchases are different every time since we sell clothing.  All the order details are going into OrderDetails table.  So I would have to currently input all the details of the product manually every time.  That is just the nature of the business at the moment.  We do plan to get inventory down the line though.

I have multiple fields that need to be restricted to certain inputs, so I was using the Lookup Wizard.  I made one Table that includes the options in different columns.  For example, in the Lookup Table Column 1 has 5 rows, but Column 2 has 10 rows.  Now, when I use the Lookup Wizard to select the correct column, the drop down box shows 10 rows, even if there are only 5 options.

Is there a way to restrict the number of rows it shows or would I need to break down the master Lookup Table I have into smaller tables?

Picture for reference, you can see all the empty rows and vertical scroll bar length