Code for updated query

[massa]

I have 2 tables one we'll call Table main the other table U. I need to update
table U data into Table Main. The fields I have are product id and USD
amount.
I created a join in both tables by the unique product id number. I then
tried an update query with Update to [Table u].[usd]
How would I add in that if there is no product id code in Table U that
matchs the Table main codes enter 0 or skip to the next line. Please help.
Thanks.

 

[John Spencer]

It always helps if you post the SQL of your existing query.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the
way you expect. Your SQL for the query would look like the following.


UPDATE TableMain LEFT JOIN TableU
ON TableMain.ID = TableU.ID
SET TableMain.USD = NZ([TableU].[USD],0)

In design view
-- Add both tables
-- Drag from product id to product id field
-- Double Click on the join line and select the option for all records
in table main and only matching in table U
-- Add table Main USD field to the grid
-- SELECT Query: Update from the menu
-- in the UPDATE TO "cell" enter the (brackets required)
NZ([TableU].[USD],0)
-- Run the query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================