callstate
asked on
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
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
Member_2_861731
Do you want to update all the ID's to a single value?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ...
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 ...
ASKER
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?
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?
ASKER
Thanks!