Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24

Thread: Append query key violations

  1. #16
    Join Date
    Dec 2008
    Posts
    37
    Hi

    Thanks for helping me and I am sorry that I have misled you in any way:

    1. There 2027 records in the input table

    2. The output table has an autonumber primary key and should simply add records - even if I had run the append query twice I would then have duplicates

    3. The append query fails 2027 records with a key violation - therefore none were added.

    I made a simple copy of the two files involved but stripped out most of the records. Cust na in the target file and Append input is the input file. Now you should be able to see all the field definitions. BTW, all the fields on the input are used to update the Cust na.

    File will be sent soonest as I need to zip it

    It worked simply meant that following your instructions I finally added the JPG

    Regards

    Tony Randel
    Last edited by trandel; 05-12-2009 at 11:14 AM.

  2. #17
    Join Date
    Dec 2008
    Posts
    37

    Zip copy of for layouts

    A zipped copy of the two files is attached.
    Attached Files Attached Files

  3. #18
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Please attach a zip copy of your tables - DO NOT DELETE any records. Also, the queries or code used to append the records to Cust na.
    Allan

  4. #19
    Join Date
    Dec 2008
    Posts
    37

    Zip copy of all tables and queries

    Hi

    There seems to be 100k limit on attachments - I tried to send them as a zipped small db but with full tables but they are way in excess of the limit.

    The Cust master is 4899 records and the input is 2027 records. There 2 queries to select and make the input table and then one more to actually do the append. Total zipsize is 1.3MB The original file for importing is 3245 before stripping some duplicates out.

    How can I get it up to you?

    Regards

    Tony R

  5. #20
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Tony

    Would you post a ZIPPED copy, in access 2003 format, of the full tables and queries to my email address below? Also, list the order that you use the queries.

    I will have a look at it during the next few days.
    Allan

  6. #21
    Join Date
    Dec 2008
    Posts
    37
    Hi

    I just tested the append on the reduced size db and it is working. There must be a problem with relationships on the main database.

    I will need to investigate more ............... pse ignore this problem for now

    Regards
    Tony Randell

  7. #22
    Join Date
    May 2006
    Posts
    407
    Tony,
    Having read the posting today, I'm thinking that you might have a field in the master table, other than the primary key, that is indexed, and will not allow duplicates. Look at the index properties window to see what fields are indexed in the table. To see the Index properties window, put the table into design mode, then use the View menu, and the Indexes option.

  8. #23
    Join Date
    Dec 2008
    Posts
    37
    Hi

    Thanks so much for your help - it was a relationship problem on another indexed field (numeric) in the Customer and was not needed in this application - it allowed duplicates as well. I added it to the input file with a default value and the append now works.

    All your ideas helped me to rethink this after realising that the stripped down version worked - it just had to be a relationship issue!

    Regards

    Tony Randell

  9. #24
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    I am glad that you have solved your problem.
    Allan

Posting Permissions

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