How to find Score corresponding to Value2 in the Table2 based on the ranges Min and Max in Table1 and the corresponding Value1(proper Value1 should land in the Score of Table2)?
Below example with a properly filled Score column in the Table2:
Table1
Table2
I can't find proper formula SQL/QBA or other solution.
Using the forms (Form1) Score can be find by the formula: DLookUp("First([Value1])";"[Table1]";"[Min]<=[Forms]![Form1]![Value2] And [To]>=[Forms]![Forms1]![Value2]").
But it doesn't work in the update type query.
Below example with a properly filled Score column in the Table2:
Table1
Value1 | Min | Max |
4 | 10 | 20 |
7 | 20 | 30 |
10 | 30 | 40 |
Table2
Value2 | Score |
27,25 | 7 |
11,15 | 4 |
32,105 | 10 |
20 | 7 |
Using the forms (Form1) Score can be find by the formula: DLookUp("First([Value1])";"[Table1]";"[Min]<=[Forms]![Form1]![Value2] And [To]>=[Forms]![Forms1]![Value2]").
But it doesn't work in the update type query.
Last edited: