How can I update fields in one table with fields in another table?

[Chrisso]

Hi All

I have two tables: my main table and a cut-down version of the main
table with newly updated values:

Main
-------
RefNo
Document_Type
Version
Title

ModMain
-------------
RefNo
Document_Type_UPDATED
Version_UPDATED
Title_UPDATED

I can't work how to use Access's query builder to do the simple task
of
updating the fields in "Main" with the modified values in "ModMain"
where the RefNos match.

Can anyone give me a pointer or web link? I cant find any information
in Access Help about performing update queries with more than one
table. This seems a straight forward action!

Chrisso

 

[Douglas J. Steele]

I won't ask why you want to store the data redundantly...

In the query builder, add the two tables Main and ModMain. If there isn't
already a relationship between the two tables, drag the RefNo field from one
of the tables and drop it on the RefNo field of the other table.

Add the three other fields from table Main to the grid and convert the query
to an Update query (there's a button on the button bar, or you can go to the
Query menu while the query's open in Design view).

Underneath the Document_Type field in the grid, put
[ModMain].[Document_Type_UPDATED] in the "Update To:" row. Do the same for
the other two field (putting, of course, the appropriate field underneath)

 

[Chrisso]

Thanks Doug.

FYI - once I have performed the update I will remove ModMain. It was
necessary to come up with the modifications off line.

Chrisso

 

[phillipo]

Thanks Doug.

FYI - once I have performed the update I will remove ModMain. It was
necessary to come up with the modifications off line.

Chrisso