Query Help

[strangeryet]

The query I have works up to a point, perhaps I need another to get the results I want, but I can't seem to get it to work.

The first query: Groups by Roll Size, Plant Site and KiloPrice.

SELECT rolltable.rollsize, Count(rolltable.rollnum) AS [Count], rolltable.rcvrplant, rolltable.kiloprice, Sum(rolltable.kgswgt) AS SumKilos, Format([SumKilos]*[Kiloprice],"Currency") AS TotalValue
FROM rolltable
WHERE (((rolltable.useddate) Is Null))
GROUP BY rolltable.rollsize, rolltable.rcvrplant, rolltable.kiloprice
HAVING (((rolltable.rcvrplant)=[@Site]));

This produces a result like:

rollsize count rcvrplant kiloprice SumKilos TotalValue
34.9 10 1 0.62 2826 $1,752.12
63.5 24 1 0.525 12035 $6,318.37
63.5 22 1 0.6035 11915 $7,190.70
63.5 125 1 0.62 68439 $42,432.18
69.9 20 1 0.62 11235 $6,965.70

Now I need a query that will Sum the 'SumKilos' and the TotalValue.

For a result like:
Count rcvrplant (Sum of sumofkilos) (sum of TotalValue)
201 1 106450 $64,659.07


(I hope spacing is in this post is maintained)
Thanks

 

[JerryKlmns]

And what prevents you to group and sum using your first query?

SELECT Sum([Count]), rcvrplant , Sum([SumKilos]), Sum([TotalValue])
FROM yourPreviousQuery
GROUP BY rcvrplant;

 

[strangeryet]

Well the first query I need to have grouped by rollsize and kilo price for detail. The second (separate) query would be to sum up the count, kilos and total value for a total line.

 

[PHV]

Something like this ?

SELECT Count(*) AS [Count], rcvrplant, Sum(kgswgt) AS [Sum of sumofkilos], Format(Sum(kgswgt*kiloprice),"Currency") AS [sum of TotalValue]
FROM rolltable
WHERE useddate Is Null AND rcvrplant=[@Site]
GROUP BY rcvrplant;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886