Operation must include an updatable query. HELP!!! 1

[pingman]

Currently, I have a database that tracks the inventory of parts. This includes the receipt of parts that were ordered via a purchase order. I have a Purchase Order table, Receipt table, and a PO Detail table. For each part ordered on the po, there can be multiple receipts, this covers parts being back ordered(yes/no field in Receipt table). What I am trying to do is to set a yes/no field(Received in full) in both PO detail and PO Table to yes if the amount received = amount ordered. I have the correct select query to get the correct po's that it should update but when I change to an update query it wont work due to the use of the sum function to get the po number that should be updated. I am using the sum function on the amount received and have amount ordered in the criteria box. Amount ordered is in the PO table. When I try and leave it as a select query and then use it as the list of records to update in a different query, then i get the "operation must include an updateable query" message. Any suggestions. Is this making any sense? Thanks for your help!

 

[RickSpr]

My hunch is that you just need to restructure your queries a little, though it's hard to formulate what you've got from your description.

Obviously you have to sum the receipts per PO. Do you also have to sum something from the PO Detail table? What gets joined with what?

Maybe the simplest thing is for you to show us the SQL for each of your queries. Rick Sprague

 

[pingman]

Rick,

Thanks for your input! Below is the sql for the queries.

SELECT Receive.[PO Number], Receive.[Part Number], Sum(Receive.[Quantity Reveived]) AS [SumOfQuantity Reveived], [PO Detail].[Quantity Ordered], [PO Detail].RIF, [Purchase Orders].RIF
FROM [Purchase Orders] INNER JOIN (Receive INNER JOIN [PO Detail] ON (Receive.[PO Number] = [PO Detail].[PO Number]) AND (Receive.[Part Number] = [PO Detail].[Part Number])) ON ([Purchase Orders].[PO Number] = [PO Detail].[PO Number]) AND ([Purchase Orders].[PO Number] = Receive.[PO Number])
GROUP BY Receive.[PO Number], Receive.[Part Number], [PO Detail].[Quantity Ordered], [PO Detail].RIF, [Purchase Orders].RIF
HAVING (((Sum(Receive.[Quantity Reveived]))=[Quantity Ordered]));

The previous sql gets those purchase orders that have quantity ordered = quantity received. When I try and change this to an update query, it wont let me due to the aggregate function. Also, if I tru to use this query as a select query in an update query, then I get the message that says I must use an updateable query.

Let me know if you need any more info. And THANKS for the help!

 

[RickSpr]

It took me a while, but at least I figured out one way to do it. You need three queries. I'll give a description and the SQL for each.

1. FilledPODetails - Selects PO Number, Part Number for each PO Detail where the quantity ordered is <= the sum of the quantity received.
SQL: SELECT [PO Detail].[PO Number], [PO Detail].[Part Number]
FROM [PO Detail] INNER JOIN Receive ON ([PO Detail].[Part Number] = Receive.[Part Number]) AND ([PO Detail].[PO Number] = Receive.[PO Number])
GROUP BY [PO Detail].[PO Number], [PO Detail].[Part Number], [PO Detail].[Quantity Ordered]
HAVING (((Sum(Receive.[Quantity Reveived]))>=[Quantity Ordered]));

2. UpdateFilledPODetails - Sets Received in full to True for each PO Number and Part Number output by the first query.
SQL: UPDATE [PO Detail] SET [PO Detail].[Received in full] = True
WHERE ((([PO Number] & [Part Number]) In (SELECT [PO Number] & [Part Number] FROM FilledPODetails)));

3. UpdateFilledPOs - Sets Received in full to True for each PO Number where there are no PO Detail rows with Received in full set to False.
SQL: UPDATE [Purchase Orders] SET [Purchase Orders].[Received in full] = True
WHERE ((([Purchase Orders].[PO Number]) Not In (SELECT [PO Number] FROM [PO Detail] WHERE [PO Detail].[Received In Full] = False)));

Notes:
1. I got around the updatability problem by using the SQL IN operator in the update query criteria.
2. I didn't see where you needed the RIF fields, so I left them out.
3. The Purchase Order rows must be updated separately after the PO Detail rows. Also, you don't want to mark a PO received in full if just any of the details are filled, you only want to mark it if all of them are. Your query seemed to be headed toward the former.
4. In the second query, I concatenated PO number and Part Number to look up in the subquery. You could use an EXISTS subquery instead and keep them separate, but this is simpler SQL. The EXISTS subquery would be more efficient, however, at least if you have indexes on PO Number and Part Number.
Rick Sprague

 

[pingman]

Thanks a ton Rick! I really appreciate the help. It works great! I totally forgot about the IN operator! DUH!

Thanks again.

MK