Linking fields based on ranges

[Guest]

Here is a million dollar question. Can I use ranges stored in one table to
apply a range indicator with a query. For instance table one has a field
labled score ranges. In that field are expressions eg <= 20 and >=10. The
second field in the table has risk labels for each range eg Very High Risk.
I have three other tables that have risk scores. For my purposes I average
the three scores in these tables. Is there anyway to link this average score
to the expressions so that I can writed a query that will allow me to attach
a risk label to each average score. So a score of 14 would be Very High. I
am trying to do it this way because I need to have a form that allows the
user to choose a risk label, very high, high, low to display different retail
locations. When I perform all of the calculations in one query it will not
let me use the form selection as criteria. Thanks in advance for the help.

 

[Guest]

In that field are expressions eg <= 20 and >=10. The second field in the
table has risk labels for each range eg Very High Risk.
Use three fields Low, High, and Risk.
Then in query criteria use --
Between [Low] And [High]

 

[Pat Hartman \(MVP\)]

You cannot work with relational operators embedded in a field. Those are
structural elements of the query. But you can join to a table using a
range. This is a non-equi join and is not supported by the QBE. Once you
change the SQL string, you will not be able to go back to the grid view for
this query. Build a table that contains three fields:
BeginRange, EndRange, ReturnValue
Make the primary key a multi-field key with BeginRange and EndRange

Change the query to
Select tblA.*, tblB.ReturnValue
From tblA Inner Join tblB on tblA.Value >= tblB.BeginRange AND tblA.Value
<= tblB.EndRange;

 

[Guest]

Will I be able to use data entered on a form as criteria for my query?

 

[Douglas J. Steele]

Sure.

Select tblA.*
From tblA
Where tblA.Value >= Forms!MyForm!ControlA
AND tblA.Value <= Forms!MyForm!ControlB