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
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.
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.
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.
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!