Cumulative Record Select 1

[inuman]

Hello!

I have a query which returns something like:
Code | Quantity
-------------------------
aaa 2
bbb 3
aaa 4

How to select records to look like this:
Code | Quantity
-------------------------
aaa 6 <=== which is the cumulated quantity
bbb 3 of the records with the same
Code: "aaa"

Many thanks in advance!

 

[Remou]

Another query that groups by code and sums quantity should suit:

[tt]SELECT Code, Sum(Quantity) AS SumOfQty
FROM tblTable
GROUP BY Code;[/tt]

 

[inuman]

Thank you very much Remou, your solution works, but I have a very long query and when I add the code it gives this error:
You tried to execute a query that does not include the specified expression 'fieldname' as part of an aggregate function.

This is my query:
SELECT Comenzi.IDComanda, Comenzi.DataComanda, Furnizori.IDFurnizor, Furnizori.Nume, Piese.Piesa, Piese.CodFurn, Sum(Piese.Bucati) AS SumBucati, Piese.Comandata, Piese.Pret, Piese.CodProd, Piese.IDPiesa
FROM (Comenzi INNER JOIN Piese ON Comenzi.IDComanda = Piese.IDComanda) INNER JOIN Furnizori ON Piese.IDFurnizor = Furnizori.IDFurnizor
WHERE (((Comenzi.DataComanda)=[Forms]![Comanda]![Data]) AND ((Furnizori.IDFurnizor)=[Forms]![Comanda]![IDFurnizor]) AND ((Piese.Comandata)=True)) GROUP BY Piese.CodFurn;

 

[Remou]

You need to group by all the fields that are not used in aggregate functions (sum etc). You may need to do this with two queries or a nested query to get exactly what you want.

 

[Golom]

SELECT C.IDComanda, C.DataComanda, F.IDFurnizor, F.Nume, P.Piesa, P.CodFurn, Sum(P.Bucati) AS SumBucati, P.Comandata, P.Pret, P.CodProd, P.IDPiesa

FROM (Comenzi C INNER JOIN Piese P ON C.IDComanda = P.IDComanda) INNER JOIN Furnizori F ON P.IDFurnizor = F.IDFurnizor

WHERE C.DataComanda=[Forms]![Comanda]![Data] 
  AND F.IDFurnizor=[Forms]![Comanda]![IDFurnizor] 
  AND P.Comandata=True 

GROUP BY C.IDComanda, C.DataComanda, F.IDFurnizor, F.Nume, P.Piesa, P.CodFurn, P.Comandata, P.Pret, P.CodProd, P.IDPiesa

 

[inuman]

Thank you very much both Golom and Remou.
Golom: your solution works like a charm!

Thank you!