Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append records that have auto numbered key (97)

    I have over 35 regions that each need their own database. I must maintain a master database. I did not want to deal will synchronized databases, and all regions can not access the master database anyway. Each region is required to have their own, separate data, and they will send me their data quarterly. At that time I want to append all data into my master database. My main question is this: I was going to use an auto number as the primary key for the records (there is no other unique number in this database such as a ssn number or order number). Can I use an autonumber as the primary key in all of the databases, and still be able to append the data into the master database. What if there are duplicate numbers in some of the databases? Is there another way for me to create a primary key, other than an auto number? Any suggestions will be greatly appreciated!

    Thanks,
    Claudie

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

    Re: Append records that have auto numbered key (97)

    First of all, how do you relate the data you import back to the data that comes from the regions? You'll need to do that in order to insure that the same data doesn't get imported twice.

    The easiest way I can suggest is to have an autonumber built into the table or tables in your master database. If the regions are already using an autonumber in their versions, you should have an additional long integer field in the tables in your master database. When you append the records from each region, you would append their autonumber to your long integer field and allow your database to create its own autonumber for your use and as a primary key. You would create an additional unique key on each table using the long integer field and the region identifier to insure that the same record could not be imported twice.

    If the regions happen to have duplicate autonumbers (which is highly likely), it won't be a problem with this approach, but you'll still be able to handle updates if necessary because you have preserved their individual keys in your table.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append records that have auto numbered key (97)

    Thank you! I have one more question about appending the records to the master table. Can you make the append, only import new and changed reecords? I did not want to import all records each time, but I also, did not want to only import new records, because then I would not get any pre-existing records that have been edited.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Append records that have auto numbered key (97)

    In theory it is possible - BUT how do you know which records have been imported already and which have not. Rather than using an autonumber key, I would create a primary key that uses one additional fields over and above what the regions send you. That would be the Region identifier. In each append query you would specify which region sent you the records. That in conjunction with their autonumber would be unique. Then if they send you records you could check for duplicates and reject them. You might also put a field into each of the records that indicated they had been sent to the central site, and as a part of the process of sending the data to you, put the flag in. That way they would only send records that had not been sent.

    Another option that would give you unique keys in the short run would be to make the autnumber field Random rather than Increment. That way the record IDs run from -2,000,000,000 to +2,000,000,000 and there is only a small probability that you will ever come up with dups. Obviously over time if you generate lots of records then it does get larger. I find that kind of key awkward to work with however if you need to work at the table level to debug a problem. Short numbers are easier to remember and put into filters or queries. The other option is to use GUIDs - they are virtually guaranteed to be unique. They do however consume more space, and we've seen significant performance hits if you try to use them as the primary key. Hope this helps.
    Wendell

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

    Re: Append records that have auto numbered key (97)

    If you've created a unique key based on the region identifier and their (converted) autonumber primary key, you won't import the same records twice. Changed records aren't handled with appends, they're handled with updates. If the source tables contained a datestamp that was changed when the record was edited, you could match the stamps up and update records where the datestamp in the region's data was later than the one stored in your master database.
    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
  •