Results 1 to 10 of 10
  1. #1
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30

    Record automatic ranking on form

    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

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "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?

  3. #3
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30
    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.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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.

  5. #5
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30
    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

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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.

  7. #7
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    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

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30
    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)?

  10. #10
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ranking (Look for previous ranking)
    By leobear in forum Queries
    Replies: 3
    Last Post: 01-10-2012, 05:58 PM
  2. ranking: can i query a report?
    By stevepcne in forum Reports
    Replies: 2
    Last Post: 11-20-2011, 01:03 AM
  3. Ranking and assigning a Value
    By loopyl00 in forum Access
    Replies: 0
    Last Post: 01-11-2011, 12:14 PM
  4. Help to build a ranking
    By chorbi26 in forum Access
    Replies: 0
    Last Post: 12-20-2010, 12:47 PM
  5. Automatic Filtering when Opening form
    By Karyn-2000 in forum Forms
    Replies: 1
    Last Post: 12-03-2005, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums