Numbering a field based on highest number of another field
I created a database for a stock photographer that will show his best selling photographs. The image below is what i have so far, the images are the current top 4 selling images.
What i am trying to do is put in a field that will give each of these a ranking number based on the Total payable field.
The quantity number that is positioned to the left of the the image thumbnail is telling you how many times the image has sold. The baby has sold a total of 14 times and the total payable field that is on the far right of the screen is calculating all 14 sales together.
The rank field would say that the baby picture is ranked #1 and then the next would be ranked #2 and so on. I need the ranking to change based on the search criteria. For example, in this search, the baby is ranked #1 because it has the highest Total Payable.
If I do a search that is for a different time period, another image might have a higher total payable, and therefore would be ranked #1, and so on. So the rankings change based on the search time frame because the total payable sales changes based on the time frame chosen for the search.
Anything you can do to help would be appriciated