Update query breaking rules issue

[frankt68]

Hi!

I have an update query and when I try to run it, it says that not all records could be updated because of keys violation rules. I understand why this is happening but I'd like to know how can I find those records that are violating the keys rule. I'm using access 365 and don't know much about it.

Regards,

Frank

 

[isladogs]

This usually occurs because you have an autonumber primary key field in your table and your update query includes that field. Remove it from the query

 

[frankt68]

Thank you both for the answers.
Automatic numbering is not a problem, as I'm not adding new records, I'm only updating existing ones. I need to update some of the data, which also includes updating a field with indexed values where duplicates are not allowed. Therefore, it's okay for me to be alert to any duplicate values when updating records, as this may indicate a typo or some other error. This field is the only indexed field (except the ID field) where the duplicate entries are not allowed, so I know that the problems are associated with this field.
I need to update more than 500 records. After running an update query a warning appears, saying that the update on three records is not possible due to a violation of the keys rules.
I'd like to know which three records cause trouble, so I can check these records and fix them. So how can I find these three records?

 

[isladogs]

Thank you both for the answers.
Automatic numbering is not a problem, as I'm not adding new records, I'm only updating existing ones.

Its still a problem if you include that field in your update query.

 

[frankt68]

Its still a problem if you include that field in your update query.

The ID field (Auto number) is not included in update query.

 

[arnelgp]

if you already updated the table then there is no way of findibg the 3 records.
If you havent yet, you can create a query Inner joining the 2 tables on tge field that you want to update.

 

[isladogs]

if you already updated the table then there is no way of findibg the 3 records.
If you havent yet, you can create a query Inner joining the 2 tables on tge field that you want to update.

That's basically correct except that as you didn't manage to update those duplicate fields, you can still link the two tables in the way arnel described to identify the records

Not so easy if you are updating using set values in your query rather than another table

 

[frankt68]

if you already updated the table then there is no way of findibg the 3 records.
If you havent yet, you can create a query Inner joining the 2 tables on tge field that you want to update.

Thanks arnel, that does the trick.