Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cumulative Record Select 1

Status
Not open for further replies.

inuman

Programmer
Feb 23, 2006
20
RO
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!
 
Another query that groups by code and sums quantity should suit:

[tt]SELECT Code, Sum(Quantity) AS SumOfQty
FROM tblTable
GROUP BY Code;[/tt]
 
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;
 
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.
 
Code:
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
 
Thank you very much both Golom and Remou.
Golom: your solution works like a charm!

Thank you!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top