Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    SF, CA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Resolving Duplicates for Composite PK (2k SR-1)

    I'm trying to create a composite primary key. Originally, I wanted to set 3 fields as a composite PK, but there are duplicates. So I want to create a 4th field so that the composite PK would be comprised of 4 fields.

    I want the 4th field to be a "counter" field that counts the number of duplicates for the composite of the first 3 fields.

    The default value would be 1. If there are duplicates, the 2nd occurence would equal 2, etc. I can't use an Autonumber because there are over 300,000 records, so I get an error message. (I can't describe this very well and the data format doesn't paste well--please see the attached xls file.)

    Any ideas?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Resolving Duplicates for Composite PK (2k SR-1)

    Why not just use a single autonumber field as your primary key? You could then define a non-unique index on the table comprised of those 3 other fields.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resolving Duplicates for Composite PK (2k SR-1)

    Are you saying you get an error message when trying to use an autonumber? What error message? 300,000 is nowhere near the limit for autonumbers (you can have a tad over 4 billion autonumbers if you don't mind them wrapping and going negative, or a tad over 2 billion if you want them to stay positive).

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    SF, CA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resolving Duplicates for Composite PK (2k SR-1)

    This is the error message I get when I try to save an Autonumber as a PK:

    File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

    I also cannot use the autonumber ID because I plan to either append all records onto a single table or use a union query to join the tables (to be decided). A composite PK of the 4 fields would be the ideal solution to uniquely identify each record easily.

    Thanks again in advance.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resolving Duplicates for Composite PK (2k SR-1)

    Trying to do it the way you envision is going to cause you more problems because as you add records, that count field is going to be wrong, and you do not change primary keys unless you want a mess on your hands.

    As for the error message, The one you quoted relates to replication (MSKB Q198633) and you haven't mentioned that so far. If you are working with replication, you need to make that clear, since autonumbers behave differently in a replicated database in order to minimize possible record conflicts. If you are using replication and trying to create a composite key to get around possible duplicates entered in different locations, your problem is much bigger than the question asked.
    Charlotte

Posting Permissions

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