Calculated field with value from another table in Microsoft Access

Question

I have a calculated field in a table that needs to take the value from another table but in the expression generator window it doesn't allow me to choose values from other tables. Then, I tried to create a form from the table, and put in the source control the function that I need but it triggers a #Name? Error in the form.

This is the code that I put in the source control (Iff function) :

=IIf([Stato]="Vendita";[Costo ivato 1 pezzo]+(([Costo ivato 1 pezzo]/100)*[Provv_Vendita]![Costo]);[Costo ivato 1 pezzo]+(([Costo ivato 1 pezzo]/100)*[Provv_Prevendita]![Costo]))

[Stato] and [Costo ivato 1 pezzo] are two fields that are in the same table (called Prodotti) that is structured like this:

Stato      Costo Ivato 1 pezzo

Vendita         10
Prevendita      20

The other table (called Provvigioni) is like

Tipo        Costo

Vendita       1,5
Prevendita    5

Provv_Vendita and Provv_Prevendita are two queries that select the values that I need and are structured like this

SELECT Provvigioni.Costo
FROM Provvigioni
WHERE Provvigioni.Tipo="Vendita";

Is there a way to take the field from the other table and use in a function on the first table?

Answer 1

I'm not 100% sure what you're after here, but I thought it would be best to show you how to link your 2 tables in a query first..

Go to Create > Query Design:

Add both your tables (double-click them):

Then close the Show Table window; you should have your tables in the Query Designer now:

Click and drag from Stato to Tipo; this should create a join between these fields:

Double-click on all your fields in each table so that they will appear in the query:

In the next available field for your query, right-click in the "Field" box and select "Zoom":

Put your IIF statement in there; precede it with a field name and colon, like this for example:

...Note that I've tidied your IFF statement up a bit, so it now reads:

MyCalculatedField: IIf([Stato]="Vendita",[Costo ivato 1 pezzo]+(([Costo ivato 1 pezzo]/100)*[Costo]),[Costo ivato 1 pezzo]+(([Costo ivato 1 pezzo]/100)*[Costo]))

Click OK on the "Zoom" window. Click "Run" to see the result of your query:

Here's what the query results in (check your calculated field is what you expect it to be):

If you want the result of this query to populate data in a form then you should save the query...

...and name it something:

You can then go to Create > Form Design:

In the Property Sheet on the right-hand side, go to the "Data" tab and use the drop-down next to Record Source to select the query you just saved:

If you go to Design > Add Existing Fields, you should be able to bring in the fields from your Query for use in your Form (Double-click or drag them to make appear on your Form):

Hope this helps!