使用Microsoft Access中另一个表的值计算字段

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.

我在表中有一个计算字段,需要从另一个表中获取值,但在表达式生成器窗口中,它不允许我从其他表中选择值。然后,我尝试从表中创建一个表单,并在源代码控制中放入我需要的函数,但它会触发#Name?表单中的错误。

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

这是我放在源代码控制中的代码(Iff函数):

=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]是同一个表中的两个字段(称为Prodotti),其结构如下:

Stato      Costo Ivato 1 pezzo

Vendita         10
Prevendita      20

The other table (called Provvigioni) is like

另一张桌子(叫做Provvigioni)就像

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

Provv_Vendita和Provv_Prevendita是两个选择我需要的值并且结构如下的查询

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?

有没有办法从另一个表中获取字段并在第一个表的函数中使用?

2 个解决方案

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

我不是100%肯定你在这之后会有什么,但我认为最好先向你展示如何在查询中链接你的2个表格。

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:

然后关闭Show Table窗口;您现在应该在查询设计器中拥有表:

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

单击并从Stato拖动到Tipo;这应该在这些字段之间创建一个连接:

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:

把你的IIF声明放在那里;在它前面加上一个字段名和冒号,例如:

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

...请注意,我已经整理了你的IFF语句了,现在它读取:

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

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!

希望这可以帮助!

#2

Create a query.

创建一个查询。

Bring in the two tables and create a join between Stato and Tipo.

引入两个表并在Stato和Tipo之间创建连接。

Now you can select [Costo] and [Costo Ivato 1 pezzo]

现在你可以选择[Costo]和[Costo Ivato 1 pezzo]