Setting up Customer ID as Primary Key

@Scottgem

Sorry, I don't quite understand what you are saying there.  Would that mean I would have to have many separate tables then if I wanted to limit the input?

@Ken Sheridan

Okay, that makes sense.  I will have to develop that table.

I have attached a small sample of the database to this post.

Here is my goal:

1. User opens up a Navigation Form
2. User Selects "Customer" tab
3. User Inputs Customer information
4. User Selects "Customer Profile" tab
5. User Inputs "Customer Profile" information

    a) Some of these inputs will be restricted to a list

    b) Some of the fields will have multiple inputs and be restricted to a list

That is what I am working on currently.  I will tackle the orders part after this.

The reason I want to limit it to a certain list is for easier data entry ultimately and reduce the chance of a mistake.  Some fields are ranges so the field type is text.  Do you have any recommendation on how ot handle that in the tables?  The Customer picks their weight based on a list of ranges such as "150-160", "161-170", etc.  

Questions I have then about my current DB:

1. How do I store multiple entries via form into the tables?

    a) A person may like multiple colors and I want to store all of that

    b) A person may like multiple patterns and I want to store all of that

2. How do I limit inputs into the form with a list?

    a) Certain fields need to be restricted to just a predetermined list

    b) Certain fields have "Other" option that the customer can select and input an option we do not 

         have (Ex. We don't have a certain color listed, so user manually inputs it into the survey they fill 

         out) The user should then be able to manually input a color in as well along with the list of colors

I hope all my questions make sense, if not, I can clarify any confusion.

I greatly appreciate all the help so far!

http://s000.tinyupload.com/?file_id=08094766332629395400

@Scottgem

Sorry, I don't quite understand what you are saying there.  Would that mean I would have to have many separate tables then if I wanted to limit the input?

Questions I have then about my current DB:

1. How do I store multiple entries via form into the tables?

    a) A person may like multiple colors and I want to store all of that

    b) A person may like multiple patterns and I want to store all of that

2. How do I limit inputs into the form with a list?

    a) Certain fields need to be restricted to just a predetermined list

    b) Certain fields have "Other" option that the customer can select and input an option we do not 

         have (Ex. We don't have a certain color listed, so user manually inputs it into the survey they fill 

         out) The user should then be able to manually input a color in as well along with the list of colors

I hope all my questions make sense, if not, I can clarify any confusion.

I greatly appreciate all the help so far!

http://s000.tinyupload.com/?file_id=08094766332629395400

OK, There are two ways to deal with lookup tables. Either you use one master or several smaller individual tables, one for each lookup. If you want to go with the master then you need to use the structure that I outlined. NOT a separate column for each set of values.

What you describe is very much SOP. And yes you should use lookups to standardize input and insure consistency. For the weight example you gave you have a lookup table like this:

WeightID  WeightRange

1                  151-160

2                  161-170

etc. If you want a Master lookup then it would be:

LookupID   LookupValue LookupTypeID

1                 151-160              1

2                 161-170             1

where LookupTypeID 1 is Weight Range.

As to your other questions:

1. Not sure I understand this. When you generate a form there is usually a Navigation bar at the bottom. That bar would have a New Record button on it. Use the Form wizard to create your forms. The forms will be bound to a Recordsource which is your table. Access will handle the I/O between the form and the table.

a & b. Then you will need a child table to model the many to many relationship (a person may like multiple colors and a color will apply to multiple people). For example:

tjxCustomerColorPreference

CustColorPrefID (PK Autonumber)

CustomerID (FK)

ColorID (FK)

You would want to set the combination of Customer and ColorIDs to be a unique index. You would to the same thing with patterns. The customer preferences will be entered by a Subform on the main customer form and linked on CustomerID.

2. This is where you use List controls (combos, lists, option groups). If you need to allow for adding new items to the list, you use the NotInList event to manage adding items.

That makes much more sense now, thanks.  

Is there any example or could you set up a quick example of something like multiple color inputs being tracked for a single customer within Access?  Is there any example I could download to see for myself?  Is it something like the table below:

I still don't quite understand how to set all of that up along with subforms for allowing multiple inputs/entries for a single customer.  I saw how to do it using the LookUpWizard on the table, but not sure the functionality on the form.

What you have here is a binary (2-way) many-to-many relationship type between customers and colours.  It is this relationship type which your table is modelling.  The most common interface for this type of thing would be to have a Customers form, in single form view, and within it a subform based on the tjxCustomerColorPreference table.  The subform would be linked to the parent form by setting the LinkMasterFields and LinkChildFields properties of the subform control in the parent form to CustomerID.

In the subform there would be just one control, a combo box bound to the ColorID column, set up as follows:

ControlSource:   ColorID

RowSource:     SELECT ColorID, Color FROM Color ORDER BY Colors;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

where Colors is the name of the referenced table, ColorID is its primary key, and Color is a text column in the table.  If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

You can then assign as many colours to the current customer as necessary by inserting a row for each in the subform, selecting a different colour in the combo box in each case.

You'll find an example of this sort of basic many-to-many relationship type and how to represent it in a form as StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates three means of interfacing with the data, but I'd recommend the conventional form/subform approach, which is easy to implement and code-free.

Is it like the Access DB I have in the link below?  I am not entirely sure if I got it correct.  I also wasn't having any luck setting up the subform for the multiple options.

http://s000.tinyupload.com/?file_id=00654915987870711570