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 No comments

55 answers

Sort by: Newest
  1. Anonymous
    2015-04-17T22:17:55+00:00

    I understand that relationship table and I realized I had an extra CustomerID relationship in the OrderDetails when I didn't need it.  I've created the separate Items table as suggested and believe I have all the relationships set up (image below).

    I'm trying to work on the forms and subforms now.  Hopefully I can figure some of it out tonight.

    Was this answer helpful?

    0 comments No comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-17T14:18:57+00:00

    So longs as the OP realises that the Customers table will not be normalized to Third Normal Form until this is done, and consequently open to the risk of update anomalies.

    I agree, but I think it more important to get the major issues understood and working in this instance before tackling that issue.

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2015-04-17T12:58:58+00:00

    ...............I would hold off on the separate City and state tables for now.

    So longs as the OP realises that the Customers table will not be normalized to Third Normal Form until this is done, and consequently open to the risk of update anomalies.

    I agree with you about navigation forms; they're like 90% of fishing tackle - designed to catch fishermen, not fish.

    Was this answer helpful?

    0 comments No comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-17T11:56:24+00:00

    OK, The Relationships window that Ken posted is what you should have, however, I would hold off on the separate City and state tables for now. Concentrate on getting the Customer and Order tables set properly. 

    I'm not a big fan of the Navigation Form. I would just use regular forms with a menu form for now. 

    You need to work with subforms more. The Order Details form should be created as a tabular form and embedded on the Orders form using the subform wizard. 

    You should also be creating a tabular form for the color preferences to use as an embedded subform on the Customers form. The only Visible control on that form will be a combobox to select a color. Comboboxes on forms work exactly the same as the lookup field the only difference is where they are placed.

    Was this answer helpful?

    0 comments No comments
  5. Anonymous
    2015-04-17T10:59:07+00:00

    Is it like the Access DB I have in the link below?

    No, the model should, broadly speaking, be like this:

    Was this answer helpful?

    0 comments No comments