Hi everyone!
First of all, shout out to theDBguy for being very friendly and for being extremely helpful!
My main data table is called Data Table.
I have a field called MostSeriousCharge. This is input using a form called RA Entry Form.
I need to lookup the corresponding field called OffenseCategory in a separate table called ChargeList.
I have a calculated field called RiskScore.
I need to lookup the corresponding field called RiskLevel in a separate table called RiskList.
Using OffenseCategory and RiskLevel, I need to lookup the corresponding SmartPraxisRecommendation in a separate table called PraxisList.
My question is: how can I do this? Possible follow-up questions include: can this be a field in Data Table? Should this be a query? I know the experts are against calculated fields in tables, but I'm going to be running logistic regressions on this data later, and if it weren't in the table now, I'd just have to add it in using Stata later, so I might as well do it now if possible/feasible.
I know this is Access, but here it is in Excel in case you need to know exactly what I'm looking for:
{=INDEX(PraxisList[SmartPraxisRecommendation],MATCH(1,(VLOOKUP(RiskScore,RiskList[#All],2,FALSE)=PraxisList[RiskLevel])*(VLOOKUP(MostSeriousCharge,ChargeList[#All],2,FALSE)=PraxisList[OffenseCategory]),0))}
Attached is a sample database with dummy records.
Thank you in advance for your help!
Jacob
First of all, shout out to theDBguy for being very friendly and for being extremely helpful!
My main data table is called Data Table.
I have a field called MostSeriousCharge. This is input using a form called RA Entry Form.
I need to lookup the corresponding field called OffenseCategory in a separate table called ChargeList.
I have a calculated field called RiskScore.
I need to lookup the corresponding field called RiskLevel in a separate table called RiskList.
Using OffenseCategory and RiskLevel, I need to lookup the corresponding SmartPraxisRecommendation in a separate table called PraxisList.
My question is: how can I do this? Possible follow-up questions include: can this be a field in Data Table? Should this be a query? I know the experts are against calculated fields in tables, but I'm going to be running logistic regressions on this data later, and if it weren't in the table now, I'd just have to add it in using Stata later, so I might as well do it now if possible/feasible.
I know this is Access, but here it is in Excel in case you need to know exactly what I'm looking for:
{=INDEX(PraxisList[SmartPraxisRecommendation],MATCH(1,(VLOOKUP(RiskScore,RiskList[#All],2,FALSE)=PraxisList[RiskLevel])*(VLOOKUP(MostSeriousCharge,ChargeList[#All],2,FALSE)=PraxisList[OffenseCategory]),0))}
Attached is a sample database with dummy records.
Thank you in advance for your help!
Jacob