How do I change Count to No Duplicates in Queries

C

Clint

Hi, I actually asked this question already, but I cannot find the
answer or the question for the life of me so I am going to ask it
again. I need to find the Number of customers that a company has based
on the invoices. I can't just Count normally because it will count a
customer with 2 purchases as 2 customers. How do I set this up to only
count the Sold to Customer # once? I'm guessing I have to change it in
SQL. Here is the current syntax. What should it look like? Thanks so
much.

SELECT Left([customer table].[Zip/Postal Code],3) AS FSA, Sum([order
table].SubTotal) AS [Total Sales], Avg([order table].SubTotal) AS
[Average Sale], Count([order table].[Sold to Customer]) AS [CountOfSold

to Customer]
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);
 
M

Michel Walsh

Hi,


SELECT Customer, COUNT(*)
FROM (SELECT DISTINCT Customer, InvoiceNumber FROM yourQuery)


will count the number of different invoices, per customer (without counting
twice an invoice number that would appear twice, for the same customer, for
whatever reason).


Hoping it may help,
Vanderghast, Access MVP
 
C

Clint

Thanks for the help. The problem is that I don't have multiple invoices
that are the same. What I have is customers with several invoices that
are different. They have bought several item. What I need to know is
how I can count the total number of customers per area code, without
counting someone as multiple customers just because they have made
multiple purchases. Does that make sense? Let me know if you have a
solution. Thank you for your time.

Michel said:
Hi,


SELECT Customer, COUNT(*)
FROM (SELECT DISTINCT Customer, InvoiceNumber FROM yourQuery)


will count the number of different invoices, per customer (without counting
twice an invoice number that would appear twice, for the same customer, for
whatever reason).


Hoping it may help,
Vanderghast, Access MVP



Clint said:
Hi, I actually asked this question already, but I cannot find the
answer or the question for the life of me so I am going to ask it
again. I need to find the Number of customers that a company has based
on the invoices. I can't just Count normally because it will count a
customer with 2 purchases as 2 customers. How do I set this up to only
count the Sold to Customer # once? I'm guessing I have to change it in
SQL. Here is the current syntax. What should it look like? Thanks so
much.

SELECT Left([customer table].[Zip/Postal Code],3) AS FSA, Sum([order
table].SubTotal) AS [Total Sales], Avg([order table].SubTotal) AS
[Average Sale], Count([order table].[Sold to Customer]) AS [CountOfSold

to Customer]
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...
 
M

Michel Walsh

Hi,


that is thus very similar, just change the "role" of the field name:

SELECT ZipCode, COUNT(*)
FROM (SELECT DISTINCT ZipCode, Customers FROM yourQuery)
GROUP BY ZipCode


(I previously forgot the GROUP BY clause, without it, you obtain just one
group, the whole universe of the table).



Hoping it may help
Vanderghast, Access MVP


Clint said:
Thanks for the help. The problem is that I don't have multiple invoices
that are the same. What I have is customers with several invoices that
are different. They have bought several item. What I need to know is
how I can count the total number of customers per area code, without
counting someone as multiple customers just because they have made
multiple purchases. Does that make sense? Let me know if you have a
solution. Thank you for your time.

Michel said:
Hi,


SELECT Customer, COUNT(*)
FROM (SELECT DISTINCT Customer, InvoiceNumber FROM yourQuery)


will count the number of different invoices, per customer (without
counting
twice an invoice number that would appear twice, for the same customer,
for
whatever reason).


Hoping it may help,
Vanderghast, Access MVP



Clint said:
Hi, I actually asked this question already, but I cannot find the
answer or the question for the life of me so I am going to ask it
again. I need to find the Number of customers that a company has based
on the invoices. I can't just Count normally because it will count a
customer with 2 purchases as 2 customers. How do I set this up to only
count the Sold to Customer # once? I'm guessing I have to change it in
SQL. Here is the current syntax. What should it look like? Thanks so
much.

SELECT Left([customer table].[Zip/Postal Code],3) AS FSA, Sum([order
table].SubTotal) AS [Total Sales], Avg([order table].SubTotal) AS
[Average Sale], Count([order table].[Sold to Customer]) AS [CountOfSold

to Customer]
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...
 
C

Clint

Thank you.
Michel said:
Hi,


that is thus very similar, just change the "role" of the field name:

SELECT ZipCode, COUNT(*)
FROM (SELECT DISTINCT ZipCode, Customers FROM yourQuery)
GROUP BY ZipCode


(I previously forgot the GROUP BY clause, without it, you obtain just one
group, the whole universe of the table).



Hoping it may help
Vanderghast, Access MVP


Clint said:
Thanks for the help. The problem is that I don't have multiple invoices
that are the same. What I have is customers with several invoices that
are different. They have bought several item. What I need to know is
how I can count the total number of customers per area code, without
counting someone as multiple customers just because they have made
multiple purchases. Does that make sense? Let me know if you have a
solution. Thank you for your time.

Michel said:
Hi,


SELECT Customer, COUNT(*)
FROM (SELECT DISTINCT Customer, InvoiceNumber FROM yourQuery)


will count the number of different invoices, per customer (without
counting
twice an invoice number that would appear twice, for the same customer,
for
whatever reason).


Hoping it may help,
Vanderghast, Access MVP



Hi, I actually asked this question already, but I cannot find the
answer or the question for the life of me so I am going to ask it
again. I need to find the Number of customers that a company has based
on the invoices. I can't just Count normally because it will count a
customer with 2 purchases as 2 customers. How do I set this up to only
count the Sold to Customer # once? I'm guessing I have to change it in
SQL. Here is the current syntax. What should it look like? Thanks so
much.

SELECT Left([customer table].[Zip/Postal Code],3) AS FSA, Sum([order
table].SubTotal) AS [Total Sales], Avg([order table].SubTotal) AS
[Average Sale], Count([order table].[Sold to Customer]) AS [CountOfSold

to Customer]
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...
 

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

Need to count # of distinct customers in Access Queries 8
Adding up row values 4
Changing the Group By Level to first 3 letters in Access Queries 2
count within a given range 1
How create ranking within subgroup 4
Query to see only first several records 1
manual deletion of records in datasheet based on joined tables 5
Invalid use of null in query expression 3

Top