Results 1 to 5 of 5
  1. #1
    pattrickcolin is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44

    Question Unique Records, Is this the best method?

    I have a large database of World Postage Stamps.(600,000 records)
    The different Varieties are in a table (called ‘varieties’)
    This ‘Varieties’ table contains 3 fields (Plus other fields)
    CountryID is the first numeric field that contains many different countries


    TypeID is the second numeric field representing General Issue, Airmail, Postage Due, etc.
    CatalougeID is the third field that needs to be unique under Countryid and typeID

    Currently the three fields are all set up as Primary keys within the Varieties table. This avoids duplicates.
    Is this the best way forward, as I want to allow other people to edit this table?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02......

    I always have an Autonumber type field as my PK field in tables (well, 99.99% of the time ).

    Read this site,
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    then pay attention to the last header:
    Don't use Primary Keys to Prevent Duplicate Records

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Currently the three fields are all set up as Primary keys within the Varieties table. This avoids duplicates.

    I'm with Ssanfu - have a specific autonumber id

    You can create a composite index which does not allow duplicates for your three ID's

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm with ajax and Steve --autonumber for PK and composite unique index to prevent duplicates.

    What tables do you have in the database?

  5. #5
    pattrickcolin is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44
    Thank you ssanfu, ajax and Steve for reply.
    I do have an autonumber field, along with other fields, built into the table. It was the article from ssanfu that prompted my question. I had not considered a 'composite-unique-index-access'. Will investigate this. Thankyou

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

Similar Threads

  1. Replies: 6
    Last Post: 06-25-2016, 02:56 PM
  2. Replies: 22
    Last Post: 11-28-2015, 06:14 PM
  3. Replies: 5
    Last Post: 10-22-2013, 07:37 AM
  4. Replies: 1
    Last Post: 06-19-2012, 06:12 PM
  5. Count Unique Records
    By PonderingAccess in forum Queries
    Replies: 2
    Last Post: 08-19-2010, 06:54 AM

Tags for this Thread

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