Return all records even if no match on joined table

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

I have two tables; one containing all the sales people of
the company, the other containing order data which
includes the appropriate sales person per order. If there
is no order data for sales person x, I want the query to
still show their name along with a $0 sales amount. I
tried using the join properties where it says to include
all records from the sale person table and only matching
records from the order table, but that didn't work. Any
ideas?
 
I have two tables; one containing all the sales people of
the company, the other containing order data which
includes the appropriate sales person per order. If there
is no order data for sales person x, I want the query to
still show their name along with a $0 sales amount. I
tried using the join properties where it says to include
all records from the sale person table and only matching
records from the order table, but that didn't work. Any
ideas?
Click to expand...

Please post the SQL of your query. Do you have criteria on the order
table? If so, you should include IS NULL as part of the criteria, to
cover the cases where there is no sale at all - if for example you're
searching for sales within a date range, then you'll see nothing.
 
Yes, I do have criteria on the orders table (actually a
query that I've created totals on) which is called Active
Oppty. Here's the SQL...

SELECT Reps.[Sales Region], Reps.[Sales Person (First
Name)], Reps.[Sales Person (Last Name)], [Active Oppty
Group].Month, Count([Active Oppty Group].[Lead ID]) AS
[CountOfLead ID], Sum([Active Oppty Group].[SumOfTotal
Amount]) AS [SumOfSumOfTotal Amount], Sum([Active Oppty
Group].[Prob Win Amount]) AS [SumOfProb Win Amount]
FROM Reps LEFT JOIN [Active Oppty Group] ON (Reps.[Sales
Person (First Name)] = [Active Oppty Group].[Sales Person
(First Name)]) AND (Reps.[Sales Person (Last Name)] =
[Active Oppty Group].[Sales Person (Last Name)])
WHERE ((([Active Oppty Group].Status)="active" Or ([Active
Oppty Group].Status)="Won") AND (([Active Oppty Group].
[Win Probability]) Between 15 And 60))
GROUP BY Reps.[Sales Region], Reps.[Sales Person (First
Name)], Reps.[Sales Person (Last Name)], [Active Oppty
Group].Month;


Thanks for your help!
 
Yes, I do have criteria on the orders table (actually a
query that I've created totals on) which is called Active
Oppty. Here's the SQL...
Click to expand...

You may want to apply the criteria *to the Totals query* so the left
outer join will work against the entire query; as it is, you'll get
all the group records (even if there is no data in Active Oppty) and
then discard them because the fields contain NULL instead of having
the values from your query.

Alternatively (and less efficiently) use OR IS NULL in your criteria:
e.g.

SELECT Reps.[Sales Region], Reps.[Sales Person (First
Name)], Reps.[Sales Person (Last Name)], [Active Oppty
Group].Month, Count([Active Oppty Group].[Lead ID]) AS
[CountOfLead ID], Sum([Active Oppty Group].[SumOfTotal
Amount]) AS [SumOfSumOfTotal Amount], Sum([Active Oppty
Group].[Prob Win Amount]) AS [SumOfProb Win Amount]
FROM Reps LEFT JOIN [Active Oppty Group] ON (Reps.[Sales
Person (First Name)] = [Active Oppty Group].[Sales Person
(First Name)]) AND (Reps.[Sales Person (Last Name)] =
[Active Oppty Group].[Sales Person (Last Name)])
WHERE ([Active Oppty Group].Status="active" Or [Active
Oppty Group].Status)="Won" Or [Active Oppty Group].[Status] IS NULL)
AND ([Active Oppty Group].[Win Probability] Between 15 And 60 OR
[Active Oppty Group].[Win Probability] IS NULL)
GROUP BY Reps.[Sales Region], Reps.[Sales Person (First
Name)], Reps.[Sales Person (Last Name)], [Active Oppty
Group].Month;

Note also that using First Name and Last Name as linking fields is
VERY risky: names are *not* unique. What will you get if you happen to
have two salespeople both named Jane Smith?
 
Back
Top