Need to count # of distinct customers in Access Queries

R

Rev

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)
 
P

pietlinden

Rev said:
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)
Click to expand...

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.
 
R

Rev

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.
Rev said:
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)
Click to expand...

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.
Click to expand...
 
J

John Vinson

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.
Click to expand...

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]
 
G

Gary Walter

Rev said:
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.
Rev said:
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)
Click to expand...
Click to expand...
Click to expand...

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)
 
R

Rev

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.
John said:
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.
Click to expand...

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]
Click to expand...
 
R

Rev

Thanks, also to Garry, I'm giving it a shot right now.
Gary said:
Rev said:
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.
Rev wrote:
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)
Click to expand...
Click to expand...

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)
Click to expand...
 
R

Rev

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
Gary said:
Rev said:
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.
Rev wrote:
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)
Click to expand...
Click to expand...

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)
Click to expand...
 
G

Gary Walter

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...


Rev said:
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
Gary said:
Rev said:
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.
Rev wrote:
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)
Click to expand...

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)
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I change Count to No Duplicates in Queries 4
Changing the Group By Level to first 3 letters in Access Queries 2
Distinct Count in Crosstab Query 11
Invalid use of Null (You'll be sent to Guantanamo Bay!) 3
Need to write a query 5
Access Access Tick Box Calculation multiple records 0
Count unique in a query instead of count # 5
Unique Count in Query 17

Top