Update query breaking rules issue (1 Viewer)

frankt68

Registered User.
Local time
Today, 01:16
Joined
Mar 14, 2012
Messages
90
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

MVP / VIP
Local time
Today, 00:16
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Yesterday, 20:16
Joined
Feb 19, 2002
Messages
42,981
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

Registered User.
Local time
Today, 01:16
Joined
Mar 14, 2012
Messages
90
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

MVP / VIP
Local time
Today, 00:16
Joined
Jan 14, 2017
Messages
18,186
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:16
Joined
May 7, 2009
Messages
19,169
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

MVP / VIP
Local time
Today, 00:16
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 01:16
Joined
Mar 14, 2012
Messages
90
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.
 

Users who are viewing this thread

Top Bottom