error with Update Query

[ras81]

hi

i am new at this, and i have a problem in an UPDATE query
every time i try it gives the message:
"Operation must use an updatable query. (Error 3073)"

this is my query and i couldn't find anything wrong with it, the inner query works well, so thah's not the problem

UPDATE ShiftSummary_temp SET price = (SELECT Sum(ShiftDetails.price) 
FROM ShiftDetails WHERE (((ShiftDetails.price)>0)))
 
WHERE (((ShiftSummary_temp.[line])=2));

can u plz help me?
thanks

 

[LPurvis]

I'm afraid it is a fundamental requirement in Jet that any Update statement must select only fully updatable data (and as your subquery data isn't updatable - neither is the full statement).
The workaround is function calls. The domain aggregates are a simple and immediate alternative.

UPDATE ShiftSummary_temp SET price = DSum("price", "ShiftDetails ,"price>0")
WHERE [line]=2

FWIW your type of statement isn't a problem in other DBMS (such as SQL Server).

 

[ras81]

thanks, that's easy, but what can i do if i have much more complex query?

 SELECT "דמי משלוח" AS Name, Sum(DeliveryFee) AS fee
FROM (SELECT O.OrderID, O.[Total Price]-(1+O.DiscountPercentGiven)*Sum(iio.price-iio.DiscountGiven) AS DeliveryFee, Sum(iio.price-iio.DiscountGiven) AS Sumמתוךprice FROM reports, ItemsInOrder AS iio INNER JOIN [Order] AS O ON iio.orderID=O.OrderID GROUP BY O.OrderID, O.DiscountPercentGiven, O.[Total Price])

i can't just put it in the Dsum as u said (which ready made life easier with the other queries - thanks )

 

[LPurvis]

Oh you'd be surprised what can often be achieved for the sake of it using just the doman aggregates. ;-)
However (to be sensible) you can just go the route of using a user defined function (a VBA function you've created yourself) to return whatever data you require.

A standard such example could be:

Function fGetSQLResult(pstrSQL As String)
On Error GoTo errHere
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(pstrSQL, dbOpenSnapshot)
    
    With rst
        fGetSQLResult = .Fields(0)
        .Close
    End With
    
ExitHere:
    Set rst = Nothing
    Set db = Nothing
    Exit Function
    
errHere:
    fErrFullText Err, Err.Description, "fOpenRecordset"
    Resume ExitHere
    
End Function

You can pass it the whole SQL you want to execute - instead of using a DSum function.
This kind of thing can get messy (slow) over many rows - but then largescale updates on convoluted results hopefully aren't a common part of your application.