424,279 Members | 1,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,279 IT Pros & Developers. It's quick & easy.

Update with subquery - MS Access

P: 19
I am attempting update a Yes/No field (Sealed) in a table called [Assets] from a subquery.

The subquery results contain the ID from [Assets] and the desired result of the field to be updated, as such:

Expand|Select|Wrap|Line Numbers
  1. Asset   Sealed
  2.   1       0
  3.   2      -1
  4.   3       0
I have tried a few options, including WHERE or FROM clauses, but can't achieve the appropriate result. The following displays my closest attempt (using WHERE EXISTS). When I run the update query, I get a prompt to enter a value for [IsSealed].

Expand|Select|Wrap|Line Numbers
  1. UPDATE [Assets] SET Sealed = IsSealed
  2. WHERE EXISTS (
  3. SELECT y.Asset AS Asset, 
  4.        IIF(y.TotalSeals>0,Yes,No) AS IsSealed
  5. FROM (
  6. SELECT x.Asset, 
  7.        SUM(IIF(x.BOMTypes="Seal",1,0)) AS BOMSeals, 
  8.        SUM(IIF(x.MatMovTypes="Seal",1,0)) AS MMSeals, 
  9.        SUM(IIF(x.POTypes="Seal",1,0)) AS POSeals, 
  10.        (BOMSeals + MMSeals + POSeals) AS TotalSeals 
  11. FROM (
  12. SELECT [Data - Assets].ID AS Asset, 
  13.        [Data - Inventory].Type AS BOMTypes, 
  14.        NULL AS MatMovTypes, NULL AS POTypes, NULL AS TotalSeals 
  15. FROM [Data - Inventory] INNER JOIN ([Data - Assets] INNER JOIN [Data - BOM] 
  16. ON [Data - Assets].ID = [Data - BOM].Asset) 
  17. ON [Data - Inventory].ID = [Data - BOM].Component 
  18.  
  19. UNION ALL 
  20. SELECT [Data - Assets].ID AS Asset, NULL AS BOMTypes, 
  21.        [Data - Inventory].Type AS MatMovTypes, 
  22.        NULL AS POTypes, NULL AS TotalSeals 
  23. FROM ([Data - Assets] INNER JOIN [Data - WO] 
  24. ON [Data - Assets].ID = [Data - WO].Asset) 
  25. INNER JOIN ([Data - Inventory] INNER JOIN [Data - MatMov] 
  26. ON [Data - Inventory].ID = [Data - MatMov].Component) 
  27. ON [Data - WO].ID = [Data - MatMov].WorkOrder 
  28.  
  29. UNION ALL 
  30. SELECT [Data - Assets].ID AS Asset, 
  31.        NULL AS BOMTypes, NULL AS MatMovTypes, 
  32.        [Data - Inventory].Type  AS POTypes, NULL AS TotalSeals 
  33. FROM ([Data - Assets] INNER JOIN [Data - WO] 
  34. ON [Data - Assets].ID = [Data - WO].Asset) 
  35. INNER JOIN ([Data - Inventory] INNER JOIN [Data - PO] 
  36. ON [Data - Inventory].ID = [Data - PO].Component) 
  37. ON [Data - WO].ID = [Data - PO].WorkOrder  
  38. )  AS x 
  39. GROUP BY x.Asset
  40. )  AS y);
  41.  
Jan 14 '19 #1

✓ answered by twinnyfo

It is a typo from my original.

Change to Line 25 below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAssetSealed_DblClick(Cancel As Integer)
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset
  5.     Dim strSQL As String
  6.  
  7.     strSQL = _
  8.         "SELECT [Data - Assets].ID AS Asset, " & _
  9.         "SUM(IIf([Data - Inventory].Type = 'Seal', 1, 0)) AS IsSealed " & _
  10.         "FROM [Data - Inventory] " & _
  11.         "INNER JOIN ([Data - Assets] " & _
  12.         "INNER JOIN [Data - BOM] " & _
  13.         "ON [Data - Assets].ID = [Data - BOM].Asset) " & _
  14.         "ON [Data - Inventory].ID = [Data - BOM].Component " & _
  15.         "GROUP BY [Data - Assets].ID;"
  16.     Set db = CurrentDb()
  17.     Set rst = db.OpenRecordset(strSQL)
  18.     With rst
  19.         If Not (.BOF And .EOF) Then
  20.             Call .MoveFirst
  21.             Do While Not .EOF
  22.                 strSQL = _
  23.                     "UPDATE [Data - Assets] " & _
  24.                     "SET Sealed = " & !IsSealed & _
  25.                     " WHERE [Data - Assets].ID = " & !Asset
  26.                     Call CurrentDb.Execute(Query:=strSQL)
  27.                 Call .MoveNext
  28.             Loop
  29.         End If
  30.         Call .Close
  31.     End With
  32.     Set rst = Nothing
  33.     Set db = Nothing
  34.  
  35. End Sub
Spaces can make or break you!

Share this Question
Share on Google+
18 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,886
Barmaximus,

Although you state you are not having success, you do not exactly describe that non-success. Is there an error being generated? Are you returning the incorrect results? Are the results correct but the Update is not occurring?

These are the things inquiring minds want to know. And we need to know them in order to guide you through a solution.
Jan 15 '19 #2

P: 19
Twinnyfo, I apologize for the lack of description. I have updated my post to show my closest attempt. It seems the update query is not recognizing IsSealed from the subquery with this method.
Jan 15 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 2,886
1. It really seems like you are trying to overly complexify this.

2. There is a LOT that I can say is probably not right about your tables, but we can talk about that later.

3. Let's move toward a solution:

Think of it this way. You only need to update assests to True is there is a value of "Seal" in BOMTypes, MatMovTypes or POTypes. Otherwise, update to False.

Why not work some of the evaluation at the earliest level?

Expand|Select|Wrap|Line Numbers
  1. SELECT [Data - Assets].ID AS Asset, 
  2.        IIf([Data - Inventory].Type = "Seal", 1, 0) AS IsSealed
  3. FROM [Data - Inventory] 
  4. INNER JOIN ([Data - Assets] 
  5. INNER JOIN [Data - BOM] 
  6. ON [Data - Assets].ID = [Data - BOM].Asset) 
  7. ON [Data - Inventory].ID = [Data - BOM].Component;
And so on for your other tables.

Now, when you Sum and Group By Asset, you can assign the Assets.Sealed using the IsSealed value. A Value of 0 always resolves to False. Any other value always resolves to True.

This prevents the need for a sub sub query.

(I think)
Jan 15 '19 #4

P: 19
In an earlier version of this query, I was interested in summing "Seals" separately by data source to assess the nature of the qualification. This is not the case any longer. I have recreated the query as suggested by using the Sum and Group By and it works like a charm!

However, I am still a bit confused on the update portion "assign the Assets.Sealed using the IsSealed value". Can you elaborate? From my understanding, this would still be a subquery within some sort of Update query, for which I'm still confused about the syntax.

I also did not know any non zero value resolves as True, thanks for that!
Jan 15 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 2,886
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
  1. UPDATE Assets 
  2. INNER JOIN (SELECT .... ) AS y 
  3. ON Assets.ID = y.Asset
  4. SET Assets.Sealed = IsSealed;
Of course you must insert your SQL for y, but this shows you the idea.

Hope this hepps!
Jan 15 '19 #6

P: 19
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?

Expand|Select|Wrap|Line Numbers
  1. UPDATE [Data - Assets]
  2. INNER JOIN (SELECT [Data - Assets].ID AS Asset, 
  3. SUM(IIf([Data - Inventory].Type = "Seal", 1, 0)) AS IsSealed 
  4. FROM [Data - Inventory] 
  5. INNER JOIN ([Data - Assets] 
  6. INNER JOIN [Data - BOM] 
  7. ON [Data - Assets].ID = [Data - BOM].Asset) 
  8. ON [Data - Inventory].ID = [Data - BOM].Component 
  9. GROUP BY [Data - Assets].ID)  AS y 
  10. ON [Data - Assets].ID = y.Asset 
  11. SET [Data - Assets].Sealed = IsSealed;
I appreciate your patience. I'm still new to this and have found Bytes to be an extremely valuable resource to my self-learning. :)
Jan 15 '19 #7

twinnyfo
Expert Mod 2.5K+
P: 2,886
Believe it or not, that was my fear that it would not be updateable.

You may have to use a VBA solution.
Jan 15 '19 #8

P: 19
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.
Jan 15 '19 #9

twinnyfo
Expert Mod 2.5K+
P: 2,886
I’ve never quite understood it myself. 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. Although we don’t consider this forum to be a “tutorial” site, we have made exceptions for those who show that they are willing to put in the effort.
Jan 15 '19 #10

Rabbit
Expert Mod 10K+
P: 12,298
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.

Expand|Select|Wrap|Line Numbers
  1. UPDATE [Data - Assets]
  2. INNER JOIN (
  3.  SELECT [Data - BOM].Asset, 
  4.  COUNT(*) AS IsSealed 
  5.  FROM [Data - Inventory] 
  6.  INNER JOIN [Data - BOM] 
  7.  ON [Data - Inventory].ID = [Data - BOM].Component 
  8.  WHERE [Data - Inventory].Type = "Seal"
  9.  GROUP BY [Data - BOM].Asset)  AS y 
  10. ON [Data - Assets].ID = y.Asset 
  11. SET [Data - Assets].Sealed = IsSealed;
Jan 15 '19 #11

P: 19
Rabbit, This yields the same error as the code in post #7: "Operation must use an updateable query".

twinnyfo, any suggestions on where to begin with a VBA approach?
Jan 15 '19 #12

twinnyfo
Expert Mod 2.5K+
P: 2,886
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.

Skeleton Code:

Expand|Select|Wrap|Line Numbers
  1. Create Recordset of Assets/IsSealed
  2.  
  3. Cycle through these records
  4.  
  5.     Create SQL string based upon the record
  6.  
  7.     Execute an update Query
  8.  
  9. Next Record
I understand you may have no clue where to start. I will offer this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdAssestSealed_Click()
  5.     Dim db As DAO.Database
  6.     Dim rst As DAO.Recordset
  7.     Dim strSQL As String
  8.  
  9.     strSQL = "[Your Query to get the Assets and IsSealed"]"
  10.  
  11.     Set db = CurentDB()
  12.     Set rst = db.OpenRecordset(strSQL)
  13.     With rst
  14.         If Not (.BOF And .EOF) Then
  15.             Call .MoveFirst
  16.             Do While Not .EOF
  17.                 strSQL = _
  18.                     "UPDATE Assets " & _
  19.                     "SET Sealed = " & !IsSealed & _
  20.                     "WHERE ID = " & !Asset
  21.                     Call CurrentDb.Execute(Query:=strSQL)
  22.                 Call .MoveNext
  23.             Loop
  24.         End If
  25.         Call .Close
  26.     End With
  27.     Set rst = Nothing
  28.     Set db = Nothing
  29.  
  30. End Sub
I can't guarantee this will work, because I don't know all the details of your DB tables, etc.

Also, rather than just "cut and paste", take some time and look at the logic of the code and see if you can understand what it is trying to do.

I'm glad to help guide you through any questions you may have.
Jan 15 '19 #13

P: 19
I have attempted to understand and modify as necessary.
Currently getting an error "Run-Time Error '3061': Too few parameters. Expected 1.

Highlights line 10 "Set rst = db.OpenRecordset(strSQL)" on debug

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAssetSealed_DblClick(Cancel As Integer)
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset
  5.     Dim strSQL As String
  6.  
  7.     strSQL = "SELECT [Data - Assets].ID AS Asset, SUM(IIf([Data - Inventory].Type = " & "Seal" & ", 1, 0)) AS IsSealed FROM [Data - Inventory] INNER JOIN ([Data - Assets] INNER JOIN [Data - BOM] ON [Data - Assets].ID = [Data - BOM].Asset) ON [Data - Inventory].ID = [Data - BOM].Component GROUP BY [Data - Assets].ID;"
  8.     Set db = CurrentDb()
  9.     Set rst = db.OpenRecordset(strSQL)
  10.     With rst
  11.         If Not (.BOF And .EOF) Then
  12.             Call .MoveFirst
  13.             Do While Not .EOF
  14.                 strSQL = _
  15.                     "UPDATE [Data - Assets] " & _
  16.                     "SET Sealed = " & !IsSealed & _
  17.                     "WHERE [Data - Assets].ID = " & !Asset
  18.                     Call CurrentDb.Execute(Query:=strSQL)
  19.                 Call .MoveNext
  20.             Loop
  21.         End If
  22.         Call .Close
  23.     End With
  24.     Set rst = Nothing
  25.     Set db = Nothing
  26.  
  27. End Sub
My guess is that this stems from my definition of strSQL and my handling of a string within a string, but I'm not sure.

Also, is there a limit to the length of string accepted by the strSQL variable?
Jan 15 '19 #14

P: 19
I believe I have resolved the 3061 error by using single quotes within the definition of strSQL. However,I'm now getting Error 3075 (Invalid operator) - Syntax error in query expression '0WHERE [Data - Assets].ID = 5706'. Note the odd 0 before WHERE. Highlights "Call CurrentDb.Execute(Query:=strSQL)" on debug. I'm not very familiar with VBA troubleshooting methods, any thoughts appreciated.
4 Weeks Ago #15

twinnyfo
Expert Mod 2.5K+
P: 2,886
Yes - I read this this morning and did not get a chance to advise about the single/double quote issue.

Let me take a look in greater detail for the other error.
4 Weeks Ago #16

twinnyfo
Expert Mod 2.5K+
P: 2,886
It is a typo from my original.

Change to Line 25 below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAssetSealed_DblClick(Cancel As Integer)
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset
  5.     Dim strSQL As String
  6.  
  7.     strSQL = _
  8.         "SELECT [Data - Assets].ID AS Asset, " & _
  9.         "SUM(IIf([Data - Inventory].Type = 'Seal', 1, 0)) AS IsSealed " & _
  10.         "FROM [Data - Inventory] " & _
  11.         "INNER JOIN ([Data - Assets] " & _
  12.         "INNER JOIN [Data - BOM] " & _
  13.         "ON [Data - Assets].ID = [Data - BOM].Asset) " & _
  14.         "ON [Data - Inventory].ID = [Data - BOM].Component " & _
  15.         "GROUP BY [Data - Assets].ID;"
  16.     Set db = CurrentDb()
  17.     Set rst = db.OpenRecordset(strSQL)
  18.     With rst
  19.         If Not (.BOF And .EOF) Then
  20.             Call .MoveFirst
  21.             Do While Not .EOF
  22.                 strSQL = _
  23.                     "UPDATE [Data - Assets] " & _
  24.                     "SET Sealed = " & !IsSealed & _
  25.                     " WHERE [Data - Assets].ID = " & !Asset
  26.                     Call CurrentDb.Execute(Query:=strSQL)
  27.                 Call .MoveNext
  28.             Loop
  29.         End If
  30.         Call .Close
  31.     End With
  32.     Set rst = Nothing
  33.     Set db = Nothing
  34.  
  35. End Sub
Spaces can make or break you!
4 Weeks Ago #17

P: 19
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?
4 Weeks Ago #18

twinnyfo
Expert Mod 2.5K+
P: 2,886
If I remember correctly, the String limit is either 5,000 or 65,535 characters.
4 Weeks Ago #19

Post your reply

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