How Can I Use a SELECT Query's Result to Update a Table in Microsoft Access?

Using SELECT Within an UPDATE Query in Access

In Microsoft Access, executing updates in a database often involves using an UPDATE query. However, when an update requires a result calculated from a separate SELECT query, the straightforward approach may not suffice.

Consider two scenarios:

• Select Query:

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

UPDATE FUNCTIONS
SET FUNCTIONS.Func_TaxRef = [Result of Select query]

In Microsoft Access 2007, it's not possible to directly include aggregates (e.g., min, max) within an UPDATE query. However, a workaround involves utilizing the capabilities of SELECT and Make Table queries.

Step 1: Create a Query to Calculate Aggregate

Construct a query that calculates the minimum tax code from the TAX and FUNCTIONS tables based on specific criteria and groups the results by the Func_ID field:

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

Save this query as YourQuery.

Step 2: Convert Query to Table

Since UPDATE queries can only operate on tables, use a Make Table query to store the calculated results in a new table called MinOfTax_Code:

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

Step 3: Perform UPDATE Query

Finally, execute an UPDATE query that joins the Functions table with the MinOfTax_Code table and updates the Func_TaxRef field:

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

This workaround allows you to use the results of a SELECT query to update a field in another table in Microsoft Access 2007.

The above is the detailed content of How Can I Use a SELECT Query's Result to Update a Table in Microsoft Access?. For more information, please follow other related articles on the PHP Chinese website!