I know I shouldn't be doing this but...

[ryetee]

I have inherited a system. In part of this system there are 3 tables

MainTemplate
SUbTemplate
Parts

To keep it short
The maintemplate contains the name and also TOTALPRICE (see below)
The subtemplate hols all of the parts for the maintemplate which includes the number used
Parts contain the cost of each part

As mentioned above a TOTALPRICE is kept on maintemplate. I know with normalised data you shouldn't do this but that's how the system has been. It's not been a problem before as the parts for the maintemplate haven't chneged in number or price. Until now. TOTALPRICE if you had't gathered is the sum of the number of parts used * their price.

I've written a query and I can sum the number*price but I can't update the TOTALPRICE on the maintemplate. I've tried this using 1 and then 2 queries. I'm getting error messages such as "OPeration" must use an updateable query (this is when I have 2 queries" and "you tried to execute a query that does not include the specified expression xxxxx as an aggregate function"

Is there a way around this?

 

[Ranman256]

You can't update from a sum.
You can sum the data to a temp table then update from that.

 

[ryetee]

You can't update from a sum.
You can sum the data to a temp table then update from that.

Thanks, I came to that conclusion as well. Google suggest you can but always lets you down when you follow the links!
I've now stored the sum on the form and then use that control in a query I run in VBA