Access Help - Creating Ranking Within A Race

[Sheets Guy]

Say I have created a variable based upon some multiple regressions I've run and end up with a value for each horse in every race. Once that's done how can I use Access to rank each of those values (within the race) from lowest to highest (low is better in this case). They might look something like this

RACE 1 HTR DOWNS
A: 15.25
B: 12.95
C: 26.90
D: 8.00
E: 10.25
F: 9.5


RACE 2 HTR DOWNS
A: 5.50
B: 6.75
C: 10.25
D: 8.00
E: 11.00
F: 4.50

I don't want to run a query using the raw # as in the above examples a rating of 8 would be far and away superior in the first race and yet only rank 4th in the 2nd race.

I'm not a great coder and don't know how to write the code to rank order a variable on ttrack/tdate/nrace.

Many thanks in advance.

Ian

[Donnie]

Ian-
It would be simple to do in a report, grouped by track, then grouped by race, then sorted on that fireld. Here is a ink if you wish to get started into doing it in the query itself.

http://support.microsoft.com/kb/208946

[Sheets Guy]

Thanks Donnie.

I learned (what little I know) about using Access starting with Mike D's HTR template. If you or anyone else had some code snippet I could modify to create a [rIan] from the [nIan] variable I created in my queries that would be a great help. In the MS example they are ranking within a database not grouping certain records together and I didn't understand how to make the leap to ranking within a given race.

[Rick]

You need to get in contact with MikeDee. He has something he did in VBA.

I think someone also came up with a way to do it in a query but that was a long time ago and I don't remember who it was.

[Donnie]

Ian--
do you have any experience in SQL querying? I can send you a manual that would explain exactly how to do it in SQL. I can email the entire manual, or give me a day or two and I can get it typed up and posted here!

[Sheets Guy]

Donnie:

Thanks for the offer. I have NO SQL experience but if you have a chance to post the appropriate pieces of code here I can probably apply it to what I'm trying to do.

In the meantime I know of a jerryrigging way to do it using Excel. Unfortunately Excel limits you to 66k records so I've got to do it in stages...

[Donnie]

Sounds like it is time for you to upgrade to 2007....1 milion + rows in there. I will check back tomorrow. If Mike asn't replied with his module, I'll post up the code to get you started. I need to get it out of one of my books at work tomorrow!

[Donnie]

Ian.... or anyone else who would like this info...drop me an email at: donnie @ htr2 . com (remove the spaces!)

[HBee]

Hi Donnie,

Does Acess/Office 2007 still have the 2 Gb table size max?

[Donnie]

Yes Herman, it is still 2gb. Here is a nice page with all the limits:

http://blogs.msdn.com/access/archive/2006/06/05/access-2007-limits.aspx

[MikeDee]

Ian,

I do have a generic vba module that will rank.

I have attached a zip file with "how to" instructions.

make a new module in vba and then make a new procedure in vba and paste the contents of the ranking doc into the procedure.

comment lines will appear in green and they are the instructions on what the modue is doing.

You will need a query with your value and rank field and the date track and race no.
The program will ranks the contents of the query and you can control what is ranked in your db with the date track and race criteria.

IMPORTANT - be sure to set the sort order in the query as
date
track
race
your value
(be sure to set this ascending or descending based on which is the better no.)

There are instructions on how to set up the references so that vba will run in your mdb. After access 2000 vba dose not run automatically. When setting this up use the most recent DAO library dates.

Finally a document has been included on how to add a button to a menu so you can run the program.

I will not be on the board very much during the holidays, but post any questions and I will get back to you when I can.

[MVM]

I've always done ranking through a simple query. Thought I posted this a couple years ago but could not locate the earlier thread.

The SQL statement below assumes you have a query (named qryIan in the SQL) with the fields (I used the HTR names) tTRK, tDATE, nRACE, tPGM, nIan (nIan being the calculated field you are using to rank the entrants).


SELECT sq.tDATE, sq.tTRK, sq.nRACE, sq.tPGM, sq.nIan, (SELECT COUNT(*) FROM qryIan WHERE sq.tDATE = qryIan.tDATE AND sq.tTRK =qryIan.tTRK AND sq.nRACE = qryIan.nRACE AND qryIan.nIan<sq.nIan)+1 AS rIan
FROM qryIan AS sq
ORDER BY sq.tDATE, sq.tTRK, sq.nRACE, sq.nIan;


The above assumes that the lower the value of the nIan field, the better. If you need to reverse the logic and assign the 1 ranking to the highest value f nIan, change the less than (<) operator in the statement to greater than.

[Donnie]

Mike--
have you ever tried the Rank and Partition Over keywords?
Was gonna post it but not sure it works in Access.....

[MVM]

Which Mike?

[Donnie]

I guess either of you...do you know if Access supports Rank? I know SQL server does.

[njcurveball]

This is my oracle SQL. B1 is the factor you want to rank.


SELECT TTRK, TDATE, NRACE, NSURF, TPGM, THOR, HX4_id,
B1,
RANK() OVER (PARTITION BY TTRK, TDATE, NRACE
ORDER BY B1 DESC) RB1,


I do not think it is available in Access. Check out this page if you haven't already.

http://support.microsoft.com/kb/208946

[Donnie]

Thanks Jim! Yeah, I tested it in Access SQL but Rank does not appear to be supported in Access. I use it in SQL Server. That page looks similar to Mike's SQL statement.....

[Sheets Guy]

Ian,

I do have a generic vba module that will rank.

I have attached a zip file with "how to" instructions.




Thanks MikeDee, Donnie, MVM and NJcurveball for all their help on this. Special thanks to KM and Rick for all their help throughout the year. I feel like I'm being a pain in the ass sometimes and I apologize if that's the case. The support by users for other users of HTR is second to none. It make me proud to be part of this community.

Merry Christmas and a Happy New Year to all.

Ian :D

P.S. A big :p to OPM who won't handicap horses with me again until he can bet into a 10% takeout. :(