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

 

[Pat Hartman]

The problem also occurs when you have unique indexes on non-autonumbers and are trying to add or update a duplicate value. For example, SSN should NEVER, EVER be used as a primary key but if you store it, you should use a unique index on it since SSN's are unique for all US residents and citizens. If you find duplicates, you are seeing either a typo or identity theft.

Another cause is Foreign key values. Current versions of Access have reverted to defaulting to 0 for numeric fields. This may be acceptable when you are talking about money or quantities but it is NOT valid when the field is a foreign key since autonumbers never generate a 0. Therefore, if you have no value for the foreign key and it is not required, the default MUST be null rather than 0.

 

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