Best way to filter data by criteria, and display description using SQL in MsAccess?

Question

I have a table in MS Access of data containing results from a survey, and I have a look up table of Risk Ids and descriptions of the sort of risk based on the survey results.

What I've tried so far is selecting distinct entries from my survey table, and inputing a new field into my query for the Risk Code whose number will depend on criteria that I determine, which I will then use to look up the risk.

My table for the survey looks like so:

Name       | Location | Days spent eating IceCream | Icecream eating location
John Smith | London   | 30                         | Hull

My Risk ID table looks like so:

RiskID | RiskBool | Description 
1      | Yes      | At risk -  This person eats too much icecream
2      | Yes      | Risk - This person does not eat enough icecream
3      | No       | Sensible amount of icecream eaten
4      | Yes      | It is illegal to eat icecream in Hull

And my query looks (something) like this in access design view

Name       | Location | Risk Code | RiskID | Description

I want to write SQL to change the Risk Code to 1, 2, 3, 4 (up to 15 in my real case) and then I will tell it to only display the person and the description for when the Risk ID and Code match. I haven't written this yet.

What is the best way to achieve this?

I see two possibilities:

1. Set up 15 queries one for each risk ID, add the descriptions to those and then join those 15 sets of results together. This is what I know how to do, but could end up quite messily.
2. Set up some 'check' using if statements, and then some how setting the Risk Code field for that entry.

My current SQL looks like this, but it doesn't make any checks yet, I'm worried the if statment will be very, very long.

SELECT DISTINCT 
[At Risk Employee List].Employee AS Name, 
[At Risk Employee List].[DaysIceCream] AS [Days spent eating Icecream], 
[At Risk Employee List].[Base Location],

     [RiskCode] AS [Risk Code], <----is this  where the check would need to go?

RiskDescLookup.RiskBoolean, 
RiskDescLookup.RiskExplanation
FROM RiskDescLookup, 
[Survey Raw Data] 
INNER JOIN 
[At Risk Employee List]
 ON 
[Survey Raw Data].ResID = [At Risk Employee List].[Staff ID]
GROUP BY 
[At Risk Employee List].Employee, 
[At Risk Employee List].[DaysIceCream],
[At Risk Employee List].[Base Location],
RiskDescLookup.RiskID,

     [RiskCode] AS [Risk Code],  <----is this  where the check would need to go?

RiskDescLookup.RiskBoolean, 
RiskDescLookup.RiskExplanation

I imagine the check done by if statements to be Very long and look something like (in pseudocode):

if ( [At Risk Employee List].[Base Location] = Hull, then [RiskCode]=4...., else if (DaysIceCream>42) then....

Is that the best way to do this? Do I even need to have a Risk Code?

I'm a bit lost as to how to produce this 'check' in the best possible way.

Answer 1

The best way to do this is to use a look up table that emulates the structure of your data.

Add a row for every 'case', and in MS Access link the corresponding fields together.

Here is a few of the links:

Then alter the SQL to pair up any options that need to go together. For instance each of the checks I make are duplicated for two seperate locations.

Here is an example:

FROM RiskDescLookupReg
 INNER JOIN ([Survey Raw Data]
 INNER JOIN [At Risk Employee List]
 ON [Survey Raw Data].ResID=[At Risk Employee List].[Staff ID])
 ON (RiskDescLookupReg.RegTravelChoice=[Survey Raw Data].RegTravelChoice)

 And (RiskDescLookupReg.MonthChoice2=[Survey Raw Data].MonthChoice2
 And RiskDescLookupReg.PercentageTimeChoice2=[Survey Raw Data].PercentageTimeChoice2
 And RiskDescLookupReg.LimitedDurationChoice2=[Survey Raw Data].LimitedDurationChoice2
 And RiskDescLookupReg.TemporaryPurposeChoice2=[Survey Raw Data].TemporaryPurposeChoice2)

 Or (
RiskDescLookupReg.MonthChoice1=[Survey Raw Data].MonthChoice1
 And RiskDescLookupReg.PercentageTimeChoice1=[Survey Raw Data].PercentageTimeChoice1
 And RiskDescLookupReg.LimitedDurationChoice1=[Survey Raw Data].LimitedDurationChoice1
 And RiskDescLookupReg.TemporaryPurposeChoice1=[Survey Raw Data].TemporaryPurposeChoice1)

Not how there are two blocks for each location. If I only had one location of interest, I could drop the last block.

If you get duplicates because of the way your lookup table is arranged, you need to specify that the parts from the lookup table are enclosed in a LAST, and the parts from the survey in FIRST. Here is an example:

SELECT 
[At Risk Employee List].Number,
 FIRST([At Risk Employee List].Employee) AS Name, 
FIRST([At Risk Employee List].[Base Location]) AS BaseLocation, 
LAST(RiskDescLookupReg.RiskBool) AS RiskBool, 
LAST(RiskDescLookupReg.RiskDesc) AS RiskDesc,

The use of LAST ensures that if someone would come up as at risk and not at risk, only the LAST at risk case is displayed (those entries come later in the field). This is counter to the fact when duplicates are displayed the at risk ones come first.

Answer 2

I am not entirely certain of your intent, but from what you've posted and the follow up comments it would appear that the process of joining the Risk Code to Risk ID is relatively simple once you have the Risk Code identified for each survey result.

The real issue it seems is how to encapsulate the logic to identify the Risk Code for each survey result. I would suggest "calculating" the risk code value for each survey result externally to your query and then join to those results before finally joining to the Risk ID.

For example, I might add a third table to the design SurveyRisk that contains Name and Risk Code.

Use whatever criteria and logic you need to use to identify the risk for each survey response. Enter these values into the SurveyRisk table. Then, you can simply join Survey to SurveyRisk to Risk to summarize your results.

Feel free to clarify where I'm misunderstanding what you are trying to accomplish and I'll edit my post accordingly.