Filling column in the table based on min/max ranges from other table. (1 Viewer)

[Rafal]

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

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

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.

 

[June7]

Should not actually save aggregate data to table. If it can be calculated for input to table it can be calculated when needed. However, a DLookup() should work in UPDATE action.

 

[CJ_London]

you also need to modify your ranges or formula

based on your formula, a value of 20 could have a score of 4 or 7

 

[Rafal]

you also need to modify your ranges or formula

based on your formula, a value of 20 could have a score of 4 or 7

You right, formula is wrong.

 

[Rafal]

Thanks, here is proper formula, works good: DLookUp("First([Value1])";"[Table1]";"[Forms]![Form1]![Value2]>=[Min] And [Forms]![Forms1]![Value2]<[Max]"). About update query - still doesn't work. Probably I can't properly address arguments to tables. It is easy to address to forms, but what are proper syntax and words to address it to tables?

 

[namliam]

You can simply "join" the tables.... drag both tables into a query and put in the proper where clause to put your value2 between the other 2 values from table1
Using DLookup is a bad idea, it is a huge performance drag

 

[June7]

I have never seen an aggregate function used within a domain aggregate function. I am very surprised you get any result. My attempt fails. There is a DFirst domain aggregate function. However, I do not recommend relying on First or Last functions.

If you need to address a field of table used in query, don't put it within quote marks.

However, @namliam likely has suggested best approach.

 

[Rafal]

To my understanding there is no aggregate function in this issue. In Table1 there are just ranges and in Table2 column with figures to which we have find relative score based on Table1 ranges. I'm very happy because all the Sunday I spent to try to solve it by Dlookup which is excellent for forms. But the solution was more simply without any special function! But forum helped by finding mistake and telling "don't use Dlookup". Now I'm adapting this to my application. Below picture with solved issue - I hope. In the app there are some additional parameters which I will have to combine with this but at the moment it works. One remark - in the picture there is simply increasing number string to easy see, but in reality ranges are different.

 

[June7]

Then what is "First([Value1])"? First() is an aggregate function.

 

[Rafal]

Then what is "First([Value1])"? First() is an aggregate function.

You right.