Access update query subquery

Update Queries let you modify the values of a field or fields in a table. You can specify the records to modify by applying a filter WHERE clause and linking the table to other tables and queries.

UPDATE is useful when you want to change many records or when the records that you want to change are in multiple tables. You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the US:. In this example, all invoices with a balance due that are 30 days late have the LateStatus field updated to True.

Each contact may have a different value in its Contacted field depending on how many times it was contacted in the past. In this example, the address records are updated with the City and State value from a zip code lookup table.

If the record has a zip code that matches the lookup table, its City and State fields are replaced. Any built-in VBA function that returns a value can be used as the update value. Any public function that you create in a standard module can be used in a query. Your field is updated with the return value of the function. If your function takes parameters, you can pass field values as parameters and the function executes for each record that is processed.

If an Update Query fails to modify the table's data, the first thing to verify is that the underlying table is updateable. Simply open the table and manually try to edit the field. If you can't do it manually, the query can't make the change either.

This can be due to several reasons:. Assuming you can edit your table, your query may fail and display this error message when you run it:. Operation Failed. If so, check out our paper on Error Operation must use an updatable query: Dealing with Non-Updateable Queries and the Use of Temporary Tables in Microsoft Access which discusses some of the issues and workarounds.

They are extremely powerful and eliminate a lot of manual updates and unnecessary coding. They are also much easier to maintain and debug than module code.

When confronted with the limitations of what you can do with SQL Syntax, you can supplement Access update queries with your own functions letting Access take care of updating the records, while you focus on the calculations. Latest Newsletter Sign up. Blog with us and subscribe to our RSS feed. Follow us on Twitter. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc.

The information provided in this document is provided "as is" without warranty of any kind. The most popular Microsoft Access Number Cruncher. To see a list of majors and their minimum GPAs for which any student with that major does not meet the minimum GPA, you may use the following query:. For example, the following query returns a list of products that are found in at least one existing order:.

You can also preface IN with NOT, to verify that a value in the current row of the main query is not part of the set that the subquery returns. For example, the following query returns a list of orders with order dates that were processed by employees who are not sales representatives:.

Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue.

Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical.

Not enough information. Not enough pictures. Any additional feedback? I also did not know any non zero value resolves as True, thanks for that! I think I am understanding your issue now. You need to join the results of your query with the table you want to update: Expand Select Wrap Line Numbers. AS y ON Assets. Asset SET Assets. Below shows my current progress. Current error: "Operation must use an updateable query". I read somewhere previously that aggregate functions could cause errors in Update queries?

Believe it or not, that was my fear that it would not be updateable. You may have to use a VBA solution. Yikes, I fear that is above my knowledge at the moment. Why would it not be updateable? The subquery yields a one to one output, seems strange that this is the case. However, based upon your general SQL skills, I do not believe what we would be trying to do is very far from your current skill set grasp. We can always help walk you through it. Rabbit 12, Expert Mod 8TB. Your SQL doesn't look like it's formatted correctly and there are probably some extraneous joins in there; unless of course the join is required to filter down the data.

See if this works. As always, be safe and save a copy of the table data when testing code that updates data. Rabbit, This yields the same error as the code in post 7: "Operation must use an updateable query". Since your non-Update query yields the appropriate results of: Asset; IsSealed, you create a recordset based upon those results. Then you update the Assets Table, based upon those results. Oddly enough, you "might" be able to update using an update query in VBA I've had some strange experiences that have allowed me to do that.

Create a form with a Command Button On it named cmdAssetsSealed Otherwise, you create a smaller update query based upon your results and update for each record.

I have attempted to understand and modify as necessary. Currently getting an error "Run-Time Error '': Too few parameters. Expected 1. I believe I have resolved the error by using single quotes within the definition of strSQL. Highlights "Call CurrentDb. I'm not very familiar with VBA troubleshooting methods, any thoughts appreciated. Let me take a look in greater detail for the other error. Very interesting! This worked perfectly! Thought nothing happened as VBA does not prompt a user confirmation, but it updated to the table exactly as expected.

Thanks for your guidance! Now to add the MatMov and PO sources to the original query. Should I expect any issues related to character limit when I apply the full query? If I remember correctly, the String limit is either 5, or 65, characters. I believe the maximum length for a string in VBA is 64K 65, However, not everything that supports strings can necessarily handle the full size of such a string.

That said, 64K is pretty large so I doubt the limit will cause you any problems.