Update so simple in SQL - not in Access

[djayam]

Hi all,

I'm new to Access but competent in SQL queries. I need to get this query working in Access but am failing miserably - can anyone help!?

In SQL:

UPDATE Orders


SET Delivered =
(
SELECT

SUM(Deliveries.Quantity)

FROM
Deliveries

WHERE
Orders.OrderID = Deliveries.OrderID
AND ShipDate < GetDate()
)

Copying and pasting into access gives the error "Operation must use an updateable query"

Help!! Thanks.
Jason

 

[DotNetGnat]

make sure you have the write permissions on the table...

-DNG

 

[PHV]

One way:
UPDATE Orders
SET Delivered = DSum("Quantity", "Deliveries", "OrderID=" & [OrderID] & " AND Shipdate<#" & Format(Date(),"yyyy-mm-dd") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[djayam]

Unbelievable. Got to say I don't really understand what you have written, but it works!!

Thank you so much and for such a quick reply.

Jason