How do I change Count to No Duplicates in Queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clint - 24 Jul 2006 22:01 GMT

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

Reply to this Message

Michel Walsh - 25 Jul 2006 14:28 GMT

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
[quoted text clipped - 13 lines]
> table].[Cust#] = [order table].[Sold to Customer]
> GROUP BY Left([customer table].[Zip/Postal Code],3);

Reply to this Message

Clint - 25 Jul 2006 22:30 GMT

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.

> Hi,
>
[quoted text clipped - 25 lines]
> > table].[Cust#] = [order table].[Sold to Customer]
> > GROUP BY Left([customer table].[Zip/Postal Code],3);

Reply to this Message

Michel Walsh - 26 Jul 2006 22:59 GMT

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

> 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
[quoted text clipped - 35 lines]
>> > table].[Cust#] = [order table].[Sold to Customer]
>> > GROUP BY Left([customer table].[Zip/Postal Code],3);

Reply to this Message

Clint - 26 Jul 2006 23:22 GMT

Thank you.

> Hi,
>
[quoted text clipped - 49 lines]
> >> > table].[Cust#] = [order table].[Sold to Customer]
> >> > GROUP BY Left([customer table].[Zip/Postal Code],3);

Reply to this Message