Ranking records

[R. Choate]

I have a table with a list of invoice numbers and corresponding percentage numbers for the income relative to each invoice. These
are real estate deals with frequent multiple brokers getting a piece of the pie. I need to find a way to do an update query to
assign a rank to each broker within each invoice in descending order. The query I created did not return the correct number of
records (too many). For instance, there should be a "1" rank for each invoice for someone, even if there was only one broker on it.
When the 2nd and 3rd and maybe 4tth are equal, the rank doesn't matter, but there needs to be a number for each person anyway.

I am not trying to rank the invoices against each other, I'm trying to rank the percentages within each invoice, thus each one will
at least have a "1", and maybe a 2, 3, and 4.

Obviously, this means I will have a 1 for every invoice, some 2s when there are only 2 brokers, or some 3s for when there are 3
brokers and the 3rd broker is either tied with one or both of the others, or when that person has the smallest percentage.

Please assist.

My table has 5 fields: Invoice Number,BrokerPercent, BrokerName, BrkrRank,Principle

The principle field is a yes/no field that is already populated in the table. BrkrRank is of course the field I now need to somehow
populate/update.

Thanks in advance!

 

[KARL DEWEY]

Use this Ranking in a Group query --
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;