Custom Software
.
 The Access Wizard Newsletter . Tips, Tricks and Traps for Access Users and Developers 
June 2004 
.
. . . . . . . . .
Dear Jim,

We use keys everyday. We have keys for our house, keys for our cars, keys for our office, some even say there's a key to our hearts (my wife Diane insisted I include that). In a similar manner each table in a database should have a key. Access calls this particular item a primary key.

This primary key is a building block for any Access database.

In This Issue
.
.
 
  • Tip of the Month
  • Keys are Critical
  • Characteristics of Primary Keys
  • Primary Key Recommendation
  • Trap of the Month

     
  • Keys are Critical
    .
    Primary Keys are critical to the smooth operation of a database. If you use them correctly they can make your life easier. If you make mistakes with them, or omit them, they'll come back to haunt you time and again.

    The short and skinny (or the Key to Keys) -- Use Autonumbers as the Primary Key in every table and you'll avoid all of the common problems.

    What is a Primary Key? The primary key is a unique identifier for each row in a table. It's the way that you (and Access) will join related tables. When you need to research an item, the primary key will give you an easy way to get to a record.

     

    Characteristics of Primary Keys
    .
    1. Primary Keys must be unique
    This is a good reason not to try to use something like initials, or social security number or some combination of fields. Something which appears to be unique e.g., a Social Security Number (SSN) really isn't. One project I inherited used SSN as the primary key. Unfortunately the database had problems when different employees had the same SSN. We suspected that the duplicate numbers were either typos or the result of identity theft.

    2. Primary Keys must never (yes that means never) change
    Don't use something like a natural ID, e.g. an employee number. If your company is bought by another company it's likely that employees will get new IDs and you'll have a problem.

    3. Primary Keys should be meaningless
    If the Primary Key means something to the user, at some point s/he will want to change it. For instance, I did a project for the State of New Hampshire and had to convert their identifiers to a new system. Their IDs indicated where in the state the person was located. Unfortunately people move around from time to time. When they went to a new part of the state, their identifiers would change, and all their past information was lost.

    4. Primary Keys should be a number
    Numbers are faster. The reason for this is that they take up less memory and sorting numbers (especially integers) is faster than sorting letters

     

    Primary Key Recommendation
    .
    Use an Autonumber as the Primary Key. Luckily, Access makes this simple. When you create a table and select the field type, one of the options available is Autonumber. It's a sequential number and clicks up 1 by 1 as you add new records to the table. Don't worry that the Autonumber appears to have gaps (that's caused by deleted records). You don't need it to indicate the number of records in the table; if you need to know how many records are in the table, simply open it up and look at the bottom left hand corner. What you see there is the total number of records.

    You'll also want to have an Autonumber primary key for every table. Autonumbers as Primary Keys are simple to include, don't cost much in terms of space or memory, and if you ever get to a point where you need the key, it will already be there.

     

    Trap of the Month
    .
    Avoid multiple Primary Keys. You might be tempted to create a key by using two or more fields, e.g., Last Name and Street Address. Although this may work in the short run, over time you'll regret the decision. Every time you need to join two tables in a query you'll need to link on both fields. It's a practice that invites problems. Query results will not be what you expect, and you'll waste time trying to find out why. Use an Autonumber as a primary key and you won't go wrong.
     

    Tip of the Month
    To see some samples of different types of queries, forms and reports you can go to the Microsoft link below.

    Be aware that most developers (including me) think that Microsoft makes some significant errors in their database examples.

    Even given this shortcoming, the link below will provide some ideas on how to approach different problems.

    Sample Access databases that you can download and adapt

    .
    .
    .
    .
    . Quick Links...

    Our Services

    Selected Presentations from Jim Connell of Custom Software

    Future Article Suggestions, Questions and Comments

    Past Newsletters

    .
    .
    .


    Join our mailing list!
    .

         email: jim@custom-software.biz
         voice: 978-392-3462
         web: http://www.custom-software.biz

    .
    .
    Custom Software - Westford MA 01886

     
    Forward email

    SafeUnsubscribe(TM)
    This email was sent to jim@custom-software.biz, by Custom Software.
    Update your profile |Instant removal with SafeUnsubscribe™ | Privacy Policy.
    Powered by
    Constant Contact