IIF statement to populate field

[GriffyNJ]

Hello all:

So what I would like to do is this:

if table1.phone is equal to table2.phone then populate table1 field1 with data from table2.indentification#

What is the best way to go about doing this? is the if statement the best thing?

Thanks for your help

 

[Scooterbug]

Sounds like you have a normalization issue (Storing the same data in duplicate tables). But...

Create an Update query where you include both tables. Create a join on the phone number. Then update the table1.field1 to table2.identification#

Also, if you actually have the # in the field name, you should get rid of it. Using special characters for field names will cause issues later on.

 

[GriffyNJ]

Ok I did an update query like you said, and it didn't seem to work. the data ended up erasing completely from the original field. should there be a criteria on the update query?

 

[Scooterbug]

What is the exact Sql that you used?

Hopefully you were working with a copy of the data as well....

 

[GriffyNJ]

This is the actual code I’m trying to write
UPDATE [Facility Code EHaRS] INNER JOIN GriffTest ON [Facility Code EHaRS].phone = GriffTest.FACILITY_PHONE SET [Facility Code EHaRS].facility_uid = GRIFFTEST.FACILITY_UID
WHERE ((([FACILITY CODE EHARS.PHONE])=[FACILITY_PHONE]));


This was two dummy tables I tried the same thing out on to see if it’d work
UPDATE Table2 INNER JOIN Table1 ON Table2.HUMBER = Table1.NUMBER SET Table2.STUFF = TABLE1.INFO;

 

[Scooterbug]

This was two dummy tables I tried the same thing out on to see if it’d work
UPDATE Table2 INNER JOIN Table1 ON Table2.HUMBER = Table1.NUMBER SET Table2.STUFF = TABLE1.INFO;

You got it backwards. Your post said:

if table1.phone is equal to table2.phone then populate table1 field1 with data from table2.indentification#

and the Sql you tried said:
"Update a field on Table2 where the fields are equal based on the number. Update the Field Stuff from Table2 to equal the value of what's in field Info on table1"

what it should be is:

UPDATE Table1
INNER JOIN Table1 ON Table2.Number = Table1.Number
SET Table1.field1 = table2.Identification#

Your query updated table 2 with the value of the field in table1, which is why it updated it to nothing.