Update with subquery - MS Access

[Barmaximus]

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:

Asset   Sealed
  1       0
  2      -1
  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].

UPDATE [Assets] SET Sealed = IsSealed
WHERE EXISTS (
SELECT y.Asset AS Asset, 
       IIF(y.TotalSeals>0,Yes,No) AS IsSealed
FROM (
SELECT x.Asset, 
       SUM(IIF(x.BOMTypes="Seal",1,0)) AS BOMSeals, 
       SUM(IIF(x.MatMovTypes="Seal",1,0)) AS MMSeals, 
       SUM(IIF(x.POTypes="Seal",1,0)) AS POSeals, 
       (BOMSeals + MMSeals + POSeals) AS TotalSeals 
FROM (
SELECT [Data - Assets].ID AS Asset, 
       [Data - Inventory].Type AS BOMTypes, 
       NULL AS MatMovTypes, NULL AS POTypes, NULL AS TotalSeals 
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 
 
UNION ALL 
SELECT [Data - Assets].ID AS Asset, NULL AS BOMTypes, 
       [Data - Inventory].Type AS MatMovTypes, 
       NULL AS POTypes, NULL AS TotalSeals 
FROM ([Data - Assets] INNER JOIN [Data - WO] 
ON [Data - Assets].ID = [Data - WO].Asset) 
INNER JOIN ([Data - Inventory] INNER JOIN [Data - MatMov] 
ON [Data - Inventory].ID = [Data - MatMov].Component) 
ON [Data - WO].ID = [Data - MatMov].WorkOrder 
 
UNION ALL 
SELECT [Data - Assets].ID AS Asset, 
       NULL AS BOMTypes, NULL AS MatMovTypes, 
       [Data - Inventory].Type  AS POTypes, NULL AS TotalSeals 
FROM ([Data - Assets] INNER JOIN [Data - WO] 
ON [Data - Assets].ID = [Data - WO].Asset) 
INNER JOIN ([Data - Inventory] INNER JOIN [Data - PO] 
ON [Data - Inventory].ID = [Data - PO].Component) 
ON [Data - WO].ID = [Data - PO].WorkOrder  
)  AS x 
GROUP BY x.Asset
)  AS y);
 

[twinnyfo]

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.

[Barmaximus]

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.

[twinnyfo]

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 if 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?

SELECT [Data - Assets].ID AS Asset, 
       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;

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)

[Barmaximus]

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!

[twinnyfo]

I think I am understanding your issue now.

You need to join the results of your query with the table you want to update:

UPDATE Assets 
INNER JOIN (SELECT .... ) AS y 
ON Assets.ID = y.Asset
SET Assets.Sealed = IsSealed;

Of course you must insert your SQL for y, but this shows you the idea.

Hope this hepps!

[Barmaximus]

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?

UPDATE [Data - Assets]
INNER JOIN (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)  AS y 
ON [Data - Assets].ID = y.Asset 
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. :)

[twinnyfo]

Believe it or not, that was my fear that it would not be updateable.

You may have to use a VBA solution.

[Barmaximus]

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.

[twinnyfo]

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.

[Rabbit]

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.

UPDATE [Data - Assets]
INNER JOIN (
 SELECT [Data - BOM].Asset, 
 COUNT(*) AS IsSealed 
 FROM [Data - Inventory] 
 INNER JOIN [Data - BOM] 
 ON [Data - Inventory].ID = [Data - BOM].Component 
 WHERE [Data - Inventory].Type = "Seal"
 GROUP BY [Data - BOM].Asset)  AS y 
ON [Data - Assets].ID = y.Asset 
SET [Data - Assets].Sealed = IsSealed;

[Barmaximus]

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?

[twinnyfo]

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:

Create Recordset of Assets/IsSealed
 
Cycle through these records
 
    Create SQL string based upon the record
 
    Execute an update Query
 
Next Record

I understand you may have no clue where to start. I will offer this:

Option Compare Database
Option Explicit
 
Private Sub cmdAssestSealed_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    strSQL = "[Your Query to get the Assets and IsSealed"]"
 
    Set db = CurentDB()
    Set rst = db.OpenRecordset(strSQL)
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveFirst
            Do While Not .EOF
                strSQL = _
                    "UPDATE Assets " & _
                    "SET Sealed = " & !IsSealed & _
                    "WHERE ID = " & !Asset
                    Call CurrentDb.Execute(Query:=strSQL)
                Call .MoveNext
            Loop
        End If
        Call .Close
    End With
    Set rst = Nothing
    Set db = Nothing
 
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.

[Barmaximus]

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

Private Sub cmdAssetSealed_DblClick(Cancel As Integer)
 
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    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;"
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveFirst
            Do While Not .EOF
                strSQL = _
                    "UPDATE [Data - Assets] " & _
                    "SET Sealed = " & !IsSealed & _
                    "WHERE [Data - Assets].ID = " & !Asset
                    Call CurrentDb.Execute(Query:=strSQL)
                Call .MoveNext
            Loop
        End If
        Call .Close
    End With
    Set rst = Nothing
    Set db = Nothing
 
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?

[Barmaximus]

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.

[twinnyfo]

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.

[twinnyfo]

It is a typo from my original.

Change to Line 25 below:

Private Sub cmdAssetSealed_DblClick(Cancel As Integer)
 
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    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;"
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveFirst
            Do While Not .EOF
                strSQL = _
                    "UPDATE [Data - Assets] " & _
                    "SET Sealed = " & !IsSealed & _
                    " WHERE [Data - Assets].ID = " & !Asset
                    Call CurrentDb.Execute(Query:=strSQL)
                Call .MoveNext
            Loop
        End If
        Call .Close
    End With
    Set rst = Nothing
    Set db = Nothing
 
End Sub

Spaces can make or break you!

[Barmaximus]

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?

[twinnyfo]

If I remember correctly, the String limit is either 5,000 or 65,535 characters.

[NeoPa]

I believe the maximum length for a string in VBA is 64K (65,536). 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.