You are here:

Using MS Access/Multiple Rankings in a query


I would like to produce a query where related records can be compared with one another and then ranked.  The challenge is that I need to rank each group of records as they relate to one another.

I am trying to compare how 3 related records in a table should rank amounsgt each other.....and so on for each related subset of records.

The practical use is to compare parent investment funds that each hold a different asset.  I need to run a query that will compare the value of each underlying holding to one another and provide a rank order (lowest to highest).  It would then need to move on to the next parent investment fund id and perform the same for its holdings.  So one query could have 12 parent funds, each with 3 holdings (hence 36 records).  I would like to have each holding of a specific parent fund ranked.  The reset the ranking again for the next parent funds.  In this example I would expect to that 12 parent funds ranking 1 to 3 (lowest to highest) each time.

Hope you can help.


Hi Jeff,

Many thanks for the question.

Is this data is one table? or more than one table?

To clarify I have understood correctly.

If in 1 table

Would it look something like this, and can you give me the names and data types of the fields to work with and the name of the table

Parent1, Holding1, FieldTobeRanked
Parent1, Holding2, FieldTobeRanked
Parent1, Holding3, FieldTobeRanked
Parent2, Holding1, FieldTobeRanked
Parent2, Holding2, FieldTobeRanked
Parent2, Holding3, FieldTobeRanked etc

If you could provide me with some sample data, as per above, that would be extremely helpful.

If in two tables, the names of the tables and the fields names and data types of the fields that are needed for the query and some sample data, would also help.  Thanks

If I can have this, then I should be able to help.

Looking forward to hearing from you.


Using MS Access

All Answers

Answers by Expert:

Ask Experts


Julie Misson


My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.


I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the website.

Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2017 All rights reserved.