Need update query to move records

Posted on 2012-08-16
Last Modified: 2013-10-08
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
Question by:callstate
    LVL 13

    Expert Comment

    Do you want to update all the ID's to a single value?
    LVL 47

    Accepted Solution

    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:

    UPDATE Table1
    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.

    Author Comment

    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 ...

    Author Comment

    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?

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now