Creating a ranked value 1

[jmcg]

I have a table with 300 sales peoples achievement and want to create a ranking of each person to show where they are in a league type format against each area.
Table has the following fields:
PERSON
TOTALSALES
SOLOSALES

So the output would be person 1 is 5th in total and 27th in solo.
Have no idea how to rank values in Access so any help would be much appreciated.

 

[cmmrfrds]

Make 2 queries that generate the ranking for each value and then combine with the table for the final query.

qrySolo.

SELECT max(a.solosales) AS ssales, Count(*) AS row, A.person
FROM persontable AS A, persontable AS B
WHERE (((B.solosales)>=A.solosales))
GROUP BY A.person, a.solosales
ORDER BY A.solosales DESC;

qryTotal.

SELECT max(a.totalsales) AS tsales, Count(*) AS row, A.person
FROM persontable AS A, persontable AS B
WHERE (((B.totalsales)>=A.totalsales))
GROUP BY A.person, a.totalsales
ORDER BY A.totalsales DESC;

Final qry

select
'what you want here'
from persontable, qrySolo, qryTotal
Where persontable.person = qrySolo.person
and persontable.person = qryTotal.person

 

[jmcg]

Excellent thanks.
A good solution simply explained

 

[cmmrfrds]

You are welcome.