Please help, update Query (2 tables)

L

Lin

Not sure why, but I have one table with ID, DOB. 2nd table ID, DOB.
When I try to update table2, Table1 field is emptied but table 2 not updated.

Any ideas?

Thanks
 
C

Chaim

More detail please. Based on your description, I can't tell whether you've
got a design problem or just trying something out. What does your update
query look like?
 
L

Lin

Hi Chaim, thanks for reply.

Table 1 has many fields but the two of concern are ID and DOB
table 2 has only ID and DOB

I created a query (update) with table2 selected field DOB and for the
(update to) section, showed table 1 and field DOB. I ran the query, and
the files disappeared from table2 but did not update table 1. They are
joined by the ID.
 
C

Chaim

Lin,

Sounds like you used the Query Builder to create the query and I think there
is a misunderstanding about what the Update To means.

Update To is the value that you want to have in the target field when you're
done with the update. So based on what you say, Table 2 DOB wants to be
updated with value in Table 1 DOB for matching ID values. You then say the
values disappeared from Table 2 (implying that there were values there to
begin with) but Table 1 wasn't updated. This implies that Table 1 should have
been the target of the update.

Try reversing the values in the update and update to rows. It sounds like
you should have:
Field: DOB
Table: Table 1
Update To: Table 2.DOB

The SQL should look like:

update Table 1 set DOB = Table2.DOB
where Table1.ID = Table2.ID;

Good luck!
 
J

John Vinson

Hi Chaim thanks again for reply, still not working. Maybe I dont understand
the Update.

The Main table Has as I said earlier many field. ID and DOB (Date of Birth)
The Alternate table has ID, DOB.

The main table has many DOb filled in but in the alternate table there are
additional DOB's not in the main table. I want to update those that are
blank with the ones in the alternate.
Click to expand...

First, BACK UP YOUR DATABASE! This process can destroy data if you do
it wrong!

Try creating a Query joining Table1 to Table2 by ID. Change it to an
Update query.

Put a Criterion on the criteria line under Maintable.DOB of

IS NULL

(unless you want to blindly overwrite all DOB's in the main table with
those from the related table).

Change the query to an Update query and put

[Alternate].[DOB]

on the Update To line (assuming that Alternate is in fact the name of
the second table).

Run the query by clicking the ! icon.

Now open the table and see if it's done the desired update.

John W. Vinson[MVP]
 
Top