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)

• 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 <p>Save this query as YourQuery.</p><p><strong>Step 2: Convert Query to Table</strong></p><p>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:</p><pre class="brush:php;toolbar:false">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!