ranking records

[R. Choate]

I have a table with a list of invoice numbers and corresponding percentage numbers of 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 interest in descending order of percentage for each invoice. The query I created did not return the correct number of
records (too many). For instance, there should be a "1" rank for every 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.
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!

 

[Allen Browne]

Take at look at:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html

The article deals with several possible approaches, including DCount(), a
subquery, or a temporary table.

 

[R. Choate]

Thanks Allen. I remember you from years ago. You've been helping people here for a very long time. I'll check out your link.

Richard

--
RMC,CPA


Take at look at:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html

The article deals with several possible approaches, including DCount(), a
subquery, or a temporary table.

 

[R. Choate]

Allen,

It appears that the examples given in your website are trying to rank the customers among each other, based on their order totals.
I'm needing to rank (using that example) the orders within each customer based on each order amount. Each customer would therefore
have a little ranking group within its orders. I further want to update the existing data in one of my tables with the rankings. Any
ideas, or did I miss something?

Thanks,

Richard

--
RMC,CPA


Take at look at:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html

The article deals with several possible approaches, including DCount(), a
subquery, or a temporary table.

 

[R. Choate]

By the way, what time is it in Perth?

Currently, now that American dst just kicked in and moved the clock forward an hour for the next several months, it is 3:00 AM here
and I'm fading. I think mistakes are going to follow if I stay up later.

--
RMC,CPA


Take at look at:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html

The article deals with several possible approaches, including DCount(), a
subquery, or a temporary table.

 

[Allen Browne]

The example assumes a table named tblInvoice, with fields:
- InvoiceID primary key
- CustomerID foreign key to the customers table
- Amount currency
In a real database, the Amount might be in a related table, so you would
need to create a query, group by the invoice and sum the dollar value to get
the Amount, and then use that query as the source for the query below.

SELECT CustomerID,
InvoiceID,
Amount,
(SELECT Count("InvoiceID") AS HowMany
FROM tblInvoice AS Dupe
WHERE Dupe.CustomerID = tblInvoice.CustomerID
AND Dupe.Amount < Invoice.Amount) + 1 AS TheRank
FROM tblInvoice
ORDER BY CustomerID, Amount;

 

[R. Choate]

I think I'm still not expressing my goal correctly. You described in your reply that I would first make a query to sum the order
amounts and then use the 2nd (ranking) query. In my case, I have percentages instead of dollars, and most importantly, I don't want
to find totals for them. I want to rank the order amounts, individually, for each invoice. Invoice #1 has customer name "Smith", and
the invoice number (NOT a primary key in this table) will be repeated for each of Smith's 3 orders ($50, $30, $30). I need to rank
them as Order #1 = $30, rank = either 2 or 3 (doesn't matter with tie), Order #2 = $50 and needs to be ranked as #1, and order #3
should be ranked as #3 (or #2 as mentioned above). Then, the next invoice will start all over with its rankings and have another 1
thru whatever number of orders are associated with it. Hopefully I've explained myself better this time.

Thanks

--
RMC,CPA


Take at look at:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html

The article deals with several possible approaches, including DCount(), a
subquery, or a temporary table.

 

[Allen Browne]

Perhaps someone else has a suggestion, as I don't seem to be getting what
the problem is.

Whether the customer is involved or not doesn't change the logic. (It might
mean you use a different pre-query to sum the totals.)

Whether you rank by dollar amount of percentage doesn't change the logic.
(Again, it may require another query to figure out the percentages, and you
use that one as a source 'table' for the one that gives the ranking.)

 

[R. Choate]

Hi again,

It is probably just me. To re-phrase what I need to have as an end result, I need for the records, as you scroll down the table you
will see rankings that go (for example), 1, 2, 1, 2, 3, 1, 1, 1, 1, 2, 3, 4, 1, 1....

Where I lose you is that your website showed a query which summed the dollars. For my case I'm trying to understand the need to sum
anything. Perhaps this is simpler than I'm thinking right now.

--
RMC,CPA


Perhaps someone else has a suggestion, as I don't seem to be getting what
the problem is.

Whether the customer is involved or not doesn't change the logic. (It might
mean you use a different pre-query to sum the totals.)

Whether you rank by dollar amount of percentage doesn't change the logic.
(Again, it may require another query to figure out the percentages, and you
use that one as a source 'table' for the one that gives the ranking.)

 

[Allen Browne]

If you already have the total (or percentage) and don't need to sum
anything, you can just put it in the ORDER BY clause.

Now the records are in order, you can COUNT the preceeding records, to the
ranking.

I see you've started a new thread on this, so we'll see what responses you
get there instead of continuing this one.

 

[R. Choate]

I sent the post in the other thread early today (my time) and now it is almost midnight, so nobody has answered it in over 14 hours.

--
RMC,CPA


If you already have the total (or percentage) and don't need to sum
anything, you can just put it in the ORDER BY clause.

Now the records are in order, you can COUNT the preceeding records, to the
ranking.

I see you've started a new thread on this, so we'll see what responses you
get there instead of continuing this one.