MS Access Expression Builder Value from another Table

Question

I have two tables - tblMain and tblConstants. I want a column in tblMain that references a value in tblConstants, but expression builder doesn't allow it.

Example Data:

tblMain

 ID*  | dblLength | tblOverall
------------------------------
 1    | 100       | {expression}

tblConstants:

 Name*      | Val
--------------------
 dblSample  | 100

Sample expression:

[dblLength] + [tblConstants].[dblSample]

But this results in the following error:

All my searching has found that I could create relationships between the tables, but this requires me to duplicate the constants as columns in tblMain.

Is there a neater way to do this?

Answer 1

Then use a query. Apart from very special cases, avoid calculated fields.

In a select query, you can perform all sorts of calculations using fields from a single table or a set of joined tables.