Setting up Customer ID as Primary Key

Anonymous
2015-04-07T14:28:40+00:00

Hello everyone,

First of all, I am very new to Access and this is my first time working in it.  I currently have a database in Excel that I would like to implement in Access. 

I am trying to create a customer database that consists of 4 different tables:

  1. Customer Contact Information
  2. Customer Profile Form
  3. Customer Order Form
  4. Customer Order Content

I want my primary key, I believe that is the correct term, to be the Customer ID.  I would like this to be a calculated field in the "Customer Contact Information" table and it should be manually inputted into the other tables.  The "Customer Contact Information" table and the "Customer Profile Form" should have the same exact number of rows since each customer will have only 1 profile.  If possible, I would like the Customer ID field to just copy over to the "Customer Profile Form".  

The "Customer Order Form" and "Customer Order Content" will have varying rows since each customer could place multiple orders or have multiple items per order.  I would like a unique Order Number to be generated in the "Customer Order Form" and the Customer ID, which would be manually inputted, would still be the primary key that ties it back to the "Customer Contact Information" table.  Then, in the "Customer Order Content" I would like the Customer ID and Order Number to be manually inputted as well.  The Order Number would l link back to the "Customer Order Form" and the Customer ID would still be the primary key.  

My goal is to create a form on top of all of this so when customer data needs to be inputted, Customer ID is simply automatically generated and pastes to the appropriate fields in the other tables.  The user will ultimately be able to click "New Customer" and then begin inputting all the fields for the "Customer Contact Information", "Customer Profile Form", and "Customer Order Form".  Then, the user will have to come back later to input the "Customer Order Content" since everything is purchased based on their preferences.  This is where it gets confusing for me since I have to keep all the tables related based on Customer ID,  but 2 of the tables will have different numbers of rows.

The problem with the current system is that it gets difficult to share the database and input the data with a few users since there are so many columns and no real interface.

Any help in setting up this database in Access would be greatly appreciated or if anyone has any other suggestions. 

Thank you for taking the time to read this.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

55 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2015-04-16T06:06:11+00:00

    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

    Was this answer helpful?

    0 comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-16T11:45:33+00:00

    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.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-04-16T14:54:05+00:00
    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.

     

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

    Was this answer helpful?

    0 comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-16T15:29:46+00:00

    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.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2015-04-16T16:46:00+00:00

    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.

    Was this answer helpful?

    0 comments