Update the contents of 1 field with the data of another table

[GlenLynam]

Morning,

I have a question regarding queries that im hoping someone can shed some light on for me.

OK... I have 2 tables one contains the details from the logging that our phone switch kicks out the other has the contents of the local directory. So the first table literally has all the information about each call that goes through our switch. The second has 2 columns the first being the directory name the second the number that coincides with that.

What i actually want to do is cross reference the phone numbers from he directory with he ones the switch kicks out and then if there is a mtatch update the field on the switch information so that it has the directory name rather than a number. I have tried an update query with a if....hen statement in it but no joy. Am i in the right place or completely wrong.

Any helpsomeone can shed will be greatly appteciated as im really stuck with this now. I think i can join the queries in some way and do it that way. Can anyone please tell me how to complete this.

Glen

 

[mordja]

Glen,


You can do it with an update query and you dont need an if statement.

For example lets say you have two tables Switch(PhoneNumber, Info) and Dir(DirName,PhoneNumber) and you want to set all matching PhoneNumber fields in Switch to the corresponding DirName value in Dir. Create an update query and join the two by PhoneNumber. Add the PhoneNumber field from Switch to the field list to be updated. In the update to box enter [Dir]![DirName].

Thats it. Obviously your names will be different. However for the above to work your PhoneNumber field must be a text field. I think you might be better off adding a DirName field to your switch table and updating that rather than the PhoneNumber field.

Mordja

 

[GlenLynam]

nice one thanks!!