Build an expression referencing another table

Question

Hi

I am surprised this is so hard to find the answer to.

I just want to add a calculated field to access, with the value from another table. I have searched for syntax in google and the best I can come up with is: =Tables![Companies]![Active]

Where companies is the table and active is the field. However this comes up with an error

Please can someone point me towards syntax for referencing another table.

Also, is it not possible to get the expression builder to help with these tasks? I can get it to suggest syntax for anything other than a few built in constants and functions, and it only autofills field names in the same table you are currently editing in.

Answer 1

Hi,

I try to type a macro here which is more difficult than inserting or writing some VBA example code. ;-)

Your description wasn't very clear about table and field names which of course you have to change to your real names in my example. 

Open the Opportunities table in design view and create an OnInsert macro like this:

Look Up A Record In   Companies

     Where Condition    = Companies.Company=Opportunities .Company

     SetLocalVar

        Name varRating

        Expression = Companies.Rating

If varRating>"" Then

      EditRecord

         SetField

            Name   Opportunities.Type

            Value = varRating

      End EditRecord

End If

I tried to keep it simple here. You could and should do more If-checks about fields not being Null etc.

Answer 2

Hi

Does anyone had any advice about this issue or where I can find more information to help?

Answer 3

Thanks Karl and Scott.

Now I understand better, thanks.

Actually this is an access web app. I have read your blog and searched and the tables are related via lookup fields in the related table (https://support.office.com/en-us/article/Create-a-relationship-40c998dd-4875-4da4-98c8-8ac8f109b85b#\_\_create\_a\_relationship\_webapp)

So I have a main table (companies) and a related table (opportunities). This opp table is related via company name in a lookup field to the companies table.

There is a field in the company table called rating. When we make a new opportunity record, the rating value should be copied to the opportunity table field called opp.type by default. But this value can later be updated independently from the company.rating value.

In other words, the opp.type field starts as the company.rating value on creation, but later may be changed. One company can own many opportunities with different types.

From my limited research, it seems this should be done with an oninsert and maybe onupdate data macro. Is that the correct way?

If so, what should the syntax be? I tried to do this, but it came up with an error when I tried to add a new opportunity record.

Answer 4

if you are trying to use a Calculated datatype, as Karl pointed it, it won't work. And, it really shouldn't be done. What you should PROBABLY be doing (I say probably because you didn't give any background on what this table is used for), is including the CompanyID as a foreign key in the table. You can then reference the Active field in the Companies table using a Join, DLookup or other techniques. See my blog on Displaying data from Related tables.

Answer 5

Hi,

you can't find the syntax because as the documentation at office.comsays:

The calculation cannot include fields from other tables or queries.