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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2015-04-09T14:16:43+00:00

    Okay, sorry for the long delay.

    I've created a basic structure for the database with the tables and I believe I have set up the correct relationships.  I have more columns to add and a few column names to change, but I just wanted to make sure I was doing it right before I moved on.

    If anyone could look at the attached file and let me know any mistakes or feedback you have, I would greatly appreciate it.

    The Customers table will generate the AutoNumber which will be the CustomerID.

    In the relationships, I linked the CustomerID to the other 2 tables: OrderForm and OrderContent.

    OrderForm generates an AutoNumber which will be the OrderID and that links to OrderContent.

    I will probably create just field using the first letter of the first and last name plus the customerID with some padded 0's for invoice purposes.  If there is a better way, I am definitely open to suggestions.

    http://s000.tinyupload.com/index.php?file_id=43489337265303321851

    Was this answer helpful?

    0 comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-07T20:17:59+00:00

    The basis of an efficient databases is your table design. So once you have that setup, pass it by us and let us offer suggestions.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-04-07T19:01:34+00:00

    A really good eBook for beginners can be downloaded from my web site That'll Do IT It's called Access Basics written by Crystal. She does a great job explaining relationships. It's only 100 pages but is full of what you should know about Access.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-04-07T18:47:31+00:00

    Thank for all the replies.

    I am going to try to set the database up with the suggestions made in this thread and I will see how far I can get.  I will try with a smaller version and see if I can get the Primary Key and Foreign Key set up properly.

    If you have any more examples I could look at or possibly walk me through how to set up the database I would greatly appreciate it.  

    I'm sure I will need help with the queries and forms as well.

    Thanks again for taking the time to help me.

    Was this answer helpful?

    0 comments
  5. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-07T17:51:18+00:00

    You need the CustomerID to remain INCREMENTAL or SEQUENTIAL? Those are two different things. You can seed the table when importing your data so that the next Autonumber will be incremental. Check out this article: http://allenbrowne.com/ser-26.html

    But Sequential means something different it means that the numbers are in a sequence with no gaps in the sequence (for example my company assigns Employee badge #s in a sequence of 5 (3005, 3010, 3015) etc. I use the technique described on my blog to pull the next sequential number. But internally I use an autonumber as my PK field. 

    The sequence doesn't really matter for any purpose.

    Exactly! That's the point I was trying to make. As long as the CustomerID is unique, there is really no value in making it sequential. And, therefore, an Autonumber suits your purpose fine.

    *"*How do I use the AutoNumber from the Customer Contact Information with the Order Form and Order Content then?"

    A Primary key and a Foreign Key are different. The Primary key uniquely identifies the parent record, but a foreign key identifies the parent record the child record relates to. So you have a CustomerID field in your Order table to identify the customer making the Order. But this is not a unique value as customers can (hopefully) make multiple orders. So each order record would have a foreign key indicating the customer making the order.

    "other than the fact that is what it is like now in the spreadsheet. "

    Don't make that mistake. Excel is a great spreadsheet. Highly suited for analyzing data and creating calculations and what ifs. But its not a database, especially not a relational database. Don't try to duplicate the spreadsheet structure. You need to normalize your data first! Then you can import the data fromt he spreadsheet and place it in normalized tables.

    Was this answer helpful?

    0 comments