Problem with Count in SQL/Distinct Count

[zimmer9]

I ran the following query and got the result set which I will split into 2 sections below:

SELECT C.CustomerNumber, C.DateLost, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))<1000,"L1") AS CAT1, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 1000 And 9999.99,"L2") AS CAT2, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 10000 And 24999.99,"L3") AS CAT3, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 25000 And 49999.99,"L4") AS CAT4, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 50000 And 99999.99,"L5") AS CAT5, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))>100000,"L6") AS CAT6, Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) AS Total, "LT6" AS LT6
FROM tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber
WHERE (((C.DateLost) Between #8/1/2004# And #1/31/2005#))
GROUP BY C.CustomerNumber, C.DateLost, "LT6";

CustNum Date Lost CAT1 CAT2 CAT3
023414 10/01/2004 L2
036983 12/01/2004 L2
047370 11/01/2004 L1
062992 11/01/2004 L1
066173 11/01/2004 L3
079580 11/01/2004 L3
138967 08/01/2004

SumOfTotal CAT1 CAT2 CAT3
29581.57
33743.51 L3
18043.26 L2
641.97 L1

I then ran another query to categorize the dollar amounts by different ranges of values as follows by adding character constant fields to the output:

SELECT Sum(qry7.Total) AS SumOfTotal, qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6, Count(C.CustomerNumber) AS CountOfCustomerNumber
FROM (tblCustomers AS C INNER JOIN qry7 ON C.CustomerNumber = qry7.CustomerNumber) INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber
GROUP BY qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6;

SumOfTotal CAT1 CAT2 CAT3 CAT4
29581.57 L4
33743.51 L3
18043.26 L2
641.97 L1


CAT5 CAT6 CountOfCustomerNumber
1
2
4
2

There can be more than 1 record per customer number. I am trying to get a count of how many distinct customers fall into each range of dollar values. On the 3rd row listed above, the count of customers is 4. I am trying to report distinct customers. The count of 4 is comprised of 3 records belonging to 1 customer and 1 record belonging to another customer as follows:

SumOfTotal CAT1 CAT2 CAT3 CAT4
29581.57 L4
17570 L3
16173.51 L3
14944.32 L2
3098.94 L2
598.76 L1
43.21 L1

CAT5 CAT6 CountOfCustomerNumber Cust Num
1 138967
1 066173
1 079580
3 023414
1 036983
1 047370
1 062992

 

[zimmer9]

Let me clarify one point:
the purpose of my second query was to twofold:

1) to consolidate the dollar amounts within each dollar range

2) to provide a count of unique customers within each dollar range.

 

[zimmer9]

I also created the wrong 2nd snapshot of my first query. The first query should read as follows:

CustNum Date Lost CAT1 CAT2 CAT3
023414 10/01/2004 L2
036983 12/01/2004 L2
047370 11/01/2004 L1
062992 11/01/2004 L1
066173 11/01/2004 L3
079580 11/01/2004 L3
138967 08/01/2004



CAT4 CAT5 CAT6 Total
4981.44
3098.94
598.76
43.21
17570
16173.51
L4 29581.57

 

[zimmer9]

I think I can get either of the following to work:

SELECT Sum(qry7.Total) AS SumOfTotal, qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6, Count(qry7.CustomerNumber) AS CountOfCustomerNumber
FROM qry7
GROUP BY qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6;


SELECT Sum(qry7.Total) AS SumOfTotal, qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6, Count(C.CustomerNumber) AS CountOfCustomerNumber
FROM tblCustomers AS C INNER JOIN qry7 ON C.CustomerNumber=qry7.CustomerNumber
GROUP BY qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6;