Results 1 to 4 of 4
  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142

    is auto number or date better for primary and foreign key?


    I have four tables right now. Associate list (name, department, shift), Associate daily information (date, clock in, clock out) , Associate task information (jobs, and related information), and Associate exception information (Daily problems dealt with).

    The associate list will not change that much and has an auto number. The other three also have auto number fields but I also have date. As I will be going in daily and putting in more information, should I have the auto numbers of each table be the primary and foreign key's or use date as the date will always be changing?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I would avoid using date as a primary key field.
    Although you state that you are only entering one record per day, at some point it may be necessary to add an extra record which won't be possible if its a PK.

    Also I think I'm right in saying it will be faster for Access to search for a specific record looking for a long integer than if its a date.
    Similarly, I have a large table of 2.6 million UK postcodes, each of which are a unique 7 or 8 character string.
    However for speed of searching, I added an autonumber PK field and indexed the postcode.
    It made a HUGE difference
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    other thing about dates is it is stored as a decimal number. If it includes a time element, you will struggle to get matches. in Access, they are 8 bytes in size, whereas an autonumber (i.e. a long datatype) is 4 bytes in size - so simplistically an index of longs will be twice as fast as an index of dates. With Colins postcodes, text is 10 bytes plus 1 for each character so a typical postcode of 7 characters will require 17 bytes - over four times slower than a long.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just for interest I ran a quick test on various fields in my 2.6 million records postcodes table

    In each case I timed how long it took using DMax to find the maximum value of a field.
    I used system time to measure time intervals in milliseconds and took an average of 5 results to reduce possible system timing errors

    a) Date field (not indexed) 13.533 s
    b) Long integer field (not indexed) 12.023 s

    As you can see, searching number fields is faster than date fields but the difference isn't massive

    However, the effect of indexing makes a huge difference to all fields
    c) Date field (indexed) 0.099 s
    d) Long integer field (indexed) 0.000 s - too small to measure
    e) Autonumber field (indexed) 0.000 s - too small to measure

    What this really confirms is the need to index fields commonly used for searching
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-30-2016, 05:09 PM
  2. Replies: 3
    Last Post: 03-10-2016, 12:53 PM
  3. Primary Key/Foreign Key question
    By JohnnyChimpo in forum Access
    Replies: 5
    Last Post: 10-03-2015, 11:20 AM
  4. Primary and Foreign Key Question.
    By Schae235 in forum Access
    Replies: 3
    Last Post: 04-08-2015, 07:03 AM
  5. Primary Key Auto Number not available in Excel for import
    By justphilip2003 in forum Import/Export Data
    Replies: 1
    Last Post: 04-30-2013, 07:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums