Record automatic ranking on form

[aat]

Hi all,

I learnt Access for a personal database and from many tips taken from that forum without proper training though so apologies in advance as you see will guess from my questions that I am not really experienced.

I have a number of records in my database and I use a form to input information, update fields etc. One of these fields is a ratio. I would like to create another field, in the record, input from the form, that automatically generate the ranking of the record in the database using the ratio, in descending (or ascending) order. So if I have 1000 records, the field would return, right after I update the ratio, which ranking within these 1000 it now holds.

Is this possible? In the event two (or more) records share the same ratio, what happens?

Thank you,



Aat

[aytee111]

"Is this possible?" Yes. In the AfterUpdate event of the field that will be used to calculate the ratio, put the actual calculation.

"In the event two (or more) records share the same ratio, what happens?". This is a design question, not an Access question - what do you want to happen?

[aat]

Hi Aytee111
Thanks for replying.
I realise my question was not clear at all.
"In the AfterUpdate event of the field that will be used to calculate the ratio, put the actual calculation." >> Do you know the calculation? That's what I'm after.
If two share the same ratio, then they should have the same ranking, basically. If the ranking is 1 to 1000 and both records arrive immediately after the record 546 for instance, then they should be both red 547, and the next record, logically, should be 549. Will this be figured out by Access or does it depend on the way the calculation is written?
Thanks again.
Aat.

[aytee111]

A ratio is a calculation based on at least two values. You - the designer of the system - need to know what the calculation is. What do you mean by saying it is number 549, what do you want to see in the system, what results are you after, etc.? These are questions that only you can answer.

Work out the answers first before you try and put it into Access. Take a piece of paper, put down data for records 1 thru 8, write down what the ratio is for each, then figure out how you calculated that. Then the calculation can be put into Access.

[aat]

Hi Aytee
OK I will word it differently.
I have three records with each a different ratio. A is 0.33, B is 0.56, C is 0.84.
I want to have a field that tells me (the order does not matter, descending or ascending, never mind): C = 1, B = 2, A = 3.
If I add a fourth record with the ratio 0.54, the afterupdate event on the ratio of D should automatically show D = 3, and all other records be equally updated showing C = 1, B = 2, D = 3, A = 4.

Does it make more sense? That is why is titled my thread "automatic ranking".

Thanks for your help.

Aat

[aytee111]

OK. There was another link here a few weeks ago where one could increment a number in a query. I can't remember what it was all about, but someone had an answer for doing that. What you are doing here is an update query - updating all records in a table. It has nothing to do with the form as such, you will need to run this query every time a record is added (or after the form closes). I suggest you start a new thread (sorry, I don't know the answer to this) with those words to do with update query increment, etc.

[alansidman]

Not sure if this is what you intended or not.

http://support.microsoft.com/kb/208946
If not, try google: MS Access Ranking Query

There are lots of hits.
Alan

[June7]

Also, probably should not store this value in table. It can be generated whenever needed by query. A report can show this sequential numbering with RunningSum property of textbox (sorry, not available on form).

Why do you need this value? What purpose does it serve?

[aat]

Hi all, thank for your help.
In response to June7, I need the ranking information on a number of fields (let's say 4 fields that each represent a different ratio), then I do an average of these four ranking on my form (easy), then I know, basically, how does my record do in relative terms (average of four ratios) compared to the rest of my records. It could be essential to my work.

In response to all, given that I have 1000 records in my db, is it likely to take ages each time I update one of those four ratio, to re-calculate the average ranking of all other records?

If so, can I have a query that launch the ranking work only after I press a different button? Say, I update 20 records with new ratios, and only when I am done with my work of the day, I press a button that updates the rankings in the whole table (then I can do something else while Access is computing)?

[aat]

Hi again,

Apologies for the confusion but as you recommended I am going to read the existing resources on ranking queries first and then if I have any question I will come back. Please consider this thread closed for now..
Thank you!