How Can I Update a Field in Microsoft Access 2007 Using a SELECT Query?

Update a Field Using a SELECT Query in Microsoft Access 2007

In Microsoft Access 2007, it is possible to update a field within a table by utilizing the results of a SELECT query. While Access may not allow aggregates in UPDATE queries, it can execute them in SELECT queries.

To achieve the desired update, follow these steps:

1. Create a SELECT Query: Define a SELECT query that retrieves the desired value to be used in the update. For example:

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;

2. Save the SELECT Query: Name and save the SELECT query, such as "YourQuery."
3. Create a Make Table Query: Access does not allow UPDATE queries on queries, so a Make Table query is used to convert "YourQuery" into a table.

SELECT YourQuery.*
INTO MinOfTax_Code
FROM YourQuery

This will create a table named "MinOfTax_Code" with the results of "YourQuery."

4. Perform the UPDATE Query: Finally, execute an UPDATE query to update the desired field using the newly created table:

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

By following these steps, you can effectively use a SELECT query to update a field of a table in Microsoft Access 2007.

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