Hi
I'm new to the list and would appreciate any help. I have a database which includes two fields, each of which can return one of three answers.
Field A returns "Low", "Medium" or "High". Likewise, field B rteturns "Low", "Medium" or "High". The calculation I am seeking to make is based on the the value in both fields i.e. If Field A returns "Low" and Field B returns "high" I need the Result Field to return a value of "Medium".
When I use the IF function the calculation works if there is only one set of parametres. I have tried to add the other eight possible results to the calculation using "&", "and", "or", and even "+" operators but to no avail.

Any ideas

Regards and thanks in advance

Greetings and welcome to our forums,

There are two ways to approach this kind of problem, one is logical and the other is mathematical.

The logical approach would be to use logical qualifiers like you mentioned you started with. You would take your two fields and have a large conditional with all the possible combinations covered, in your case a brute force approach would work. For example:

Code: Select all

Case ( Field A = High & Field B = High ; High ;
Field A = High & Field B = Medium ; Medium-High ;
Field A = High & Field B = Low ; Medium ;
Field A = Medium & Field B = High ; Medium-High ;
Field A = Medium & Field B = Medium ; Medium ;
Field A = Medium & Field B = Low ; Medium-Low ;
Field A = Low & Field B = High ; Medium ;
Field A = Low & Field B = Medium ; Medium-Low ;
Field A = Low & Field B = Low ; Low ;
Default Result )

Now obviously, there are no quotes around my sample code, and the field names are obviously off, but I think you get the idea. The problem with this sort of approach is that it doesn't scale very well, and for this solution, I am not wild about the results, is Medium-Low an option, if not, what should it be? The detail is a bit lacking.

If you take a mathematical approach, you can assign values to each option, say High = 3, Medium = 2, and Low = 1, add the values from the two ( or three or more in the future ) fields, and come up with a value representing the combination. If you apply that to a map of what the final values represent, you can get a good detailed approach that would scale well and preserve your detail better.

The map and math should be straightforward, and to get you started, I would look at the "Let" function, and the "Choose" function, and let us know if you have any other questions.

Court

Thanks a lot for the help Court. I'll try as you suggest

Regards