Key Violation errors - Expert advice needed

[SallyG]

I have a table that I am try to add new records to using an append query.

All the fields in the query are of the same data type as the table i am appeneding into, everything matches perfectly but when I run the query i get the error msg, "didn't add XX number of records to the table due to key Violations".

Now if I remove my primary key from the AutoNo ID field in my table they append OK but as the key is removed, they append all over the place and not sequentially as the existing records are. Access starts assigning IDs from EXISTING records to the new ones which means when I display the records in a from, the wrong records show up.

Any ideas on what I am doing wrong here ?????

Thanks in advance,


Sally [sig][/sig]

 

[Robertd]

Hi Sally

if you append to a table with an autonumber type key field the target table will increment the key field automatically.

you cant add this field youself access must do it.
so what are you adding ie do you just want to add records to the taget table. in which case don't include this field in the query.

if the order of the records you are adding is important try an "order by" sub clause in the append query to add the records in the order you require. however you cant change the sequence (autonumber) of the existing records!

sorry with out any more information there are to many factors to consider

see ya
Robert
[sig][/sig]

 

[Waynest]

I'd say you need to reload your table from scratch to sort out your autonumber problem and then follow Roberts advice above abour ordering your records. Just rename your existing table, create a new one with the original name and create a query to copy everything over - leaving access to worry about the autonumber this time. [sig][/sig]