Results 1 to 3 of 3
  1. #1
    acannon is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Birmingham, AL
    Posts
    16

    Unhappy Change Unique Record Identifier in Existing DB

    I have an Access 2010 db for monitoring data about equipment on engineering projects. There is a basic table containing the complete equipment number, name, and a couple of other items, and then multiple related tables with specifics about the equipment. The equipment number is comprised of 3 separate fields, Area, Type, and Sequence. Currently Sequence is the single unique identifier, and is used to relate the basic table to the specifics. After working with this db for a while we have decided we would like to use the combined Area and Sequence as the unique identifier. Area is a text field and Sequence is a number with 2 decimal places. The related tables don't currently have Area in them, only Sequence, but since Sequence is currently unique it won't be difficult to add Area to those other tables.



    Is there a reasonable way to change to the compound unique identifier and if so how do I do it? Would it be better to simply create a compound index? How would I do that?

    Any help will be greatly appreciated!

    Alan

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Create field in the related tables, run UPDATE sql action to populate, set fields as compound index to prevent duplicate pairs. If the related tables don't also have dependent tables, a primary key is not a concern.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    acannon is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Birmingham, AL
    Posts
    16
    Thanks, June7! That solves the issue nicely.

    Alan

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

Similar Threads

  1. Custom Unique Identifier
    By sstrode in forum Forms
    Replies: 2
    Last Post: 09-17-2014, 05:10 PM
  2. Adding a unique identifier to each record
    By Jessica240 in forum Queries
    Replies: 28
    Last Post: 07-15-2014, 01:42 PM
  3. Replies: 1
    Last Post: 07-22-2013, 10:17 PM
  4. Replies: 4
    Last Post: 04-09-2013, 03:54 PM
  5. Unique Record Identifier for MS SQL Server Tables
    By MGSP in forum Import/Export Data
    Replies: 2
    Last Post: 01-29-2006, 03:00 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