RecordSet Not Updatable?

[jedooley]

Can someone tell me why my query is not updatable. I have created a form with this query and would like to update data when need be. All this query does is make a few calculations. thanks

SELECT Client.Company_Name, Product.Product_Name, Delivery.FC_Number, Delivery.Bill_of_Lading_No, Delivery.Location, Delivery.Del_Date_From, Delivery.Del_Date_To, Delivery.Act_Volume, Delivery.Prod_Price, Delivery.Truck_Price_M3, Delivery.Hot_Oil_Price, Delivery.Hourly_Truck, Delivery.Terms, Sum([Delivery].[Truck_Price_M3]+[Delivery].[Tank_Price]+[Delivery].[Prod_Price]+[Delivery].[Hot_Oil_Price]) AS Total_Price, Format(Total_Price*[delivery].[Act_Volume],"Currency") AS Total_Priced_Volume

FROM Product INNER JOIN ((Client INNER JOIN JOB ON Client.Client_ID = JOB.Cient_ID) INNER JOIN Delivery ON JOB.Job_ID = Delivery.Job_ID) ON (Product.[Product _ID] = JOB.Product_ID) AND (Product.[Product _ID] = Delivery.Product_ID)

GROUP BY Client.Company_Name, Product.Product_Name, Delivery.FC_Number, Delivery.Bill_of_Lading_No, Delivery.Location, Delivery.Del_Date_From, Delivery.Del_Date_To, Delivery.Act_Volume, Delivery.Prod_Price, Delivery.Truck_Price_M3, Delivery.Hot_Oil_Price, Delivery.Hourly_Truck, Delivery.Terms;

 

[Jon K]

Can someone tell me why my query is not updatable.

A Totals Query is non-updatable.
.

 

[Pat Hartman]

It's late but if I'm reading the query correctly, it doesn't need to be a totals query. You don't need the Sum() function to add the fields together.

Sum([Delivery].[Truck_Price_M3]+[Delivery].[Tank_Price]+[Delivery].[Prod_Price]+[Delivery].[Hot_Oil_Price]) AS Total_Price

should be

[Delivery].[Truck_Price_M3]+[Delivery].[Tank_Price]+[Delivery].[Prod_Price]+[Delivery].[Hot_Oil_Price] AS Total_Price

Then get rid of the group by and you should be back in business. Just don't expect to update the calculated fields.

 

[jedooley]

Ok, I fixed the query by remving the Sum and the Group by Clause and the Query stiill works. However it is still not updatable. Am I to assume that queries that perform calculations are not updatable? I am not trying to edit the totals, just the other fields. Is it possible? thanks for your help

 

[Pat Hartman]

Calculated expressions will not cause a query to be non-updatable.

You have an extra table in the FROM clause - JOB. No columns are selected from it. What is its purpose in this query?

Make sure that all the tables have primary keys defined so that Jet can determine the correct cardinality of the relationships. If you don't define primary keys, Jet may make a query non-updatable because it can't determine if the relationship is 1-1, 1-m, or m-m. A query that includes two independent 1-many relationships will be non-updatable in addition to being nonsensical. But a query with two hierarchical 1-m relationships will be updatable. So:
Orders --> OrderDetails --> Products WILL be updatable, but
Student --> Classes
Student --> Parents WILL NOT be updatable.