RANKINK#

[ronnie h]

I HAVE ADDED F1+F2+F3 HOW CAN I RANK THEM ?

[MikeDee]

The way I rank compound factors like the one you have made requires that you make a new field in your table, like say rFRT, for rank FR total. Then I use a vba module to do the ranking. let me know if you are interested and I will post some code and walk you through getting it working.

Are you just adding the rankings (1 through 9)?

[tbrown]

Mike,
I would be interested in ranking calculated fields, too.
Can it be done within Access or do I need to buy V-Basic?

[ronnie h]

mike dee
i would like to try your way. every year i like to learn at least one thing new


thanks for your help

[tbrown]

Originally posted by reddy
mike dee
i would like to try your way. every year i like to learn at least one thing new


thanks for your help

I try to learn one new thing every year, too. Hopefully, I get it out of the way early
~G~

Happy New Year everyone, may your pockets bulge and you hard drives not crash.

[MikeDee]

I'll get some stuff together and post in the next day or 2.
TB- you don't need any additional software access has a vba code built in.

[MikeDee]

I have put together some instructions on how to build a ranking vba module. I have attached them to this post. I recommed that you download this, save and print it. I have put the actual code you will need in a second document that is attached to the following post. How to use the code is covered in the attached instructions.

Hope it works:) have fun and ask any questions, I'm sure we can get it working for you.

[MikeDee]

Attached to this mesage is the text you need to insert into your vba modue. download this and save it. You can open it with Microsoft Write, Word or any word processor you use that can read a rtf text file. Just open this document and do a copy on the contents, then go back to Access and do paste the text into the ranking vba function as covered in the instructions.

[MikeDee]

Reddy food for thought. You might want to base your ranking on the actual FR1,2,3 velocities rather then the the fr1,2,3 rank consider the following:
tTRACK nRACE fr tot rank
AQU 8 4 1
AQU 8 12 2
AQU 8 12 2
AQU 8 15 4
AQU 8 15 4
AQU 8 16 6
AQU 8 17 7
AQU 8 17 7

tTRACK nRACE fr tot rank
AQU 8 174.30 1
AQU 8 172.30 2
AQU 8 171.26 3
AQU 8 170.98 4
AQU 8 170.40 5
AQU 8 170.15 6
AQU 8 169.95 7
AQU 8 169.42 8

You get alot of ties when you rank the rankings this is a common occurance 3 ties in one race but if you add the velocities instead and then rank them the decimal numbers solve the tie problem same race and runners no ties in the ranking.

Notice that you have to do your sort in desending rather then ascending. (This is covered in the instructions).

[ronnie h]

mike
Ihave three tables msa, impact ,2001 iguess i should go ahead and make a master table first

[MikeDee]

If you are presently bring in all three tables, then I would recommend that you join them in query. I would then take all of the fields from htr 2001 and put them in the design and then add of of the unigue fields from htrmsa and impact and put them in the design.

When this is done just change the query to a make table query and make a new table, this will be the begining of your work bench and master table. After you make this table then save the qauery as an append query and you can use it to append addtiional data to your work bench as it becomes available.

[ronnie h]

mike

if i use any other numbers for ranking would the code need to be change. still have not got the fr 1,fr2, ,fr3 yet but learing more every day also i right click in the box and use the build experession. is that o.k?

[MikeDee]

The code does not need to change as long as you FR Tot as the name for the result of your calculation. The program is looking for the field named FR Tot. and you can use the expression builder to build your formula. Just start it with Fr Tot:

If you want to change this you can go into the code and with the edit command do a find and replace on the term FR Tot replacing it with the name you want to use

Same thing would apply to the name rMRk as the program will use this field to put the ranking result. If you change this name just chagne the name in the code with the find and replace.

Also you don't have to save new code to test it just make the changes and run it in the debugger if all is well you can save your changes