Need to count # of distinct customers in Access Queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rev - 29 Jul 2006 00:23 GMT

I have a table that is grouped by postal codes first 3 digits, and I
need to know how many customers are in each catagory of postal codes. I
could use count but there are multiple invoices for single customers,
and if I just use count it will count those multiple invoices as two
customers. Here is the syntax, what should it look like to be able to
count distinct customers. Thank you.

SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order
table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS
AvgOfSubTotal, Count([customer table].[Cust#]) AS [CountOfCust#]
FROM [customer table] INNER JOIN [order table] ON [customer
table].[Cust#] = [order table].[Sold to Customer]
GROUP BY Left([Customer Table].[Zip/Postal Code],3)

Reply to this Message

pietlinden@hotmail.com - 29 Jul 2006 04:13 GMT

> I have a table that is grouped by postal codes first 3 digits, and I
> need to know how many customers are in each catagory of postal codes. I
[quoted text clipped - 9 lines]
> table].[Cust#] = [order table].[Sold to Customer]
> GROUP BY Left([Customer Table].[Zip/Postal Code],3)

Okay, I'm confused. How are the invoices relevant to the question? "
I need to know how many customers are in each catagory of postal codes"
- surely you can do this by summarizing the Customers table only.

Reply to this Message

Rev - 29 Jul 2006 16:52 GMT

Thank you for your time, let me explain a little more. I have a
customers table with all customers and potential customers. They are
not distinguished between the two. Also, I have a orders table which is
tied to the customers table via a field called Sold To Cust #. The
problem I am having is that some customers have multiple orders,
therefore they appear on the Sold To Cust # field more than once. I
don't want a customer with two orders to appear as two customers, plus
I can't just Count the customer table because some of the "customers"
on it are not actually customers since they have not placed any orders.
I need to know how many seperate people have placed orders, which means
placing a count on the customer # field, but I need to add a functions
so that it does not count duplicate orders. Does this help? Thanks,
again.

> > I have a table that is grouped by postal codes first 3 digits, and I
> > need to know how many customers are in each catagory of postal codes. I
[quoted text clipped - 13 lines]
> I need to know how many customers are in each catagory of postal codes"
> - surely you can do this by summarizing the Customers table only.

Reply to this Message

John Vinson - 29 Jul 2006 18:49 GMT

>Thank you for your time, let me explain a little more. I have a
>customers table with all customers and potential customers. They are
[quoted text clipped - 9 lines]
>so that it does not count duplicate orders. Does this help? Thanks,
>again.

I'd suggest using an EXISTS criterion then:

SELECT Count(*)
FROM Customers
WHERE EXISTS (Select [Sold To Cust #] FROM Orders WHERE [Sold To Cust
#] = [Customers].[CustomerID]);

This will select the record if there are any number of sales, but will
select it only once.

John W. Vinson[MVP]

Reply to this Message

Rev - 29 Jul 2006 22:04 GMT

Thank you for your time. I can't quite figure out how the full syntax
will final syntax must look to get no error messages. Here is my entire
SQL, if you could plug in your suggestion exaclty as my entire SQL will
look, that way I could just paste it in, that would be much
appreciated. Thank you for your time and effort.

> >Thank you for your time, let me explain a little more. I have a
> >customers table with all customers and potential customers. They are
[quoted text clipped - 21 lines]
>
> John W. Vinson[MVP]

Reply to this Message

Gary Walter - 29 Jul 2006 22:03 GMT

> Thank you for your time, let me explain a little more. I have a
> customers table with all customers and potential customers. They are
[quoted text clipped - 22 lines]
>> > table].[Cust#] = [order table].[Sold to Customer]
>> > GROUP BY Left([Customer Table].[Zip/Postal Code],3)

In addition to John's sage advice,
one other strategy is "divide-and-conquer":

qryGrpByFSACustNum:

SELECT
Left([Customer Table].[Zip/Postal Code],3) AS FSA,
[customer table].[Cust#] AS UniqueCustNum
FROM
[customer table]
INNER JOIN
[order table]
ON
[customer table].[Cust#] = [order table].[Sold to Customer]
GROUP BY
Left([Customer Table].[Zip/Postal Code],3),
[customer table].[Cust#];

qryDistinctCntCustNum:

SELECT
q.FSA,
Count(q.UniqueCustNum) As CustCnt
FROM
qryGrpByFSACustNum As q
GROUP BY
q.FSA;

final query:

SELECT
Left([Customer Table].[Zip/Postal Code],3) AS FSA,
Sum([order table].SubTotal) AS SumOfSubTotal,
Avg([order table].SubTotal) AS AvgOfSubTotal,
Count([customer table].[Cust#]) AS OrderCnt,
First(C.CustCnt) AS DistinctCustCnt
FROM
([customer table]
INNER JOIN
[order table]
ON
[customer table].[Cust#] = [order table].[Sold to Customer])
INNER JOIN
qryDistinctCntCustNum As C
ON
C.FSA = Left([Customer Table].[Zip/Postal Code],3)
GROUP BY
Left([Customer Table].[Zip/Postal Code],3)

Reply to this Message

Rev - 29 Jul 2006 22:06 GMT

Thanks, also to Garry, I'm giving it a shot right now.

> > Thank you for your time, let me explain a little more. I have a
> > customers table with all customers and potential customers. They are
[quoted text clipped - 71 lines]
> GROUP BY
> Left([Customer Table].[Zip/Postal Code],3)

Reply to this Message

Rev - 29 Jul 2006 22:24 GMT

Thank you very much!!! Gary that answer was dead on the money and you
made it so I didn't have to change a thing. Thank you very much!!! Have
a great day, and thanks to all who put input into helping me figure
this out, I appreciate it very much!! Clint Friesen

Reply to this Message

Gary Walter - 29 Jul 2006 22:40 GMT

One caveat:

if you think you might ever have
a zip field that is NULL, equalities
fail on NULL

NULL is not equal to NULL
and
NULL is not equal to "anything"

so...if that is a possibility, change
the join equality to something like:

INNER JOIN
qryDistinctCntCustNum As C
ON
NZ(C.FSA,"#$%")
=
NZ(Left([Customer Table].[Zip/Postal Code],3),"#$%")

otherwise, the null zip will fail to
provide records for cust w/ null zip...

> Thank you very much!!! Gary that answer was dead on the money and you
> made it so I didn't have to change a thing. Thank you very much!!! Have
[quoted text clipped - 78 lines]
>> GROUP BY
>> Left([Customer Table].[Zip/Postal Code],3)

Reply to this Message