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. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-20T20:39:57+00:00

    Are you trying to enter data in the subform or are you opening the subform outside the main form. The message indicates the latter, because, if you are using it as a subform, the links would automatically input the CustomerID. And I can't imagine Ken forgetting to set up the links.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2015-04-20T20:15:03+00:00

    I am looking at it now. It says I must enter a value in the 'CustColorPreference.CustomerID' field.

    Is there a good video tutorial on any of this that you might have? I'm trying to replicate this for other options and multi-input fields.

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2015-04-20T17:39:53+00:00

    Have you taken a look at the frmCustomerProfile form in the amended version of your file in my OneDrive folder?  In that you can select a customer and then insert as many rows as necessary in the subform, one for each colour.  The primary key of the CustColorPreference table on which the subform is based is a composite one of the two foreign keys.

    Was this answer helpful?

    0 comments No comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-20T16:38:35+00:00

    The combination of CustomerID and ColorID should be set as a unique index. Looks like you are entering directly in the Color preferences form, not using it as a subform.

    Was this answer helpful?

    0 comments No comments
  5. Anonymous
    2015-04-20T15:38:34+00:00

    I am having some trouble getting multiple colors to save for one customer. It says that I can't make duplicate entries, but none of the fields are primary keys in the CustColorPreference table.

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

    Was this answer helpful?

    0 comments No comments