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-09T19:02:24+00:00

    I agree with what John and Ken said. Comboboxes and subforms are very important tools in the Access toolbox. Foreign keys are most often populated using these tools.

    As far as the product table is concerned, do you sell the same things multiple times?  If so, then you should use a Products table.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2015-04-09T17:10:30+00:00

    All these fields must still be manually inputted correct, except for the AutoNumbers, even the FKs? 

    You don't have to insert the foreign key value at all.  In fact you never need know what the values of the keys are.  To insert a new order record you would use a combo box in a form based on the orders table and select the customer by name.  The combo box would be bound to the foreign key column, but would show the customer's name by hiding the bound column, which is done by setting the combo box's ColumnWidths property to zero.

    In the orders form would be a subform for inserting rows into the OrderDetails table, but as Scott pointed out, you need to redesign this table by removing the CustomerID and Item columns, and adding a ProductID column or similar to reference the primary key of a Products table.  The current price should be looked up and inserted from the Products table.

    You'll find an example of this sort of set-up as InvoicePDF.zip in 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.

    This little demo file is primarily intended to illustrate how to output a report as a PDF file, but  it is based on a similar model to yours, albeit for invoices rather than orders.  The main invoice form includes a combo box to select a customer and a subform for inserting the invoice details.  It would be exactly the same for orders.  Ignore the other subforms for the moment.

    Note how (a) when a customer is selected their address data automatically shows in read-only controls bound to columns in referenced tables in the form's underlying query, and (b) in the invoice details subform the current unit price and tax rate of the selected product are automatically inserted by means of code in the ProductID combo box's AfterUpdate event procedure.

    Note also how typing a new customer name into the customer combo box allows you to insert the new customer record via a dialogue form opened by code in the combo box's NotInList event procedure.

    Before you embark on designing the form's however, you must first get the table designs right.

    PS:  You definitely do need a Products table or similar.  An Item column in the OrderDetails table, without referencing a table, is an open invitataion to update anomalies.  In database terms this table models a many-to-many relationship type between orders and products (items).

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2015-04-09T16:59:18+00:00

    Let the computer do more of the work for you. That's what the program is FOR!

    The user should never even SEE an autonumber value, much less need to type it in. The CustomerID autonumber would be created by adding a record to the table in the Customers form (where you would also enter the customer's name, contact information, and so on); on the Customers form you would have a SUBFORM, linked by CustomerID as the master/child link field, linking the autonumber on the main form to the foreign key in the subform. Neither need be displayed to the user. 

    This would be a general approach - a Form for each "parent" table (Orders, Customers, etc.) and Subforms for each related "child" table. You will also often use a Combo Box control to populate a numeric foreign key; the combo will have two columns, the concealed numeric ID for the computer, and the visible human-meaningful name for the user.

    Do study the links that Scott has posted. It takes some study time - this isn't trivial! - but it's all described in the links and many other places on the web.

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2015-04-09T15:32:55+00:00

    Content wise, I am still missing a lot since I just wanted to get the basics set up before I add in some more columns.  I don't believe we need a product table yet due to our business model and not having a set inventory.  All the OrderDetails will have to be manually inputted into the database for now.  The OrderForm is the Order Table and the Order Details is the OrderContent table.  I changed the labeling in the picture below.

    The relationships I have set up in the database are correct though?  All these fields must still be manually inputted correct, except for the AutoNumbers, even the FKs? 

    For example, let's say I have a new customer being put in the database and his CustomerID is 10.

    Next, I would begin filling out the Orders Table.  Would I manually put in 10 into the CustomerID field there and it would relate it together?  My ultimate goal is to be able to easily pull a report based on various things.  One of them would be if I wanted to see all of a certain customers purchase history.  I could then just filter by CustomerID 10 and see all of his Orders.  I imagine I can put a form on top that automatically goes through a customer creation and order creation process?

    Then, the Orders table would generate an AutoNumber which would in turn be used as the OrderID for the OrderDetails table.  Again, same question as the manually filling in for the CustomerID. 

    Picture of relationships: http://infernalrage.com/i/2G4g8.png

    Access Database: http://s000.tinyupload.com/index.php?file_id=89222181573186150385

    Was this answer helpful?

    0 comments No comments
  5. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-04-09T15:01:35+00:00

    The Customer table is fine. But your order tables are wrong.

    You should have an Order table and an Order Details table. You will also, probably, need a Product table.

    tblProduct

    ProductID (PK Autonumber)

    ProductName

    UnitPrice

    tblOrder

    OrderID (PK Autonumber)

    OrderDate

    CustomerID (FK)

    tblOrderDetails

    OrderDetaiID (PK Autonumber)

    OrderID (FK)

    ProductID (FK)

    Quantity

    UnitCost

    This is a simple and standard order entry setup. You may need more depending on your particular needs.

    Was this answer helpful?

    0 comments No comments