Update Table/colum with info in a different table and multiple columns 1

[sjck]

I would like to update a table with information from other tables. I need know how to update the factor column with the information that is contained in different columns in the Rate table (see updated claim table for example). I can look up the column I need to access in the Procedure table, but I do not know how to write the code using a variable column. Any help would be appreciated.

Below is a sample of the tables and data.
Claim
Claim CPT Provider ID IPA CPT Category Factor
001 99201 PRV001
002 10021 PRV001

Provider Table
Provider ID IPA
PRV001 A032

Procedure Table
CPT CPT Category
99201 Medical
10021 Surgical

Rate Table
IPA Table Medical Surgical
A032 RVS 5.82 99.50

Updated Claim Table

Claim CPT Provider ID IPA CPT Category Factor
001 99201 PRV001 A032 Medical 5.82
002 10021 PRV001 A032 Surgical 99.50

Any help would be appreciated

 

[PHV]

It's often a bad idea to store derived/calculated values in a table.
Here a query to retrieve the informations:
SELECT C.Claim, C.CPT, C.[Provider ID], I.IPA, P.[CPT Category], IIf(P.[CPT Category]='Medical',R.Medical,IIf(P.[CPT Category]='Surgical',R.Surgical,0)) AS Factor
FROM ((Claim AS C
INNER JOIN Provider AS I ON C.[Provider ID] = I.[Provider ID])
INNER JOIN Procedure AS P ON C.CPT = P.CPT)
INNER JOIN Rate AS R ON I.IPA = R.IPA

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

 

[sjck]

Thank you so much it worked.