[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Need update query to move records

Howdy all!

I've been searching too long for this ... so I figured I'd throw it out to the EXPERTS and see if it's a simple fix.

I have a contacts database with a Primary Key called ID.  I have 6 other tables linked to the Contacts table using a field called ContactsID, which contains the ID of the record in the Contacts table.

Now I have duplicates ... and what I want is a simple update query that changes the value of the ContactsID field in the subtable from the record I want to delete, to the record I want to keep - thus moving all of the data from the duplicate contact to the contact I will retain.

Is there an easy way to change the value of the ContactsID field for all records containing ID 6974 to ID 1044?

I will deal with the duplicate contact record in a different way, at this point I
  • 3
1 Solution
Do you want to update all the ID's to a single value?
Dale FyeCommented:
I generally do this type of thing with dual listboxes.

On the left, I display a list of IDs and their associated text values.  This is were I select the value that I want to keep.

In the list on the right, I use a SQL that filters the Contacts table based on the selection in the left listbox.  So the RowSource of the list on the right might look like:

SELECT ID, LastName, FirstName
FROM tblContacts
WHERE [LastName] = Forms!myForm.lstLeftListName.column(1)

Then I have a replace button which updates the values the other tables with SQL that looks like:

SET ContactsID = Forms!myForm.lstLeftListName
WHERE ContactsID = Forms!myForm.lstRightListName

In your case, I would loop through all of those tables that use the client ID and do this in each of them.

Then I would delete the record in tblClients that is associated with the selected item in the right hand list:

DELETE * FROM tblContacts where ID = Forms!myForm.lstRightListName

And finally, you will need to requery both lists.
callstateAuthor Commented:
fyed - looks good, I will digest and see if I can apply to my situation

Lionking - I have about 60 pairs of duplicates that I knwo I need to merge.  Not enough to really worry about writing a bunch of code for.  However each of the 120 records has 6 tables linked to it that may have multiple entries, so that is the data I need to change.

I know ID 1044 and 6974 are the same person, I want to change the ContactsID field in each of the 6 tables for any record containing 6974 to 1044.  Then we will merge the data in 6974 that is missing in 1044, and delete 6974.  Then repeat for the other 59 duplicates ...
callstateAuthor Commented:
fyed -  Having trouble with the update statement ...

strSQL = "UPDATE " & Table & " SET " & Field & "=" & Value & " WHERE " & WhereSQL CurrentDb.Execute strSQL

produces ...
UPDATE History SET [ContactsID]=13 WHERE [ContactsID]=27
and the error

Runtime 3061 Too Few pararameters, expected 1

AUUUGHH what am I doing wrong?
callstateAuthor Commented:

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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