lankapala
asked on
MS ACcess SQL query
This is my Sql coding
UPDATE Book1 T1
SET [newlocation-ID] = DMin("IdLocation","Book1", "Address LIKE '" & Address & "*' OR '" & Address & "' LIKE (Address + '*'")
WHERE
EXISTS (Select T2.IdLocation FROM Table2 T2 WHERE (T1.Address LIKE (T2.Address + '*') OR T2.Address LIKE (T1.Address + '*'))AND T1.PC = T2.PC AND T1.IdLocation <> T2.IdLocation)
Please find the attachement folder for Erors and Table format.
Errors showing i can't update the records. the error in the attachment folder
Book1.JPG
Error1.JPG
Table2.JPG
UPDATE Book1 T1
SET [newlocation-ID] = DMin("IdLocation","Book1",
WHERE
EXISTS (Select T2.IdLocation FROM Table2 T2 WHERE (T1.Address LIKE (T2.Address + '*') OR T2.Address LIKE (T1.Address + '*'))AND T1.PC = T2.PC AND T1.IdLocation <> T2.IdLocation)
Please find the attachement folder for Erors and Table format.
Errors showing i can't update the records. the error in the attachment folder
Book1.JPG
Error1.JPG
Table2.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Errors similar to the one you are getting are the result of you trying to paste the same key field value into the table more than once.
This keyfield is either a Primary Key or an indexed field with it's AllowDuplicates property set to No.
Check you tables and their properties carefully, then check the query string just as carefully.
A test would be to create a test database with no Primary keys or indexes and then try the code/SQL
Let's see if another Expert has more insight...
JeffCoachman