Use SELECT inside an UPDATE query

Solution 1:

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

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

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]

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

Solution 2:

I wrote about some of the limitations of correlated subqueries in Access/JET SQL a while back, and noted the syntax for joining multiple tables for SQL UPDATEs. Based on that info and some quick testing, I don't believe there's any way to do what you want with Access/JET in a single SQL UPDATE statement. If you could, the statement would read something like this:

UPDATE FUNCTIONS A
INNER JOIN (
  SELECT AA.Func_ID, Min(BB.Tax_Code) AS MinOfTax_Code
  FROM TAX BB, FUNCTIONS AA
  WHERE AA.Func_Pure<=BB.Tax_ToPrice AND AA.Func_Year= BB.Tax_Year
  GROUP BY AA.Func_ID
) B 
ON B.Func_ID = A.Func_ID
SET A.Func_TaxRef = B.MinOfTax_Code

Alternatively, Access/JET will sometimes let you get away with saving a subquery as a separate query and then joining it in the UPDATE statement in a more traditional way. So, for instance, if we saved the SELECT subquery above as a separate query named FUNCTIONS_TAX, then the UPDATE statement would be:

UPDATE FUNCTIONS
INNER JOIN FUNCTIONS_TAX
ON FUNCTIONS.Func_ID = FUNCTIONS_TAX.Func_ID
SET FUNCTIONS.Func_TaxRef = FUNCTIONS_TAX.MinOfTax_Code

However, this still doesn't work.

I believe the only way you will make this work is to move the selection and aggregation of the minimum Tax_Code value out-of-band. You could do this with a VBA function, or more easily using the Access DLookup function. Save the GROUP BY subquery above to a separate query named FUNCTIONS_TAX and rewrite the UPDATE statement as:

UPDATE FUNCTIONS
SET Func_TaxRef = DLookup(
  "MinOfTax_Code", 
  "FUNCTIONS_TAX", 
  "Func_ID = '" & Func_ID & "'"
)

Note that the DLookup function prevents this query from being used outside of Access, for instance via JET OLEDB. Also, the performance of this approach can be pretty terrible depending on how many rows you're targeting, as the subquery is being executed for each FUNCTIONS row (because, of course, it is no longer correlated, which is the whole point in order for it to work).

Good luck!

Solution 3:

I had a similar problem. I wanted to find a string in one column and put that value in another column in the same table. The select statement below finds the text inside the parens.

When I created the query in Access I selected all fields. On the SQL view for that query, I replaced the mytable.myfield for the field I wanted to have the value from inside the parens with

SELECT Left(Right(OtherField,Len(OtherField)-InStr((OtherField),"(")), 
            Len(Right(OtherField,Len(OtherField)-InStr((OtherField),"(")))-1) 

I ran a make table query. The make table query has all the fields with the above substitution and ends with INTO NameofNewTable FROM mytable