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.
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.