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-19T05:15:38+00:00

    I'm having some trouble with the subform and how to create it.  If anyone could help me with that I would appreciate it.  I used the Form wizard and created it tabular as you said, but am not sure what else I should do.  I have attached the Access file below if anyone could help me get it set up on there.  This is just a small scale and cut down version so once I get it down I can then expand it.

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-04-19T12:13:01+00:00

    I've uploaded an amended version of your file as InfernalRage_2.accdb to my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    I've amended the customer combo box in the Orders form so that it lists customers by name, and  I've amended the item combo box in the subform so that it lists the items by name and shows the value of the PantsFit column in a second column in its list.  When an item is selected the PantsFit value is shown in a read-only text box in the subform.

    One thing which puzzles me, though, is why you have a purchase date in the parent form, and an order date in each row of the subform.  Normally only the Orders table, and therefore the parent form, has an order date.

    For how to (a) automatically insert a new customer by opening a separate customers form in dialogue mode from a button in the orders form, and (b) how to insert a new item similarly by opening a separate items form in dialogue mode by typing the new item name into the combo box in the subform, see the NotInList demo file in my same OneDrive folder.

    Was this answer helpful?

    0 comments
  3. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-19T13:10:45+00:00

    Just to add here, you embed a subform using the subform wizard. The wizard will walk you through selecting the form you want to embed and selecting the linking fields.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-04-19T15:01:12+00:00

    I did not know all of that was possible. That looks much better and fluid now. The purchase date and order date labels were mixed up, they should be swapped.

    The NotInList demo is very helpful and shows me the possibilities of what to do in my database.

    One more question I have from that file you sent, how would I set up a form for the CustomerProfile table to allow multiple Color inputs?

    @Scottgem

    I found the Subform wizard now, thanks. I did not realize it was in Design View. I will have to try it out now though to see if I can set one up properly.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2015-04-19T19:47:26+00:00

    You don't need the CustomerProfile table at all; you can relate the Customers table directly to the CustColorPreference table.

    To select the colour preferences per customer you can use an unbound parent form in which the customer is selected, and a bound subform to select the colours.  The LinkMasterFields property of the subform control is in this case set to the name of the unbound combo box in the parent form, rather than to the CustomerID column name.

    I've updated the  InfernalRage_2.accdb file in my OneDrive folder to include such a form/subform, and have amended the relationships to exclude the redundant CustomerProfile table.

    Was this answer helpful?

    0 comments