469,306 Members | 1,987 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

How do I make an UPDATE query with subquery?

Seth Schrock
2,962 Expert 2GB
After searching online for awhile, I have discovered many people with this same question, but no answers. I'm trying to create an update query that sets the value of the field to the result of a subquery. However, when I try to run the query I get an error message that says "Operation must use an updateable query." My research tells me that it is because of the subquery. Here is what I have so far:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblACHFiles INNER JOIN tblInvoices ON tblACHFiles.InvoiceID = tblInvoices.InvoiceID 
  2. SET tblACHFiles.InvoiceID = (SELECT InvoiceID FROM tblInvoices WHERE CustomerID = Forms!frmCustomer!CustomerID
  3. AND BeginDate = Forms!frmCustomer!txtStartDate AND EndDate = Forms!frmCustomer!txtEndDate)
  4. WHERE (((tblACHFiles.[ACHCompanyID])=[Forms]![frmCustomer]![CustomerID]) 
  5. AND ((tblACHFiles.[EffectiveDate])>=[Forms]![frmCustomer]![txtStartDate] 
  6. And (tblACHFiles.[EffectiveDate])<=[Forms]![frmCustomer]![txtEndDate]));
  7.  
I suppose that I could replace the subquery with a DLookup() function, but I don't know the syntax to have three criteria. As those are the only two options that I know of, I don't know how to procede.
Nov 10 '11 #1

✓ answered by NeoPa

Check out Reasons for a Query to be Non-Updatable. This is Jet-specific, so you needn't worry that all SQL engines necessarily behave that way.

If you are unable to make your underlying query updatable then the only recourse is to create the data in a table then run the UPDATE query using that table. Probably best to delete or clear the temporary table after use I would suggest.

5 10468
NeoPa
32,173 Expert Mod 16PB
Check out Reasons for a Query to be Non-Updatable. This is Jet-specific, so you needn't worry that all SQL engines necessarily behave that way.

If you are unable to make your underlying query updatable then the only recourse is to create the data in a table then run the UPDATE query using that table. Probably best to delete or clear the temporary table after use I would suggest.
Nov 11 '11 #2
Seth Schrock
2,962 Expert 2GB
After reading the information in the link, I'm not sure where my query falls. However I did just come up with an idea. Would it be possible to use this as the set value:
Expand|Select|Wrap|Line Numbers
  1. SET tblACHFiles.InvoiceID = DLookup("InvoiceID", "qryFindInvoiceID")
qryFindInvoiceID would be what I did have in the subquery and would return only one record. Does this fall into the temporary table category or would this still be non-updatable? I'm not at work so I can't try it until Monday.

I've never worked with temporary tables so I'm not sure what this entails.
Nov 11 '11 #3
NeoPa
32,173 Expert Mod 16PB
I would expect that to work Seth. I'm sure such techniques have been used before successfully. Still best to test it of course, but I'd expect it to work.
Nov 11 '11 #4
Seth Schrock
2,962 Expert 2GB
It worked. Thanks for your help.
Nov 12 '11 #5
NeoPa
32,173 Expert Mod 16PB
Pleased to hear it Seth :-)
Nov 12 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Mark Carlyle via AccessMonster.com | last post: by
3 posts views Thread by Slower Than You | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.