Unique count for sales rep (1 Viewer)

[nileshtx]

I have a table with salesdate[date], center[text], sales_rep[number], and sales[yes/no].
I need to find the number of sales_rep in each center by month who had yes sales
Is there a way to make query that would group by month and center, and give a unique count of the sales_rep where sales is yes.

Thank you,
nilesh

 

[Jon K]

Try this query. (Create a new query. Copy and paste the code to the query's SQL View. Amend the table name.):

SELECT format(SalesDate,"mmmm") AS Month,
Center, Count(*) AS NumOfRep
FROM yourTable
WHERE Sales
GROUP BY month(SalesDate), format(SalesDate,"mmmm"), Center;

 

[Pat Hartman]

If you have data from multiple years in your table, you will need to include year as well as month in the query.

 

[nileshtx]

I tried Jons query, however since a sales rep is in the table many times each rep would be counted more then once. How would i find the number of individuals reps by month?

 

[Pat Hartman]

You can break up the problem using nested queries.

Query1:
Select Distinct format(SalesDate,"yyyy-mmmm") AS SalesYearMonth, RepId
From YourTable;

Query2:
Select SalesYearMonth, count(*) AS CountOfReps
From Query1
Group By SalesYearMonth;