Update records from anothet table

Posted on 2011-04-20
Last Modified: 2012-05-11
I have a table CombinedCount with ItemNumber, Count, and ChngNote we had to correct some of the item numbers and counts as they were in wrong. I have exported the the corrected item numbers, counts and a reason for change  to a table CorrectedItem with only the corrected items. I want to replace the bad Item numbers, count and add the notation to ChngNote in CombinedCount with the corrected Itemnumber, count and ChngNote from CorrectedItem I think it can be done with a sub query but I am not sure how.
Question by:skull52
    LVL 6

    Expert Comment

    So what is your unique field to join on? You mentioned "I want to replace bad Item numbers".

    Can you show an example of your data?
    LVL 23

    Accepted Solution

    hi skull2,
    - i assume ItemNumber is the Primary Key for both tables. but how do you identify which is the wrong ItemNumber in CombinedCount and whch is the correct ItemNumber in CorrectedItem to replace that particular wrong ItemNumber?

    - unless you have a unique identifier between CombinedCount and CorrectedItem , you need to defined the ItemNumber manually and execute the update query one by another.
    - i have prepared an UPDATE statement whereby when you run, it will:
    : 1st prompt to input parameter for the ItemNumber in CorrectedItem table
    : 2nd prompt to input parameter for the wrong ItemNumber in CombinedCount table
    : then i will replace the ID, Count and ChngNote in CombinedCount with ID, Count and ChngNote from CorrectedItem.
    : (again if ItemNumber is the primarykey) the Update will work on
    - same CorrectedItem.ItemNumber to same CombinedCount.ItemNumber
    - CorrectedItem.ItemNumber to a different CombinedCount.ItemNumber; and the CorrectedItem.ItemNumber doesn't have duplicate in CombinedCount.

    UPDATE CombinedCount 
    SET ItemNumber = DLookup("ItemNumber", "CorrectedItem", "ItemNumber = " & Forms![CorrectedItem]!ItemNumber), 
    Count = DLookup("Count", "CorrectedItem", "ItemNumber = " & Forms![CorrectedItem]!ItemNumber), 
    ChngNote = DLookup("ChngNote", "CorrectedItem", "ItemNumber = " & Forms![CorrectedItem]!ItemNumber)
    WHERE ItemNumber = WrongItemNumber;

    Open in new window


    Author Comment

    Yes the ItemNumber is the primary key for both tables the corrected table has the wrong Item number in the first column and the correct item number and count in the second and third columns respectively. and the wrong itemnumber is in the combined count  
    LVL 23

    Expert Comment

    does the above update work for you?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    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…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    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

    12 Experts available now in Live!

    Get 1:1 Help Now