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

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

Update records from anothet table

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.
  • 2
1 Solution
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?
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

skull52Author Commented:
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  
does the above update work for you?

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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