Sales Ranks

[CVN69usn]

Hi, I'm a beginner with MS Access and was wondering if anyone could help me.
My boss gave me a list of 10,000 customers. The list has the sales person ID
number, customer ID number and total sales for each customer. He wants me to
rank the by sales person then by total sales. It should look something like
this:
SalesPersonID CustID TotalSales Rank
102 2005 1000.00 3
102 9012 950.00 2
102 0712 500.00 1
201 4548 900.00 2
201 3232 825.00 1
and so on, and so on
I just stated using access and I'm getting comfortable with the expression
builder in Queries. My kingdom for whoever can help this novice user.

 

[Dale Fye]

Well

If your not worried about ranking by the sales person with the most total
sales, then you could simply sort by SalesPersonID and TotalSales. Assuming
that you need to compute the rank, you would do something like:

SELECT SalesPersonID, CustID, TotalSales,
DCOUNT("CustID", "yourTableName", "SalesPersonID = " & [SalesPersonID] & "
AND [TotalSales] <= " & [TotalSales]) as Rank
FROM yourTable
ORDER BY SalesPersonID, TotalSales DESC

If you want to sort it so that the sales person with the most total sales is
on top, you would need to start out be creating a query that computes the
total sales for each salesperson.

Query1:
SELECT SalesPersonID, Sum([TotalSales]) as CumSales
FROM yourTable
GROUP BY SalesPersonID

Then, you would link that query to "yourTable" and sort by the CumSales
field as well. Something like

SELECT SalesPersonID, CustID, TotalSales,
DCOUNT("CustID", "yourTableName", "SalesPersonID = " & [SalesPersonID] & "
AND [TotalSales] <= " & [TotalSales]) as Rank
FROM yourTable
INNER JOIN Query1
ON yourTable.SalesPersonID = Query1.SalesPersonID
ORDER BY Query1.CumSales DESC, SalesPersonID ASC, TotalSales DESC

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.