Select Top 10 Customers for Each Salesperson

Question

I am trying to create a report that only shows the Top 10 customers for each salesperson.

I can create the query and tell it to only return the top 10 but it only returns the first 10 records. It's not even the top 10 customers that it should be returning. When I run it the first two customer's total sales equal 0. I need the top 10 total sales for each customer.

This is what my query currently says...

Note: Sum(dbo_ARINVOICE.CurrencyValue) AS SumOfCurrencyValue is the total of all of one customer's invoices. I would have that sort first but I need the Salesperson to be in alphabetical order first, then their total sales.

SELECT TOP 10 dbo_ARINVOICE.Customer, dbo_ARINVOICE.Salesperson, Sum(dbo_ARINVOICE.CurrencyValue) AS SumOfCurrencyValue
FROM dbo_ARINVOICE
WHERE (((dbo_ARINVOICE.InvoiceDate) Between #1/1/2016# And #10/13/2016#))
GROUP BY dbo_ARINVOICE.Customer, dbo_ARINVOICE.Salesperson
ORDER BY dbo_ARINVOICE.Salesperson, Sum(dbo_ARINVOICE.CurrencyValue);

I tried something I found in another forum but it didn't work. It kept telling me that I had an error or had extra ().

Any ideas?

Thanks,

Jessica :) 

Answer 1

Hi Jessica,

Just a guess but how about maybe something like:

SELECT T1.Customer, T1. Salesperson, SQ.Total
FROM dbo_ARINVOICE AS T1
WHERE T1.Customer IN
  (SELECT TOP 10 T2.Customer, Sum(T2.CurrencyValue) AS Total
  FROM dbo_ARINVOICE AS T2
  WHERE T2.Customer=T1.Customer
  GROUP BY T2.Customer
  ORDER BY Sum(T2.CurrencyValue) DESC) AS SQ
ORDER BY T1.Salesperson, T1.Customer, SQ.Total DESC

(untested)

Hope it helps...

Answer 2

I copied that in and I get a syntax error...

Syntax error (missing operator) in query expression 'T1.Customer IN

(SELECT TOP 10 T2.Customer, Sum(T2.CurrencyValue) AS Total

  FROM dbo_ARINVOICE AS T2

  WHERE T2.Customer=T1.Customer

  GROUP BY T2.Customer

  ORDER BY Sum(T2.CurrencyValue) DESC) AS SQ

When I click OK it highlights the AS that I have bolded and underlined.

Any ideas?

Thanks,

Jessica

Answer 3

Hi Jessica,

Sorry about that... It's hard to guess when I can't test the query. Let's try a different way:

SELECT T1.Customer, T1.Salesperson, Sum(T1.CurrencyValue) AS Total
FROM dbo_ARINVOICE AS T1
WHERE T1.Customer IN
  (SELECT TOP 10 T2.Customer
  FROM dbo_ARINVOICE AS T2
  WHERE T2.Customer=T1.Customer
  GROUP BY T2.Customer
  ORDER BY Sum(T2.CurrencyValue) DESC)
GROUP BY T1.Salesperson, T1.Customer
ORDER BY Sum(T1.CurrencyValue) DESC

(unfortunately, still untested)

Hope it helps...

Answer 4

Well I tried it and it was still running after an hour and 15 minutes so I stopped it. I will try again Tuesday.

Thank you!!!

Answer 5

Hi Jessica,

Are you able to send me some test data?

Answer 6

You might find this two-query solution using data from Northwind helpful as an example.

First create a query qryEmployeeRevenue which returns the total revenue from orders per employee per customer within the first quarter of 2016:

SELECT [Employees].[First Name] & " " & [Employees].[Last Name] AS Employee,
[Customers].[First Name] & " " & [Customers].[Last Name] AS Customer,
SUM([Quantity]*[Unit Price]) AS TotalAmount
FROM Customers INNER JOIN (Employees INNER JOIN (Orders
INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID])
ON Employees.ID = Orders.[Employee ID]) ON Customers.ID = Orders.[Customer ID]
WHERE Orders.[Order Date] BETWEEN #2016-01-01# AND #2016-03-31#
GROUP BY [Employees].[First Name] & " " & [Employees].[Last Name],
[Customers].[First Name] & " " & [Customers].[Last Name];

Then create a query which returns the top customers per employee.  In this case I've restricted it to the top 2 customers per employee due to the small amount of data in Northwind:

SELECT Employee, Customer, TotalAmount
FROM qryEmployeeRevenue AS Q1
WHERE (SELECT COUNT(*)+1
                FROM qryEmployeeRevenue AS Q2
                WHERE Q2.Employee = Q1.Employee
                AND Q2.TotalAmount >= Q1.TotalAmount) <= 3
ORDER BY Q1.Employee,Q1.TotalAmount DESC;

Note that to get the top 2 customers the subquery returns the COUNT of rows + 1, and the outer query is restricted to those rows per employee where the subquery returns a count of less than or equal to 3.  This allows for ties.  Note also that employees who have not generated any revenue within the date range will not be returned.  You could easily include those employees by LEFT JOINING  the Employees table to the final query above in a further query.

I've assumed for simplicity that all employee and customer names are distinct.  In reality this is not reliable as personal names can legitimately be duplicated.  I worked with two Maggie Taylors.  Both queries should really return the distinct Employee ID and (Customer) ID values to avoid any coalescence of two or more same-named employees or customers into one.  That requires only minor amendments, however.

Please do not follow Northwind's bad practice of including spaces or other special characters in table or column names, and using the generic ID as a column name!

---

Ken Sheridan, Stafford, England

Answer 7

Hi DB,

I think I can send some test data. Let me see if I can put something together for you.

Thanks,

Jessica

Answer 8

Hi Ken,

I didn't try it using 2 queries but if I make this run from a form then I can just have it call a macro that runs the two queries. 

I will try this (once I reconfigure it for my tables) and let you know the outcome.

Thanks,

Jessica :)

Answer 9

I didn't try it using 2 queries but if I make this run from a form then I can just have it call a macro that runs the two queries.

There is no need to 'run' the queries.  You would simply need to make the second query a report's RecordSource.  In the first query, instead of the two date literals #2016-01-01# and #2016-03-31#, put references to two unbound text boxes on your form as parameters.  Add a button to the form to open the report.  You'd then enter the start and end dates for the date range for which you wish data returned and then click the button to open the report.

---

Ken Sheridan, Stafford, England

Answer 10

If run that way then query 1 would be the source for query 2 and query 2 would be the source for the report, correct?

Thanks,

Jessica

Answer 11

If run that way then query 1 would be the source for query 2 and query 2 would be the source for the report, correct?

Exactly.

---

Ken Sheridan, Stafford, England

Answer 12

I never did get this to work. I ended up exporting the data to Excel and deleting what I didn't want. They were rushing me to get the info.

And now here I am again because they want a similar report and I want to try to figure this out instead of just fudging it.

Is there any way to do this without using SQL statements? That's where I get confused and lost. No matter how many times I read it I can't make sense of it. When it's laid out in the Access query and i can see it then I understand it better.

This time they want Top 10 customers for each month.

Table name = dbo_ArTrnSummary

Fields are:

- TrnYear (Where = 2018)

- TrnMonth (Group By)(Ascending)

- Customer (Group By)

- MerchandiseValue (Sum)(Descending)

Again if I select 10 in the Return box it only returns the Top 10 overall. It doesn't care about the month.

I figure if I can get this to work then I should be able to apply it to other queries.

Thanks,

Jessica

Answer 13

I don't think there is any way you can do that in query design view.  The SQL statement is quite simple, however:

SELECT *
FROM dbo_ArTrnSummary AS T1
WHERE MerchandiseValue IN
    (SELECT TOP 10 MerchandiseValue
      FROM dbo_ArTrnSummary AS T2
      WHERE T2.TrnYear = T1.TrnYear
      AND T2.TrnMonth = T1.TrnMonth
      ORDER BY MerchandiseValue DESC)
ORDER BY TrnYear, TrnMonth;

The way it works is that the subquery, by virtue of being correlated with the outer query on the transaction year and month, returns, for each row potentially returned by the outer query, the top 10 rows for that month in descending order of MerchandiseValue.  The IN operator in the outer query operates on the set of MerchandiseValues returned by the subquery, restricting the outer query's result set to those rows which have a match in the subquery's result set.

---

Ken Sheridan, Stafford, England

Answer 14

Hi Ken,

It took over 2 hours to run. Is that normal? Regular queries take seconds to run.

When it finally came up it had data going all the way back to 2009. If I change the line that says:

WHERE T2.TrnYear = T1.TrnYear      to

WHERE T2.TrnYear = 2018

Should it still work?

Thanks,

Jessica

Answer 15

It took over 2 hours to run. Is that normal?

No.  It suggests that the columns are not indexed.  Appropriate indexing is crucial to the performance of a database.

As regards using the constant 2018 to restrict the subquery, you will also need to restrict the outer query similarly:

SELECT *
FROM dbo_ArTrnSummary AS T1
WHERE TrnYear = 2018
AND MerchandiseValue IN
    (SELECT TOP 10 MerchandiseValue
      FROM dbo_ArTrnSummary AS T2
      WHERE T2.TrnYear = 2018
      AND T2.TrnMonth = T1.TrnMonth
      ORDER BY MerchandiseValue DESC)
ORDER BY TrnMonth;

The ORDER BY clause will also affect performance, so only use it if it is necessary.  Do not use it if the query is intended as a report's RecordSource.  A report should be ordered by its internal 'Group and Sort' mechanism.

---

Ken Sheridan, Stafford, England