calculation in a query

[Neil_Pattison]

I have a query that is built up of 5 fields with only the 2 below being visible. I am wanting to be able to calculate the total price for each pole no i.e. Pole no 01-02 total price would be £1441.79, What is the best way to do this? Sample data is shown below


New Pole No Total Cost
01-02 .....£437.08
01-02 .....£476.37
01-02 .....£36.21
01-02 .....£70.27
01-02 .....£51.56
01-02 .....£370.30
01-1 .....£437.08
01-1 .....£476.37
01-1 .....£36.21
01-10367 .....£493.43
01-10367 .....£70.15
01-10367 .....£76.51
01-10367 .....£102.67
01-10367 .....£87.54
01-10367 .....£36.21
01-10367 .....£51.56
01-10367 .....£39.54
01-11448 .....£437.08
01-11448 .....£463.25
01-11448 .....£493.43
01-11448 .....£476.37
01-11448 .....£70.15
01-11448 .....£76.51

 

[neileg]

A Totals query, perhaps? Click the sigma Σ button and change the Group By to Sum in the total cost field.

 

[Neil_Pattison]

Thanks for replying. I did this before posting but the calculation is coming through incorrectly. The Total cost is found by adding 2 field together

i.e. Total Cost:[Material Cost]+[Labour Cost]

Is it something to do with trying to do a sum function on a field that contains a calculation

 

[neileg]

If your cost fields appear in the query even if you have unchecked the Show box, this makes the rows returned potentially unique and the Group By will show these as distinct records. If you remove the cost fields from the query and just have the New_Pole_No and the TotalCost, then the sum should opperate properly.

Alternatively, if you are showing this data in a form or report, you can do some of the maths there. This makes the query less complex.

 

[Neil_Pattison]

thanks for the reply