Query to find customer accounts from one salesman and exclude accounts with multiple salesmen

[Gardinia]

Hi,

I'm trying to query some specific data on our customer database.

For some context our customers could have had sales contracts with multiple different sales reps over the years.

I want to find all the customer accounts of a particular sales rep, however I then want to exclude any accounts where this sales rep and any other sales rep has sold to the same customer.

This is where I'm unsure how to proceed, I can find everything that sales rep has sold (rep id 2) with the query below:





However I'm not sure how I would go about expanding that to exclude any accounts (ACNo) that also have contracts (ContractNo) from another sales rep (RepNo)

I'd be grateful for any advice on how to go about doing this.

[Welshgasman]

I want to find all the customer accounts of a particular sales rep, however I then want to exclude any accounts where this sales rep and any other sales rep has sold to the same customer.

Are you not contradicting yourself there?

[CJ_London]

do you mean find all the customers who have had only 1 sales rep?

[xps35]

Change your WHERE clause to

WHERE ContractHeader.RepNo=2 AND 
Customers.ACNo In (SELECT Customers.ACNo FROM SalesReps 
INNER JOIN (Customers 
INNER JOIN ContractHeader 
ON Customers.ACNo = ContractHeader.[ACNo]) 
ON SalesReps.RepNo = ContractHeader.[RepNo] 
GROUP BY Customers.ACNo 
HAVING Count(SalesReps.RepNo)=1)

[Gardinia]

Are you not contradicting yourself there?

do you mean find all the customers who have had only 1 sales rep?

I probably could have worded that a bit better, essentially I was looking for customers that have only ever had dealings with a single sales rep, excluding any who had dealings with multiple sales reps. So I could then narrow these customers down to a particular sales rep.

Change your WHERE clause to

WHERE ContractHeader.RepNo=2 AND
Customers.ACNo In (SELECT Customers.ACNo FROM SalesReps
INNER JOIN (Customers
INNER JOIN ContractHeader
ON Customers.ACNo = ContractHeader.[ACNo])
ON SalesReps.RepNo = ContractHeader.[RepNo]
GROUP BY Customers.ACNo
HAVING Count(SalesReps.RepNo)=1)

Perfect, thank you! that looks to have given me the exact data I was hoping to obtain.

It's been a long time since I've done anything with Access / SQL queries, so I appreciate the help.