Integrating information updates from satellite table to master tab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NewtoComputing - 18 Apr 2008 00:39 GMT

I apologise in advance if this is an aggravating question.

I want to have 2 tables. Both have exactly the same fields/column
definitions. One is a "master" table containing existing data. The "other"
table is empty except for a few cells of updated data and the Primary Key
field (ID).

Can I merge or append the two so that the few cells of updated data in the
"other" table replace their matching cells in the "master" table without
changing any other cells in the "master" table?

Reply to this Message

John W. Vinson/MVP - 18 Apr 2008 02:14 GMT

>I apologise in advance if this is an aggravating question.
>
[quoted text clipped - 6 lines]
> "other" table replace their matching cells in the "master" table without
> changing any other cells in the "master" table?

Yes, with some difficulty. Just how you do so depends on whether this is
truly an update table in which you will ONLY be updating existing records
(existing primary key values), or whether you will also be adding new
records.

Let's say your two tables are named MasterTbl and UpdateTbl. To update
existing records you would create an Update query joining the two tables on
the primary key; each field in MasterTbl *except* the primary key would be
updated to

NZ([UpdateTbl].[fieldname], [MasterTbl].[fieldname])

Note that this technique will ignore any Null fields in UpdateTbl so that
you cannot erase a field value by putting a NULL into the UpdateTbl.

If you want to also be able to add new records, change the join type to a
Left Outer Join and include the primary key in the update (unless the PK is
an autonumber, which cannot be updated).

Reply to this Message

NewtoComputing - 18 Apr 2008 07:31 GMT

Thanks for that John, however I need a little more information.

I entered the syntax you suggested;
NZ([UpdateTbl].[fieldname], [MasterTbl].[fieldname])
into the "criteria section" in the design view of the Update query.
I press "run" , but am then told that I need a "destination field" before
the query can run.
What does this mean?

Your help is much appreciated.

> >I apologise in advance if this is an aggravating question.
> >
[quoted text clipped - 25 lines]
> Left Outer Join and include the primary key in the update (unless the PK is
> an autonumber, which cannot be updated).

Reply to this Message