Need Help with Aggregating Groups in MS Access Query 1

[SeaMcD1]

I'm trying to use MS Access Query to aggregate "count" and "sum" contract data for each customer. I have six customers, but thousands of contracts. The field names are "CUSTNm", "CONTNo" "$VAL", and "$BAL?. Is there any way to design the query or SQL Statement so it returns six row showing the contract sum/count for each of my six customers?

 

[PHV]

something like this (SQL) ?
SELECT CUSTNm, Sum([$VAL]) AS TotVal, Sum([$BAL]) AS TotBal, Count(*) AS NumberOfContracts
FROM tblCustomers
GROUP BY CUSTNm;

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

 

[SeaMcD1]

Thanks! - It worked great!

 

[SeaMcD1]

I'd appreciate just one last bit of help with counting-up 3 different entries in an additional field in my table named "KIND". Kind info can be "1", "2", or empty (null).
What's messing me up is trying to specify the value to be counted-up - Can you please help me again.


SELECT CUSTNm, Sum([$VAL]) AS TotVal, Sum([$BAL]) AS TotBal, Count(*) AS NumberOfContracts, Count([KIND=1]) AS KIND1, Count ([KIND=2]) AS KIND2, Count([KIND=NULL]) AS KINDNONE
FROM tblCustomers
GROUP BY CUSTNm;

 

[vise]

SELECT CUSTNm, Sum([$VAL]) AS TotVal, Sum([$BAL]) AS TotBal, Count(*) AS NumberOfContracts, Count([KIND]=1) AS KIND1, Count ([KIND]=2) AS KIND2, Count([KIND]="") AS KINDNONE
FROM tblCustomers
GROUP BY CUSTNm;


--
Just to explain [Field] you put the criteria in the field.. thus it was looking for field "Kind=2

 

[PHV]

SELECT CUSTNm, Sum([$VAL]) AS TotVal, Sum([$BAL]) AS TotBal, Count(*) AS NumberOfContracts
, Sum(IIf(KIND=1,1,0)) AS KIND1, Sum(IIf(KIND=2,1,0)) AS KIND2, Sum(IIf(KIND Is Null,1,0)) AS KINDNONE
FROM tblCustomers
GROUP BY CUSTNm

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

 

[SeaMcD1]

Thanks again PHV - it worked like a charm, and I was able to expand it to cover more kinds.