Re: Challenging SQL Query Problem. Can you solve it?

To keep it the way you have it what I can suggest then is just to change your INNER JOIN statement on both sold sales and lost sales to LEFT OUTER JOIN SELECT leads.salesman AS Salesperson, COUNT(lost) AS [Number of Lost] FROM leads LEFT OUTER JOIN salesman ON leads.salesman = salesman.salesman
WHERE lost<>0
GROUP BY leads.salesman;

SELECT leads.salesman AS Salesperson, SUM(amount) AS Totaled, COUNT(sold) AS [Number of Sales] FROM leads LEFT OUTER JOIN salesman ON

leads.salesman = salesman.salesman
WHERE sold <> 0
GROUP BY leads.salesman;

and then change your third query to:

SELECT * FROM [Lost Sales] JOIN [Sold Sales] ON [Lost Sales].[Salesperson] = [ Sold Sales.Salesperson];

this should give you all employees on both sides or your report. If you don't want to see employees that haven't sold or lost anything then you can filter them out from your last query by adding number of lost + number of sold > 0 or any other way you want to filter them out.

T3. Received on Fri Dec 23 2005 - 16:28:18 CET