Update Table

[Guest]

[Master]
FCode FDesc
--------------------
Code1 Desc1
Code2 Desc2


[Trans]
FTransCode FCode FValue
--------------------------------------
Trans1 FCode1 AAAAA
Trans2 FCode1 BBBBBB
Trans3 FCode2 AAAAA

hi , based on the above table diagram I have a problem where in order to
update the transaction table the FValue information is based on the [Master
Table]. The query i used in SQL server
"UPDATE [Trans] SET FValue=(SELECT [FDESC] FROM [Master] WHERE
[FCode]=[Trans].[FCode])" can perform the update transaction in SQL server
database without a problem but in MS Access it had given me an error message.
Thanx for anyone that can help me solve this problem

 

[Andreas]

Nobody has answered you yet.
I am not an expert on SQL but here we go.
Before doing so, I would like to say that I am puzzled why you are doing
this as it seems that you are duplicating a whole lot of data which can
be retrieved at any time using a query.
Still, your choice and there is probably a reason not obvious to me.

New SQL:
UPDATE [Trans] SET FValue=(SELECT [FDESC] FROM [Master] WHERE
[Master].[FCode]=[Trans].[FCode])

Alternative SQL:
UPDATE [Trans] SET FValue="'" & (SELECT [FDESC] FROM [Master] WHERE
[Master].[FCode]=[Trans].[FCode]) & "'"

I hope one of these works.
If not, at least we tried
If you are desperate, you can donate me a decent PC so I can load Access
and use it to test my SQL before posting it (without having to switch
from my 200MMX to my K6-2-300 and back)

Regards,
Andreas

 

[John Spencer (MVP)]

You should be able to get the information by using a JOIN. It makes little
sense to me why you would store the data twice. However, you may have a good reason.

UPDATE Trans INNER JOIN Master
ON Trans.FCode = Master.FCode
SET Trans.FVALUE = Master.FDesc

If Master.FDesc ever changes then the corresponding values in Trans will be
different until you rerun your update query.

That is why I would normally use

SELECT Trans.*, Master.FDesc
FROM TRANS LEFT JOIN Master
ON Trans.FCode = Master.FCode

This would always give the most current value in Master.FDesc for each record in Trans.