Update Query: SUM Function

[MsMope]

Here is my query first off

UPDATE api_tblTmpJobQuote INNER JOIN user_tblJobProp ON api_tblTmpJobQuote.[Job ID] = user_tblJobProp.ID SET user_tblJobProp.Delivery_Charge = Sum([api_tblTmpJobQuote]![TrussExtPrice])
group by api_tblTmpJobQuote.[Job ID],user_tblJobProp.ID ;

What I want to do is update the delivery_Charge column for the individual job ID with the total amount from the TrussPrice column any suggestions?

 

[SkipVought]

MsMope,

Be careful using aggregate values in a Table. Aggregating is USUALLY what is done a REPORT TIME. Table rows USUALLY contain granular detail data.

If you start putting aggragated data in a table, then you'll find that the aggregation is constantly changing and you end up with a maintenance nightmare.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PHV]

Storing calculated or derived value denormalize your database.
Anyway you may try something like this:
UPDATE user_tblJobProp
SET Delivery_Charge=DSum("TrussExtPrice","api_tblTmpJobQuote","[Job ID]='" & [ID] & "'");
If Job ID is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MsMope]

Thanks Guys,
I think I will take your suggestions and do the following:
create a variable to be used at run time, store the calculated total in that variable and then return it back to the dB. Sound good/

 

[PHV]

and then return it back to the dB
???

 

[MsMope]

What I ended up doing, probably not the most efficient, but it works:

Created two queries, one a make-table query, which creates a table storing the summed value of delivery charge and the jobID. I created a second Update query which updates the jobProp table for permanent storage of the delivery charge..
Then I simply called those two queries at report_open

Thanks,