Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a Microsoft Access table called calc1 with fields:

  • calc_ID (primary key, number)
  • calc (text)

I have another table called res1 with:

  • res_ID (primary key)
  • calc_ID (number)
  • dimensionbefore (number)
  • dimensionafter (number)
  • result (calculated)

There is a relation, one calc1 to many res1, linking with calc_ID.

Is it possible to make the result field take its formula from the calc field in the calc1 table?

Example: For elongation the field calc is

([dimensionafter] - [dimensionbefore]) / ([dimensionbefore] * 100)

and calc_ID is 1. In the res1 table if the calc_ID is 1 then the result field would take the text from calc and make it its formula and return the result.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Create a query like this:

SELECT res1.res_ID, res1.calc_ID, res1.dimensionbefore, res1.dimensionafter,
       Eval(Replace(Replace([calc1].[calc],"[dimensionafter]",[dimensionafter]),"[dimensionbefore]",[dimensionbefore])) AS result
FROM res1 INNER JOIN calc1 ON res1.calc_ID = calc1.calc_ID;

The result field will return the right calculation result.

share|improve this answer
    
Thanks for helping Scott. Unfortunately I think I need some more guidance. When I paste this query line in "Expression" in the field property of the "result" field in the "res1" table I get a message like this: The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses. Unfortunately I do not understand what it mean. Is this where I should paste this query line? –  Francis Dumas Apr 12 '13 at 2:47
    
This is an SQL query. You should create a new query, click on "SQL View", delete anything from the query, and then paste the aforementioned SQL statement. Alternatly, you can create a query in the usual way, adding only the calculated field as follows: Result: Eval(Replace(Replace([calc1].[calc],"[dimensionafter]",[dimensionafter]),"[dimen‌​sionbefore]",[dimensionbefore])). –  naftalip Apr 14 '13 at 18:54
    
Thanks so much naftalip, this is fantastic. This is a new way of doing things that will make my database a lot better. –  Francis Dumas Apr 17 '13 at 0:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.