Title
Numbering a field based on highest number of another field
Post
Hi,
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
Thanks,
Carl
Try this:
Now you have a rank that won't change until you do the above steps again (and this can be scripted).
I'd also give serious consideration to a seprate images table where you have only one record for each image. The above table would then refer to it by an ImageID number so that multiple records of this image selling would all link to the same image record. I'd put the rank number field in this related, one record for each unique image) table so that it will be visible no matter which record for the same ranked image appears in your layout shown above.