MySQL 具有 Microsoft Access 中另一个表中的值的计算字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30053847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculated field with value from another table in Microsoft Access
提问by Ponzaro
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) :
这是我放在源代码管理（If 函数）中的代码：
=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_Venditaand Provv_Prevenditaare 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?
有没有办法从另一个表中获取该字段并在第一个表的函数中使用？
回答by Matt Hall
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 IIFstatement in there; precede it with a field name and colon, like this for example:
把你的IIF陈述放在那里；在它前面加上一个字段名称和冒号，例如：
...Note that I've tidied your IFFstatement 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!
希望这可以帮助！
回答by Gustav
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]
