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?

There's quite a lot problems with this question. 1) In your IIF statement you've used semi colons (;) rather than commas (,). 2) You are probably getting a #Name? error on your form, because the fields you've referenced in your IFF statement don't exist on the form (i.e. you haven't given the form a Record Source that refers to a table/query containing those fields). 3) In the Provvigioni table you've said that Vendita has a Costo of "1,5"... is it meant to be this or is it 1.5? 4) Your IIF statement would be better suited to a query (see my answer below). — Matt Hall

Matt Hall Matt Hall · Accepted Answer · 2015-05-05T16:01:02

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:

enter image description here

Add both your tables (double-click them):

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

...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:

enter image description here

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

enter image description here

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

enter image description here

...and name it something:

enter image description here

You can then go to Create > Form Design:

enter image description here

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:

enter image description here

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):

enter image description here

Hope this helps!

Calculated field with value from another table in Microsoft Access

2 Answers