calculated field in query

[ilpara87]

Hello everybody,


I ask you for help regarding a problem in calculating a field in an access query where the calculation formula is stored in a parametric table that I use in join. let me explain: I have a table in which there are two "key" columns and another 3 columns in which there are values

KEY_1	KEY_2	field_3	field_4	field_5
A	1	0,5	0,1	0,2
A	2	0,8	0,6	0,8
A	3	0,8	0,9	0,1
B	1	0,4	0,2	0,8
B	2	0,4	0,3	0,4

then I built a table in which I mapped the various formulas based on the combination of the first two key fields

KEY_1	KEY_2	FORMULA
A	1	[field_3] + [field_5]
A	2	[field_4] * [field_5]
A	3	[field_4] * 12,7
B	1	[field_3] + [field_4]
B	2	[field_3] / [field_4] * 7

How can I make a query in which by joining the two tables I get a new "value" field in which the formula contained in the "formula" field of the second column is calculated? currently the system returns me the string field with the contents of the "formula" field (eg "[field_3] + [field_5]") instead I would like it to return the result of the calculation 0.7 (0.5 + 0.2).


Can anyone help me out?
thanks

[Welshgasman]

You write a function to do just that.

Then use that in the query.

However your data does not look normalised?

[ilpara87]

how should i write the function? I have to write all the syntax of the sql or how? I made several attempts but failed as you suggested

[Welshgasman]

No, you would, or at least I would approach it by
Write a function that....
Received the five parameters key, key2, field3, field4 and field5
Then retrieve the formula for those keys.
Then split that formula into 3 parts, fieldnumber1, operand and fieldnumber2

Then do the math and return the result.

Might be easier if you just use 3 + 5, instead of field_3 + field_5, as you will need to retrieve the numbers from the string. Just one less step to take.