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-20T20:42:43+00:00

    My mistake, you are right. I wasn't opening it up in the main form.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-04-20T20:54:12+00:00

    It says I must enter a value in the 'CustColorPreference.CustomerID' field.

    Which means you have not selected a customer in the combo box.  As it stands you'll need to have entered at least one customer in the Customers table first, so you have something to select.  You'll also need to close the form and open it again after inserting the row(s) in Customers.   In a developed application you'd include a button in the form to add a new customer, as in my NotInList demo, but that functionality is not present in the form as yet.

    The way to learn how to do this sort of thing is not to copy by rote from a file like mine, or from some You-Tube presentation, but to follow Richard Feynman's advice in the quote below my signature.  Only when you understand the underlying methodology will you be able to transfer that understanding to similar tasks in other contexts.  I'd suggest you start with one of the basic tutorials such as Crystal's Access Basics to get a good grounding in how an Access database is put together.  My demos are illustrations, not tutorials, and assume a sufficient understanding of the methodologies to be able to transfer what I'm illustrating to other contexts.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-04-22T19:12:58+00:00

    I wish I had looked at that tutorial when it was mentioned earlier. I took the past couple of days to look at it in detail and relate it to my current Access database. I definitely feel like I have a stronger understanding now. I've started to normalize more of my data and it turns out, I have a lot more lookup tables than I thought. Is there a general rule of thumb or limit on lookup tables?

    I've also manged to sort out the issues I was having with the Subforms. I was able to replicate it with the other lookup tables I had in other forms.

    Was this answer helpful?

    0 comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-22T19:58:33+00:00

    No You create as many lookup tables as you need. Something it may not be worth it. For example, I would not create a lookup table gender. On the other hand I might create (actually, you can problem download one) for US States.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2015-04-22T21:51:51+00:00

    The number of tables is determined by the reality being modelled.  A table represents an entity type, so if an entity type exists in that part of the real world being modelled, then there would be a table for it in the database.  Where the values of an entity type are immutably fixed in the external world, then you don't need a table.  Months of the year or days of the week would be an example, in addition to the genders entity type which Scott mentioned.  Even so it can sometimes be advantageous to have a table for such things if it improves the efficiency of the database.

    Your reaction of finding you have more tables than expected is not untypical.  Inexperienced developers often tend to create fewer tables than required, but with experience we come to realise that many tall skinny tables are better than a few short fat ones.

    Was this answer helpful?

    0 comments