Access 2003-根据选择查询结果运行更新查询 [英] Access 2003 - Running an update query based on select query results

问题描述

我目前有一个显示选择查询的子窗体.我想用来自表单的信息更新子表单中显示的表B的所有记录.子窗体不是必需的.我只是在使用它来确保我的选择查询正确显示.

I currently have a subform that displays a select query. I want to update all the records of Table B that are showing in the subform with information from the form. The subform is not necessary. I was just using it to make sure my select query was displaying correctly.

表A具有3列(OID，Project_Number，Landowner)
表B具有4列(OID，电话号码，地址，年份)

Table A has 3 columns (OID, Project_Number, Landowner)
Table B has 4 columns (OID, PhoneNum, Address, Year)

这些表具有一对多关系.表A中的一个OID与表B中的许多OID相关

These tables have a one to many relationship. One OID in Table A relates to many in Table B

Table A
1          A10          Bill
2          B10          Sally
3          A10          Bill

Table B
1          555          123 blah st           2012
1                                             2013
2          111          456 aaa st            2012
3                                             2012

该表格允许用户输入填充表B的信息. 子表单显示记录列表，其中Project_Number，Landowner和Year等于表单上显示的记录

The form allows the user to enter information that populates Table B. The subform displays a list of records where Project_Number, Landowner, and Year are equal to the record showing on the form

For example.  If the form is showing
1         A10          Bill

the subform is showing
1         A10          Bill        2012
3         A10          Bill        2012

当我单击保存命令按钮时，我希望它运行更新查询，但是SQL命令出现问题.

When I click a save command button I would like it to run the update query but I'm having issues with the SQL command.

我的选择查询如下:

SELECT B.Project_Number, A.LANDOWNER, B.Year
FROM A INNER JOIN B ON A.OBJECTID = A.OBJECTID;

子窗体已设置
链接子字段:Project_Number；年;地主
链接主字段:B.Project_Number；年; A.Landowner

The subform is setup
Link Child Fields: Project_Number; Year; Landowner
Link Master Fields: B.Project_Number; Year; A.Landowner

我想要:

UPDATE B.PhoneNum, B.Address, B.Year
WHERE items found in my subform
WITH information from my form

忘记子窗体并通过单个更新查询来完成所有操作更容易吗?

Is it easier to forget the subform and do it all through a single update query?

UPDATE B SET B.phonenum = [New_Info]![PhoneNumCtrl], B.Address = [New_Info]![AddressCtrl]
WHERE [A]![Landowner] = The same landowner as the OID selected, [A]![Project_Number] = The same project number as the OID selected, [New_Info]![Year] = [B]![Year]

在此先感谢您的帮助！

现在一切正常.我想添加到djphatic的答案中.
执行此操作时，请确保添加[Forms]！[formname]！[controlname]

Everything is working now. I wanted to add to djphatic's answer.
When doing this make sure to add [Forms]![formname]![controlname]

推荐答案

根据控件在表单上的位置，您可能需要更改控件引用.

Depending on where the controls are on your form you may need to change the control reference.

使用查询构建器GUI创建一个选择查询，该查询包含您要更新的列，并使用表单上的控件过滤记录.完成此操作后，您可以将查询更改为更新查询，并在表单上设置控件的值.

Use the query builder GUI to create a select query which has the columns you wish to update and filter the records using the controls on your form. Once you have this you can change the query to an update query and set the values the controls on your form.

UPDATE  B
SET B.phonenum = [formname]![controlname], ...
FROM B JOIN A ON B.OID = A.OID
WHERE A.PROJECTID = [formname]![controlname]
AND B.YEAR = [formname]![controlname]

这篇关于Access 2003-根据选择查询结果运行更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！