How to rank and assign a value?

[Lisa B]

Hello, I've been trawling the web trying to find how to build the expression to rank how many machines an individual has sold by date and to assign points for the ranks - so he who sold the most 6points, 2nd 4 points, 3rd 3 points, 4th 2 points 5th 1 point.

This is how my query is set out

Thank you

[normalice]

i'd imagine the sales are stored in a table somewhere?

in that case there are first two questions you need to know in order to solve this: what to do in the case of a tie and what to do in the case that all but 4 or less people sold zero machines.

your question is similar to generating praeto charts, which in general, you will have to set up two queries for those. Because you want to add the extra dimension of points, you will need two queries and a table: however it will look cleaner with three queries and a table so that's what I'm going to do here.

the first will just be a sum query (let's call it query A), like this:

select [Date], [Person], Sum([Machines Sold]) as Sold 
from [tblSales] 
Group by [Date], [Person]

your second query (let's call it Query B) will look like this:

select [Date], [Person], nz(DSum(1,"[Query A]","[Sold] >" & [Sold]),0) as Rank 
From [Query A]
Where nz(Dsum(1,"[Query A]","[Sold] >" & [Sold]),0) < 6
Group By [Date], [Person]
Order by Rank

now the '[Sold] >" & [Sold]' may need to be a '[Sold] >=" & [Sold]' depending on how you want to treat ties. I recommend not doing this, as it gets tricky.

also, you may need to make a small "point allocation" table with two integer fields: rank and points. depending on how you want to treat ties, your "point allocation" table may need to start at rank 0 (in the case of > start with rank 0 in the case of >= start with rank 1)

in addition, you will need a third query that would look like this:

Select [Query B].[Date], [Query B].[Person], [Point Allocation].[Points]
From [Point Allocation] Left join [Query B] on [Query B].[Rank] = [Point Allocation].[Rank]
Group By [Date], [Person], [Points]
Where isnull([Date]) = False

here, the left join may need to be an inner join depending on how you want to handle situations when all but 4 or less have sold 0.

[Lisa B]

Thank you for your quick reply

Sales are stored in the table [Weekly Machines Sales Data]

the people are Vendeurs

When i adapt your first query with

Rank: (select [Date], [fkVendeurid], Sum([Machines Sold]) as Sold  
from [Weekly Machines Sales data] 
Group by [Date], [fkVendeurid])

I'm getting an error

You have written a subquery that can return more than onefield without using the EXISTS reserverd word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field

(oo this is giving me a headache!) - thanks so much for the help

[normalice]

what version of access are you using? that's not an error i've ever seen so i might be using an older/newer version than you?

also, your [Weekly Machines Sales data] table, does it have a [Machines Sold] field? I don't see it in the picture, and that would cause an error if it didn't have that field.

If not and each entry counts as one sale, try changing the 'Sum([Machines Sold])' to 'Count([fkVendeurid])'

[Lisa B]

Am using Access 2010 - am happy to send you the file :P

yes Machines Sold exists - 6th column in [Weekly Machines Sales Data]

[Lisa B]

I have uploaded a zip with the db file in it herehttp://cid-4490afdc094900ec.skydrive...DC094900EC!368

I'm aiming towards having a ranking with value assigned for most machines sold and largest margin for each seller and for each team(equipe) for any given date period

Thanks again

[normalice]

Hmm.

I'm using Access 2003. I wouldn't think the SQL syntax would be so vastly different, but apparantly it is.

it works fine in my recreation, and I can't open yours since I'm using 2003 :-/

[Lisa B]

Unfotunately i can't save as 2003 version and it's using features that don't exist in 2010 :(

[Lisa B]

Hi again

I am successfully ranking several queries now, however, I don't want it to rank if the value is 0, I want it to return a zero ranking

This is my expression

Rank: ((Select Count(*) FROM QryDistinctFacturationEquipe Where [SumOffacturation]>AliasSumofFacturationEquipe.[SumOffacturation])+1)

So, what do I add to tell it to return a 0 rank if the SumOffacturation=0?


thank you

[Lisa B]

This may help too?

SELECT AliasSumOfFacturationEquipe.SumOfFacturation, AliasSumOfFacturationEquipe.Equipe, ((Select Count(*) FROM QryDistinctFacturationEquipe Where [SumOffacturation]>AliasSumofFacturationEquipe.[SumOffacturation])+1) AS Rank, IIf([rank]=1,6,IIf([rank]=2,4,IIf([rank]=3,3,IIf([rank]=4,2,IIf([rank]=5,1,0))))) AS Points
FROM QrySumOfFacturationEquipe AS AliasSumOfFacturationEquipe;

thank you

[normalice]

I think the nz function works for count. Try:

Rank: ((Select nz(Count(*),0) FROM QryDistinctFacturationEquipe Where [SumOffacturation]>AliasSumofFacturationEquipe.[SumOffacturation])+1)