Update Table

  • Thread starter Thread starter David W
  • Start date Start date
D

David W

first question
how do you make a query compare data from 2 different tables and replace it
if it is different.
I have got a imported table that I am wanting to compare to table3, and if
the data is different in certain fields in the imported table, change only
those certain fields in the table3.

say if the imported table had
field1 field2 field3 field4 field10
C001 Van red rewarded tune
C002 Truck red rewarded tune

say if table 3 had
field6 field9 field11 field13
field16
C001 car blue(keep this value) probation tune 2

how do you get table3 to change to
field6 field9 field11
field13 field16
C001 Van blue(value stayed the same) probation tune 2
C002 Truck red rewarded
tune 2

I DO NOT WANT TO CHANGE THE INFORMATION IN THE IMPORTED TABLE.

Last question,
If field10 from the imported table had "tune"
how could you get the data to change to "tune 2" in table3
 
first question
how do you make a query compare data from 2 different tables and replace it
if it is different.
I have got a imported table that I am wanting to compare to table3, and if
the data is different in certain fields in the imported table, change only
those certain fields in the table3.
Click to expand...

Well, if the value didn't change, then you can just update it anyway:
updating "Blue" to "Blue" doesn't hurt your database!

You will need some unique ID in the table. I'm guessing that Field1 in
the imported table and Field6 in Table3 are that field: that C001
uniquely identifies a record.

If so create a Query joining ImportedTable to Table3 by linking Field1
to Field6 (and if those are your fieldnames... I'd suggest changing
them to something else!!!) Change the query to an Update query using
the Query menu option or the query type tool on the toolbar.

Then for each field that you want to update in Table3, put onto the
Update To line an expression like:

[ImportedTable].[FieldX]

where FieldX is the name of the field that you want to use for the
update.

For the Field10, you could update to

IIF([Field10] = "Tune", "Tune 2", [Field10])

This will update to Tune 2 if the imported value is "Tune", and update
to whatever was in the imported field if it's anything else.

John W. Vinson[MVP]
 
Back
Top