Sum in a Query by date.

[cap.zadi]

Hey there,



i having a query with the following fields:

MachineID, TranDate, PrdDate, GoodQTY, BadQty, CarNo


M1 12/10/2011 11/09/2011 20 10 1A
M1 12/10/2011 11/09/2011 05 01 1A
M1 12/10/2011 11/07/2011 09 07 1A

So what i am looking here is if on same Trndate there is same Prddate and same careNo so must be add up and show one results.


plz advise.

thanks

[alansidman]

must be add up and show one results

Must add the records? Which fields?

Add the fields across?

Which addition are you looking for? Perhaps if you showed expected results it would be helpful.

[cap.zadi]

Sir to addup Good Qty and BadQty if the TranDate, prodndate and KCarNo is same so can show us against this CarNo how much is GoodQty and BadQty.


M1 12/10/2011 11/09/2011 20 10 1A
M1 12/10/2011 11/09/2011 05 01 1A
M1 12/10/2011 11/07/2011 09 07 1A


Results:

M1 12/10/2011 11/09/2011 25 11 1A
M1 12/10/2011 11/07/2011 09 07 1A

*this value is not added beacuse the Prddate is different.

[orange]

Have you set up a query? Can you show us the SQL? What result/error?

Your field names are inconsistent and makes your post confusing.
Trndate there is same Prddate and same careNo so must be add up and show one results.
Good Qty and BadQty if the TranDate, prodndate and KCarNo is same so can show us against this CarNo
MachineID, TranDate, PrdDate, GoodQTY, BadQty, CarNo

[alansidman]

Try this:

SELECT Table2.MachineID, Table2.TranDate, Table2.PrdDate, Sum(Table2.GoodQty) AS SumOfGoodQty, Sum(Table2.BadQty) AS SumOfBadQty, Table2.CarNo
FROM Table2
GROUP BY Table2.MachineID, Table2.TranDate, Table2.PrdDate, Table2.CarNo;