Ranking in groups

[Stewart]

I have a set of data which I need to rank by country.
There are 100's of results from 50 different countries in
one table. I want to be able to rank those results by
country, not by all results.

I hope this makes sense and that someone will be able to
help.

Thank you

Stewart
MOS Excel Expert(I wish it was expert in Access!!!)

 

[Michel Walsh]

Hi,


DCount("*", "tableName", " Country=""" & Country & """ AND qty >=" &
qty )


assuming the country is alphanumerical, and that you order accordingly to
the value in the field qty. Since the Count occurs only over the records
being of the same country, the rank is "by country".


If country is numerical, you can try, as computed column:


DCount("*", "tableName", " Country=" & Country & " AND qty >=" & qty )

or an equivalent all-SQL statement

SELECT a.*,
( SELECT COUNT(*)
FROM myTable As b
ON b.country=a.country AND b.qty>=a.qty
) As rankByCountry
FROM myTable As a





Hoping it may help,
Vanderghast, Access MVP