在UPDATE查询中使用SELECT [英] Use SELECT inside an UPDATE query

问题描述

如何使用Microsoft Access 2007中的SELECT查询结果UPDATE表的字段.

How can I UPDATE a field of a table with the result of a SELECT query in Microsoft Access 2007.

这是选择查询":

SELECT Min(TAX.Tax_Code) AS MinOfTax_Code
FROM TAX, FUNCTIONS

WHERE (((FUNCTIONS.Func_Pure)<=[Tax_ToPrice]) AND ((FUNCTIONS.Func_Year)=[Tax_Year]))

GROUP BY FUNCTIONS.Func_ID;

这是更新查询:

UPDATE FUNCTIONS

 SET FUNCTIONS.Func_TaxRef = [Result of Select query]

推荐答案

好吧，看来Access无法在UPDATE查询中进行聚合.但是它可以在SELECT查询中进行汇总.因此，使用以下定义创建查询:

Well, it looks like Access can't do aggregates in UPDATE queries. But it can do aggregates in SELECT queries. So create a query with a definition like:

SELECT func_id, min(tax_code) as MinOfTax_Code
FROM Functions
INNER JOIN Tax 
ON (Functions.Func_Year = Tax.Tax_Year) 
AND (Functions.Func_Pure <= Tax.Tax_ToPrice) 
GROUP BY Func_Id

并将其另存为YourQuery.现在，我们必须解决另一个访问限制. UPDATE查询不能对查询进行操作，但是可以对多个表进行操作.因此，让我们使用Make Table查询将查询变成一个表:

And save it as YourQuery. Now we have to work around another Access restriction. UPDATE queries can't operate on queries, but they can operate on multiple tables. So let's turn the query into a table with a Make Table query:

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

这会将视图的内容存储在名为MinOfTax_Code的表中.现在，您可以执行UPDATE查询:

This stores the content of the view in a table called MinOfTax_Code. Now you can do an UPDATE query:

UPDATE MinOfTax_Code 
INNER JOIN Functions ON MinOfTax_Code.func_id = Functions.Func_ID 
SET Functions.Func_TaxRef = [MinOfTax_Code].[MinOfTax_Code]

在Access中执行SQL有点困难，我会为您的项目研究Sql Server Express Edition！

Doing SQL in Access is a bit of a stretch, I'd look into Sql Server Express Edition for your project!

这篇关于在UPDATE查询中使用SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！