Creating a SQL update query in MS Access 1

[lyleatmark]

I have two table with a set of records
TABle A
ACC# Name Name2 Name3 Name4
1 John
2 Mary
3 Richard

TABLE B
ACC# Name
1 Dick
1 John
2 Vincent
2 jane
3 Med
3 John
3 Mary


I want to update my table A records in field Name2,Name3 etc When the act# of tables A and B are equal. I neednan expamle ofa SQL script to do that

 

[PHV]

For Name2:
UPDATE TableA A INNER JOIN TableB B ON A.[ACC#]=B.[ACC#]
SET A.Name2=B.Name
WHERE A.Name<>B.Name AND A.Name2 Is Null;
For Name3:
UPDATE TableA A INNER JOIN TableB B ON A.[ACC#]=B.[ACC#]
SET A.Name3=B.Name
WHERE A.Name<>B.Name AND A.Name2<>B.Name AND A.Name3 Is Null;
etc.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

any particular reason you want to de-normalize your tables?


Leslie

 

[lyleatmark]

Actually all the data was orginally in one table. I am doimg this because i want everyone with the same Act# to be in one record. I have to do this to the data send to another progam that needs it in that format. so I divided the records first based on another field in the original table. This field has consist of a code, where only one person with the same Act# has a of one and all the others have a code of 2. The I was faced with th e problem above

If there is any other way of get the same result please tell me.

 

[lyleatmark]

thanks this worked fine