Update TableA with data from TableB using Update Query

[Ellette]

I am trying to add two fields to Table A from Table B.
When AcctNo from TableA = AcctNo from TableB (here it is a primary key), I
want two fields added to TableA. I have the fields created for TableA, but
the update query returns blanks.

 

[Michel Walsh]

You can UPDATE existing values with a join.

UPDATE prices INNER JOIN newPrices
ON prices.ItemID = newPrices.ItemID
SET prices.unitPrice=newPrices.newUnitPrice



You can append, horizontally, new fields, with a join

SELECT tableA.*, tableB.*
FROM tableA INNER JOIN tableB
ON tableA.itemID=tableB.itemID


(where you preferably change the * to list only the fields you really want).

Sure, if ItemID is duplicated, in both tables, you would get a
'multiplicative' effect (more than one row will be 'matched' )



Hoping it may help,
Vanderghast, Access MVP

 

[Ellette]

I was hoping to do this with an update query as I don't SQL...Don't mind
learning, just never have and don't know where to go!

 

[Michel Walsh]

Both of these queries can be done 'graphically' in Access query editor.

Bring the two tables, JOIN them through their common field, let say, ItemID.

In the update query, change the SELECT query to an UPDATE query, and 'set'
the fields to be updated in the grid.

In the SELECT case, that is even easier: drag the required fields, from the
upper part, to the grid.


Hoping it may help,
Vanderghast, Access MVP