I have searched multiple places for this information and find nothing which can help me achieve my goal.
I have a query which pulls data from numerous tables and performs calculations on certain fields.
The resulting query shows 4 fields with calculated data.
I can sort these fields ascending or descending.
If I sort field 2 descending, then field 1 is random.
I would like to add a ranking number to each field so that I can easily sort any field, yet have a ranking number for the 3 other fields.
In other words I wish to display 8 fields - Field 1 Data, Field 1 Ranking, Field 2 Data, Field 2 Ranking, etc.
Example of what I wish to see:
I sort field 2 descending (meaning the high value = rank 1) and I can see that for this record it ranks 5th in field 1, 3rd in field 3 and 10th in field 4.
Is this possible and how would I go about achieving such?
You see, I have tried that, but I run into problems.
The example I mentioned lists 4 columns of data, but that is a sample. If I estimate roughly I will have at least 8 columns such as this. Perhaps more.
One solution would be to fill a table for each column using an update table query, which would have an autonumber field. This can work and is dandy, but the problem comes that the data needs to be refreshed regularly (at least daily) and thus the records need to be deleted in each of these tables and in order for the autonumber to reset to zero, the database needs to be compacted. This equals a lot of work getting all the tables empty and refreshed and a macro cannot compact the database unless it is run from a second database.
I cannot find a solution which will allow me to use the Make Table query, since I do not know how to add a new column to the table which will contain an autonumber field.
No, autonumbers aren't the way to go... I was thinking about recordset processing. How many RECORDS are we talking about?
I still think the SQL gurus around here might have something more efficient -- I see them talking about numbers tables to do stuff like rankings, so I'd wait to see if any of them reply before doing much.