• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
0
lankapala
Asked:
lankapala
  • 2
1 Solution
 
Jeffrey CoachmanCommented:
lankapala,

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
0
 
Jeffrey CoachmanCommented:
Either that or you are trying to add a child record that does not have a corresponding Foreign key value.

For example: You are trying to add an Order (Child Record) for a customer that does not exist yet in the Customer table.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now