calculations in a query

[NeilPattison]

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

 

[lupins46]

Convert the query to a Totals query using the toolbar button.
Group By New Pole No and change 'group By' to Sum for the Tota Cost column.

 

[NeilPattison]

Thanks for replying. I did this before posting but the calculation is coming through correctly. 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

 

[lupins46]

Inthat case use a second query based on this one *leavve this one just showing every record with its Total Cost and then base a Totals query on this one.

 

[NeilPattison]

Meant to say coming through incorrectly

 

[lespaul]

if you need to have this total in your existing query (instead of creating a new one based on this query) you can't use the "alias" names you used in the query.

For instance, if you have:

SELECT Sum(Materials) As MaterialTotal, Sum(Labor) as LaborTotal, Sum(MaterialTotal + LaborTotal) FROM ....

won't work, you need to use the formula again:

SELECT Sum(Materials) As MaterialTotal, Sum(Labor) as LaborTotal, Sum(Sum(Materials) + Sum(Labor)) FROM ....

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Why not simply this ?
SELECT [New Pole No], Sum([Material Cost])+Sum([Labour Cost]) AS [Total Cost]
FROM yourTable here
WHERE yourCriterias here
GROUP BY [New Pole No]

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