Ranking Data In Access

[charliec]

I have three fields: State, Total Sales, Sales Person. In a query, what is the formula/syntax for calculating the ranking (most to least sales) of sales persons in each state. I tried- New Ranking:Rank([Sales Person], [Total Sales], [State]) but I got an UNDEFIND FUNCTION error.

Any ideas?

Thanks.

 

[sillysod]

essentially you need to sort, dont think theres a function called rank.

The SQL for the query you would base your table would be something similar to this

SELECT [state], [sales person], Sum([Sales])
FROM where ever
GROUP BY [state], [sales]
ORDER BY Sum([Sales]) DESC;

This would show a total sold by each salesman in each state, this is then sorted descending by the value of the order.

So the person with highest sales will show first.

This can be further refined to only include the top 10 or 5 etc etc rows.

Hope that helps

 

[TheAceMan1]

How are ya charliec . . . . .

Have a look below:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208946&Product=acc2000

See Ya! . . . . . .