具有Microsoft Access中另一个表的值的计算字段 [英] Calculated field with value from another table in Microsoft Access

问题描述

我在一个表中有一个计算字段，该字段需要从另一个表中获取值，但是在表达式生成器窗口中，它不允许我从其他表中选择值. 然后，我尝试从表中创建一个表单，并将所需的功能放入源控件中，但它会触发该表单中的#Name?错误.

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.

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

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]和[Costo ivato 1 pezzo]是同一表(称为Prodotti)中的两个字段，其结构如下:

[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

另一个表(称为Provvigioni)就像

Tipo        Costo

Vendita       1,5
Prevendita    5

Provv_Vendita和Provv_Prevendita是两个查询，它们选择我需要的值，并具有如下结构

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?

推荐答案

我不是100％知道您在这里要做什么，但我认为最好向您展示如何在查询中链接2个表首先.

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:

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

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

在其中放置您的IIF语句；在其前面加上字段名称和冒号，例如:

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

...请注意，我已经整理了您的IFF声明，所以现在显示为:

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

...并为其命名:

然后您可以转到创建">表单设计":

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

希望这会有所帮助！

这篇关于具有Microsoft Access中另一个表的值的计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！