Want to show salespeople with 0 sales within date filter (1 Viewer)

thardyjackson · Jul 6, 2013

This should be easy but I'm struggling.

I want a very basic report that shows:

* all field reps (sales people),
* their quota in terms of new customer registrations
* their actual new customer registrations for a particular month

The report should include field reps with 0 sales. The query I've created works fine as long as there's no date filter. But adding <1/1/2013 removed all field reps with 0 sales. I want to make sure the filter concept was working before getting into date ranges.

Suggestions?

Code:

SELECT DISTINCTROW tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota, Count(tbl_customers.dateRegistration) AS NewRegistrations
FROM tbl_ksFieldRep LEFT JOIN tbl_customers ON tbl_ksFieldRep.[salesRepName] = tbl_customers.[ksFieldRep]
WHERE (((tbl_customers.dateRegistration)<#1/1/2013#))
GROUP BY tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota;

plog · Jul 6, 2013

When you add criteria to a table that has been LEFT JOINED to your main table, you effectively make that a regular join (because to meet the criteria, it has to have data in that table, if no data, no matching results). I think you can get around this with a sub-query.

Create a query based on tbl_customers and apply your criteria, save that query and then use it in the query you posted instead of tbl_customers. LEFT JOIN that query to tbl_ksFieldRep and you will get all salesRepName values.

thardyjackson · Jul 7, 2013

Worked like a charm. Thanks.

Want to show salespeople with 0 sales within date filter (1 Viewer)

thardyjackson

Registered User.

plog

Banishment Pending

thardyjackson

Registered User.

Similar threads

Users who are viewing this thread