Unique count for sales rep

Can you help

  • Can you help

    Votes: 1 100.0%
  • was question good

    Votes: 0 0.0%

  • Total voters
    1

nileshtx

New member
Local time
Today, 16:19
Joined
Apr 3, 2002
Messages
9
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
 
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;
 
If you have data from multiple years in your table, you will need to include year as well as month in the query.
 
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?
 
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;
 

Users who are viewing this thread

Back
Top Bottom