How Can I Update a Field in Microsoft Access Using the Result of a SELECT Query with Aggregates?

Update Query Using SELECT Subquery

When working with Microsoft Access 2007, updating a field with the result of a SELECT query presents challenges. Although Access supports SELECT queries within UPDATE queries, it prohibits aggregates within the UPDATE portion.

Consider the following example:

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]

To resolve this issue, a workaround is necessary.

1. Convert SELECT Query to Table:

Create a query named 'YourQuery' with the following definition:

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

Then, execute a Make Table query:

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

This creates a table named MinOfTax_Code containing the results of the SELECT query.

2. Perform UPDATE Query:

Now, perform the UPDATE query:

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

By breaking down the aggregation into a separate table, Access allows for the successful updating of the Functions table with the result of the SELECT query.

The above is the detailed content of How Can I Update a Field in Microsoft Access Using the Result of a SELECT Query with Aggregates?. For more information, please follow other related articles on the PHP Chinese website!