SQL Update Query that references another query and another table

[Amy Badgett]

UPDATE tblCallLog SET tblCallLog.tblCallLog_shipmentID = (SELECT [queryNextShipmentID].[Next ShipID] FROM [queryNextShipmentID])
WHERE ((tblCallLog.tblCallLog_clientID In (SELECT ListTop.tblCalLLog_clientID  FROM[ListTop])));
 

Access is saying that this query is not updateable. Can anyone tell me why?

[Amy Badgett]

So, I'm guessing this code either looks right to most people or more information is needed to help me out.

I would suppose the query would be self-explanatory, but if it isn't, here is what I need from this query.

I need to set the tblCallLog_shipmentID field to the next shipmentID number (which I found in another query by selecting the maximum shipmentID plus one) where the clientID's of the callLog table are the same as the clientID's in the query ListTop. To get this result I am referencing two different queries, perhaps this is the problem? Any ideas of how I could get around that?

Thanks in advance.

[pod]

I could be wrong, but I think your second level query is returning multple RECORDS which you are trying to insert into an integer field [tblCallLog_shipmentID]

If you have more than one client, you might be getting more than one NextShipmentID

and if you have more than one NextShipmentID record returned then the update query will throw an exception


troubleshooting:
I suggest run the second level and innermost query,
see what it returns you,
and assess from there

SELECT [queryNextShipmentID].[Next ShipID] FROM [queryNextShipmentID])
    WHERE tblCallLog.tblCallLog_clientID In 
(SELECT ListTop.tblCalLLog_clientID  FROM[ListTop])

[Amy Badgett]

Thank you, that was helpful advice, and my update query is now working.

[NeoPa]

Check out Reasons for a Query to be Non-Updatable if you still need further understanding of this area.