使用WHERE条件的UPDATE引用其他表中的字段 [英] UPDATE with WHERE conditions reference to fields in other table
问题描述
在我的访问数据库中有一个名为？T_Inv_Transaction的事务表？具有创建Job_ID时生成的成分数量记录。
此表中与我的问题相关的字段包括以下内容。没有为此表分配主键。
Job_ID
ProdID
ProdV
BatchQTY
UOM
每次用户点击生产按钮时，都会按程序创建Job_ID。与特定Job_ID，ProdID和ProdV相关联的BatchQTY和UOM被插入到此表中以用于跟踪目的。
我需要开发的下一步是更新表中的库存？T_Inventory？。此表中的字段包括以下两个主键：Prod_ID和Prod_V。此库存表用作成分数量的存储位置，并在每批生产配方时更新。
Prod_ID
Prod_V
数量
UOM
我坚持使用UPDATE查询语句。 UPDATE和WHERE条件语句有没有办法引用其他表中的字段？在这种情况下，更新库存表中的数量（数量）（带有一定的数学运算），其中Prod_ID和Prod_V等于事务表中的字段ProdID和ProdV以及特定的Job_ID组？
In my access database there is a transaction table named ?T_Inv_Transaction? that has records of ingredient quantity generated when a Job_ID is created.
The fields in this table related to my question includes followings. There is no primary key assigned to this table.
Job_ID
ProdID
ProdV
BatchQTY
UOM
Each time when user hits a production button, a Job_ID is created by program. BatchQTY and UOM associated to specific Job_ID, ProdID, and ProdV are inserted into this table for tracking purpose.
The next step I need to develop is to update inventory in table ?T_Inventory?. Fields in this table include followings with two primary keys: Prod_ID and Prod_V. This inventory table serves as storage place for ingredient quantity and get updated every time when a batch of recipe is produced.
Prod_ID
Prod_V
Qty
UOM
I am stuck with the UPDATE query statement. Is there any way the UPDATE with WHERE condition statement can reference to fields in other table? In this case, update quantity (Qty) in inventory table (with certain math) where Prod_ID and Prod_V equal to fields ProdID and ProdV in transaction table and with specific group of Job_ID?
推荐答案
您应该可以通过加入两个表来实现这一目标。
这是一个粗略的例子：
You should be able to accomplish this by joining on the two tables.
Here is a rough example:
谢谢Frinavale。我会尝试这种方法。我不是VBA专家，因此需要一段时间来讲述结果。
Thanks Frinavale. I will try this approach. I am not a VBA expert so it will take a while to tell results.
这不应该是问题Joe，也不是Frinavale。这是SQL代码而不是VBA。她擅长SQL。与VBA不同; - ）
That shouldn''t be a problem Joe, nor is Frinavale. This is SQL code not VBA. She''s great at SQL. Not so much at VBA ;-)
这篇关于使用WHERE条件的UPDATE引用其他表中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
