CODEWITHSUNDEEP
ms-access
MK01111000
MK01111000

Reputation: 822

How do I update values in a table from another table with the exact same fields?

Let's say I have a table called AA.
I distributed 2 copies for an update.
In 1 copy, called Table1, all values in Field2 are updated updated where the value in Field1 is AA.
In the other copy Table2 all values are updated where the value in Field1 is BB

My question is:
How do I update table AA with this new information?

enter image description here

Upvotes: 0

Views: 401

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

This query will update AA.Field2 with values from Table1.Field2 if ID match and where Field1=AA``

UPDATE AA INNER JOIN Table1 ON (AA.Field1 = Table1.Field1) AND (AA.Id = Table1.Id) SET AA.Field2 = [Table1].[Field2]
WHERE (((Table1.Field1)='AA'));

You can make a second query but with parameters of TableB.

UPDATE AA INNER JOIN Table2 ON (AA.Field1 = Table2.Field1) AND (AA.Id = Table2.Id) SET AA.Field2 = [Table2].[Field2]
WHERE (((Table2.Field1)="AA"));

UPDATE: You can make all updates on a single Query:

UPDATE Table1 INNER JOIN (Table2 INNER JOIN AA ON (Table2.Field1 = AA.Field1) AND (Table2.Id = AA.Id)) ON (Table1.Field1 = AA.Field1) AND (Table1.Id = AA.Id) SET AA.Field2 = IIf([AA]![Field1]="AA",[Table1]![Field2],[Table2]![Field2]);

Upvotes: 1

Related Questions