How to Perform Update Query with Subquery in Access

How do I perform update query with subquery in Access?

That could be because Company_ID is not an existing field in VendorRegKeys OR Users.

EDIT:

UPDATE VendorRegKeys
INNER JOIN Users ON Users.User_ID = VendorRegKeys.CreatedBy_ID
SET VendorRegKeys.Company_ID = Users.Company_ID

Access Update Query w/ Subquery

Your query can be re-written to this:

UPDATE [DATA OUTPUT TABLE]
SET [DATA OUTPUT TABLE].[Shared Appropriation] = [dBranch] & " Shared Appropriation"
Where [DATA OUTPUT TABLE].[Journal Voucher ID] IN
  (
    SELECT [DATA OUTPUT TABLE].[Journal Voucher ID]
    FROM [DATA OUTPUT TABLE]
    GROUP BY [DATA OUTPUT TABLE].[Journal Voucher ID]
    HAVING
      (((First([DATA OUTPUT TABLE].dBranch)="Navy")
        AND
      (Last([DATA OUTPUT TABLE].dBranch)="USMC"))
      OR
      ((First([DATA OUTPUT TABLE].dBranch)="USMC")
        AND 
      (Last([DATA OUTPUT TABLE].dBranch)="Navy")))
  )

Use SELECT inside an UPDATE query

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!

Update query using a field from another Access database

You can use a subquery to work with the IN clause in updates. That way, it's explicit which table is in which database.

UPDATE Table1 INNER JOIN (SELECT * FROM Table2 IN "C:\Folder\Database.accdb") t2 ON Table1.ID=t2.ID 
SET Table1.Field1 = t2.Field2

Update with Select in Microsoft Access

First, MS Access uses UPDATE...JOIN as its syntax and not UPDATE...FROM for multiple tables in UPDATE queries. Second, Access requires update queries to be updateable where self-joins or subqueries are not updateable.

However, you can use domain function, DLookUp, to retrieve values from same table:

UPDATE [Table] t
SET t.[Name] = DLookUp("[Name]", "[Table]", 
                       "[Name] <> 'NoName' AND [Ref] = '" & t.[Ref] & "'")

Note: this solution will only work if using query inside MSAccess.exe (Office program) and not its backend database via ODBC/OLEDB. Domain functions are part of hte Access object library and not the Jet/ACE SQL Engine. And Ref is assumed to be a string value.

Access: Update query with count in subquery - error or all results

Create a temp table with counts:

SELECT customer, product, COUNT(customer) as count
INTO CustomerCounts
FROM Table2 
GROUP BY customer, product

Update join of new table with Table1:

UPDATE 
    Table1 t JOIN 
    CustomerCounts cc ON cc.customer = t.customer
       AND cc.product = t.product
SET t.count = cc.count

---

Related Topics