Need Help with Select Criteria

[Runnr]

I have a table, one field has customer #, the other field has sales rep. Now
in some cases it is possible to have the same customer # more than once with
a different sales rep. I need to design a query that will pull out all the
Customer #'s that have more than 1 sales rep. Please help.

Thanks

 

[Ken Snell \(MVP\)]

Couple of different ways this can be done.

If you just want a list of the customer # values:

SELECT T.[customer #]
FROM TableName AS T
WHERE 1 <
(SELECT Count(*)
FROM TableName AS TT
WHERE TT.[customer #] = T.[customer #]);


If you want the count along with the customer # value:

SELECT [customer #], Count([customer #]) AS HowManySalesReps
FROM TableName
HAVING Count([customer #]) > 1;

 

[KARL DEWEY]

Try these two queries. If you know subqueries you could do it in one.

Runnr_1 ---
SELECT Runnr.Customer, Runnr.Sales_Rep
FROM Runnr
GROUP BY Runnr.Customer, Runnr.Sales_Rep;

SELECT Runnr_1.Customer, Count(Runnr_1.Sales_Rep) AS CountOfSales_Rep
FROM Runnr_1
GROUP BY Runnr_1.Customer
HAVING (((Count(Runnr_1.Sales_Rep))>1));

 

[Runnr]

Thanks, but I think I was not clear with my request (Or am i now more confused)

Table name "duplicate Prospects"
Field names: customer#, salesrep, clientfirstname, clientlastname

So, i need to run a querry that will pull out all the customer#'s that have
more than 1 different sales rep assigned to it. So i start by creating a
query in design mode, select table called "duplicate Prospects", select all
the fields.. Then what?

Thanks

 

[Runnr]

Thanks, but I think I was not clear with my request (Or am i now more confused)

Table name "duplicate Prospects"
Field names: customer#, salesrep, clientfirstname, clientlastname

So, i need to run a querry that will pull out all the customer#'s that have
more than 1 different sales rep assigned to it. So i start by creating a
query in design mode, select table called "duplicate Prospects", select all
the fields.. Then what?

Thanks

Couple of different ways this can be done.

If you just want a list of the customer # values:

SELECT T.[customer #]
FROM TableName AS T
WHERE 1 <
(SELECT Count(*)
FROM TableName AS TT
WHERE TT.[customer #] = T.[customer #]);


If you want the count along with the customer # value:

SELECT [customer #], Count([customer #]) AS HowManySalesReps
FROM TableName
HAVING Count([customer #]) > 1;

--

Ken Snell
<MS ACCESS MVP>

I have a table, one field has customer #, the other field has sales rep.
Now
in some cases it is possible to have the same customer # more than once
with
a different sales rep. I need to design a query that will pull out all
the
Customer #'s that have more than 1 sales rep. Please help.

Thanks

 

[John Spencer]

This is going to require two queries. The SQL for the queries should look
like the following

First query saved as qUniqueCustomerReps
SELECT Distinct [Customer#], SalesRep
FROM [Duplicate Prospects]

Now use that to get your information
SELECT [customer#], salesrep, clientfirstname, clientlastname
FROM [Duplicate Prospects]
WHERE [Customer#] In
(SELECT Customer#
FROM qUniqueCustomerReps
GROUP BY [Customer#]
HAVING Count(SalesRep) > 1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..