Assign points based on value

[jmhicsupt]

I have a report that I need to assign ranking values to based on how the sales rep performed. If SalesRep1 has 15 sales and SalesRep2 has 10 sales, then I want to assign a Point value to the highest sales rep of 10 points; the next sales reps of 8 points and so on. So SalesRep1 would have 10 points assigned and SalesRep2 would have 8 points.

My fields are:
[Salesrep]
[SRPerform]

How would I assign the ranking values based on the sales rep's performance.

Thanks in advance for any help.

 

[BoxHead]

I'm not sure where the scores of 10 and 8 are coming from, but to rank your reps by [SRPerform] (I have to assume that [SRPerform] is the number of sales made by each rep)

SELECT Salesrep, SRPerform, DCount("[Salesrep]","TableQueryName","[SRPerform] > " & [SRPerform]) AS RepRanking FROM TableQueryName;"

HTH

 

[jmhicsupt]

Thanks for the quick reply.

The scores of 10 and 8 are predetermined values. So I need to assign these values to the sales reps according to their rank. So the top sales rep would have 10 and the next sales rep would have 8.

Query name = qrysalesrank

So do you know how I would figure this out?

 

[BoxHead]

In a new query, paste the SQL from my last post and replace
TableQueryName with qrysalesrank.

Look at the results to make sure that it is ranking people as you want.

Depending on how many scores you want to assign you could use an IIf statement in your query. If the first query wroked for you, add the blue text from below.

SELECT qrysalesrank.Salesrep, qrysalesrank.SRPerform, [COLOR=blue]DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform]) AS RepRanking, IIf(DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform])=0,10,IIf(DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform])=1,8,IIf(DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform])=2,6,IIf(DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform])=3,4,IIf(DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform])=4,2))))) AS Expr1[/color]
FROM qrysalesrank
ORDER BY DCount("[Salesrep]","qrysalesrank","[SRPerform] > " & [SRPerform]);

Let me know if that does it for you.

 

[jmhicsupt]

I tried your first code for the Dcount. That works wonderful except I would like to start at "1", not "0". Is there a way to start at 1?

 

[BoxHead]

= 1+ DCount....

 

[jmhicsupt]

OK, success by using just your first formula. Now I have another question to complete my report.

There were four sets of queries which I combined with subforms.

Qrysalesrank
Qrysalesunitssold
Qrysalesstorerank
...

Now I need to add up all of the rankings to get the top achiever. In other words, when I add up all four rankings from the four queries, it totals:


Salesrep1 score was 12
Salesrep3 score was 10
Salesrep2 score was 5
and so on

So I want my report to show salesrep1 as highest and then the next highest would be next.

How would I do this?

 

[BoxHead]

Look at the DSum function

Something like

DSum("[rankingField]","rankingQuery","[Salesrep] = '" & [SalesRep] & "'")

HTH

 

[pwbrown]

This seems so close to what I am needing but I am getting lost in the code and what goes where.
I currently have a query [Ranking] that has a calculation that totals [Total]scores. What I am needing to do is rank the scores in a new field [Rank] that will update the field [Ranking] in the table [player information].
The ranking would be lowest to highest starting with 1.
Example:
Total Rank
-66 1
-45 2
-30 3
0 4
61 5
61 6

The total may be duplicated, and in such case, it really does not matter which record is 5 or 6, or 1 or 2, ect.

Thank you in advance for any help