Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    23

    Unanswered: Foreign Key Field Shift-A-Roo!!

    In looking at the OrderEntry Template (from HiTechCoach’s site), I noticed something interesting: The Customer Id field in the Customer’s Table—it’s primary key-- changes when it becomes the foreign key in the Order’s Table.

    For example, the Customer ID in the Customer Table is an Autonumber. In the Orders Table, the Customer ID field—now the foreign key--lists the customer by name rather than with the same number used as the ID number in the Customer table.

    Customers
    Customer ID Company Name
    1 A. Datum Corporation
    2 Contoso, Ltd
    3 Trey Research
    4 Litware, Inc

    Orders
    Order ID Customer ID
    1 Contoso, Ltd
    2 A. Datum Corporation
    3 Trey Research
    4 Contoso, Ltd
    5 Litware, Inc
    13 A. Datum Corporation
    14 Contoso, Ltd

    Being new to Access, I was wondering if this is common practice. Or is the norm to have the foreign key remain the same as it was while it was a primary key in the original table?

    It certainly makes it easier to distinguish the customer as you move from table to table.

    Are there any negatives to using this method of foreign key assignment?

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by rlb2252 View Post
    In looking at the OrderEntry Template (from HiTechCoach’s site), I noticed something interesting: The Customer Id field in the Customer’s Table—it’s primary key-- changes when it becomes the foreign key in the Order’s Table.

    For example, the Customer ID in the Customer Table is an Autonumber. In the Orders Table, the Customer ID field—now the foreign key--lists the customer by name rather than with the same number used as the ID number in the Customer table.

    Customers
    Customer ID Company Name
    1 A. Datum Corporation
    2 Contoso, Ltd
    3 Trey Research
    4 Litware, Inc

    Orders
    Order ID Customer ID
    1 Contoso, Ltd
    2 A. Datum Corporation
    3 Trey Research
    4 Contoso, Ltd
    5 Litware, Inc
    13 A. Datum Corporation
    14 Contoso, Ltd

    Being new to Access, I was wondering if this is common practice. Or is the norm to have the foreign key remain the same as it was while it was a primary key in the original table?

    It certainly makes it easier to distinguish the customer as you move from table to table.

    Are there any negatives to using this method of foreign key assignment?
    That is based on Microsoft's template.

    It is storing the primary key as a foreign key. You just can not see it because the field in the table has a Lookup defined. If you look at the actual design view, you will see that the date type does not match what you are seeing.

    So what you are seeing in the table when you view the data is not what is actually stored.

    See:
    The Evils of Lookup Fields in Tables
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •