Help with making this query updateable with 3 joins.

[idbands]

I'm really struggling with this. I have this query:

SELECT OrderNumber, Code, dbrderItems.Status
FROM [Picking], dbrderItems, dbrders
WHERE ((clng([Picking].CONumber)=dbrders.OrderNumber) And (([Picking].ItemNo)=dbrderItems.Code) And (([Picking].isShort)=-1) And (([Picking].lastPickedDateTime)>=#12/15/2008#) And ((dbrderItems.OrderID)=dbrders.OrderID)) And dbrderItems.Status<>"Backordered";


To put it in English, I have a order database with two basic tables: Orders and OrderItems. Orders table holds the OrderNumber and primary key between Orders and OrderItems is OrderID. Then I have a seperate picking system that records items picked by OrderNumber and also "shorts" which are backordered items. I want to match the "Shorts" from the picking system to the OrderItems database and update the status in the OrderItems table with a status of "Backordered" when a short exists. I want this to be a Select query where I can manually update the status field by typing in the word "Backordered" but when I try to type in the Status field, it won't let me due to the query not being updatable I think??

How do I accomplish my goal?

Thank you!
Kelly

 

[idbands]

I should also mention that the Code field is a lovely Memo field so when I do an inner join on the Code field, I always get an error about how I can't join to a memo field.

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/148581

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886