MS Access Help

[Chinbok]

I want to convert my racing spreadsheets into an Access database and am trying to teach myself Access using the help files.

One thing I will need to do is rank each horse's rating within a race. The table will consist of multiple races and there is a record for each horse - one of the records is a rating. Using a query, how do I add a field with the ranking?

The table has a field called RaceID which identifies one race from another in the table. Of course, the number of horses in each race can vary.

Any help would be much appreciated.

[Chrome Prince]

Oh Chinbok,

I bet you found nothing on the net that even comes close to what you require.

I admire you converting to MSACCESS, but ranking and allowing for date track and race is not easy.
Even the Access "Gurus" were unable to help without dribbling on about VBA and still couldn't get it right after a page and a half of code!

I have since played around with examples and think I'm close to solving it, but it takes a while to get it right.

There are two options:

Option A (easy but time consuming)

Do your rankings in Excel prior to importing.

Sort by whatever it is you are trying to rank, then sort by date,track and race.

Then use this code (assuming date=columnA track=columnB race=column C)

=IF(A1&B1&C1<>A2&B2&C2,1,F1+1)

Assumes column F is the rank field and that row 1 has your column names.

Option B (very hard to explain, but if you get it right, you'll save time in the longrun)

Here's the very best page I found in 3 years on ranking:

http://www.databasejournal.com/features/msaccess/article.php/10895_3112091_6

It uses the Northwind sample database, but is a hell of a lot better than the Microsoft example.

What you need to do is allow for the other three rank by fields in the code.

Create a query within a query and make the second Query an alias.

For no values or 0 values, you'll have to do an update query.

CAUTION- do NOT use an update query straight away to do your rankings as "orphaned" entries will still get a ranking, you need the records found from relationships to only get a ranking.

Instead do a make table query and then update that table, so you have a new table based on the relationships and can run the update on that.

I need a good lie down now ;)

Best of luck - told you it's hard.

[KennyVictor]

I don't use access or excel but do use a database. I would have thought that the easiest path would be to export the access data out to a csv file and read that in to the database. Then do what databases are good at (although I have heard less than complimentary things second hand about access compared to "real" databases) and create your rankings within the database.
KV

[Chrome Prince]

Yes, that is close to option A and the easiest if you're not prepared to devote a LOT of time to getting it right.
What do you use Kenny, I use Excel Access and finally Filemaker to achieve the end result.

[KennyVictor]

I use a thing called Powerflex - it's a derivative (and a much improved one at that) of Dataflex (which is probably another one you haven't heard of). Dataflex was sort of like DBase IV.
Powerflex is good in that it also has commands for accessing the net and what have you so I can do everything I need within one program.

[Chinbok]

Thanks for the reply CP. I want to avoid switching to and from excel/access because I want to run >100,00 queries where a rating is calculated and profit/loss stats are calculated looking at the highest rater. Would probably take too long if I had to use both programs.

I could do it now in excel but it would also a long time because my files are so large. I have 6 files about 25Mb each.

A couple of other problems;

I run a query which produces a list of selections with win dividends. I can then run another query on this table to give me:
- no. of selections
- no. of winners
- total of win dividends, etc.
Can I use this query to also calculate the WIN SR and POT? I've worked out how to do it by running another query on the first one but was wondering if it could be done as one.

and

In VBA/excel, I can write code with nested for..next loops to run multiple calculations over and over. Can this be done in Access with a Macro or would I need to use code?

Cheers

[Chrome Prince]

Chinbok,

Well with that many records you really have to use Access.
My Excel suggestion was to import rankings into Access as you build up the database, but what you want is rankings based on queries?

If your Excel files are that large, you risk data corruption as well - I lost a whole Excel master list 6 years ago which had months of figures in it :(

Yes, you can include the other calculations, just create a new calculation field in the query based on the Win dividends.

Example POT:Profit/Number of Bets as a calculation.

Yes you can use a macro, just create a recorded macro and modify it later to suit your needs, then run it as a loop.

You can do just about anything in Access you can think of, BUT some things require a lot of trial and error, such as the dreaded ranking issue.

All over the place you find examples of ranking a set of records in a query - just about nowhere can you find rankings by date,track race.

Have a look at that link I posted, if you can follow the tutorial, you'll catch on quickly how to modify it to include the various categories as well.

If anyone else has a better reference or idea for rankings, I'd sure welcome it.

[shoto]

CP,
Don't know if this will be helpful or if you're way ahead of this, but ...

In Filemaker data is easily ranked by creating a related file and displaying the required data in a portal, with the data sorted by a field that can be easily changed dynamically using a script. With the data sorted in the desired order, a script inserts the portal row number into the ranking field, and looped to run through a group of records. Exclusions can be easily accomodated with 'if' or 'case' statements.

The shortcoming of this is that it will give each line a ranking, even if the data is equal, but for me that is a small price for such ranking ease.

[Chinbok]

CP,

Read the article last night which has helped a little. I can now rank all horses in the table. Still not sure how to make it differentiate between races in the table. Is there a mrexcel.com equivalent for Access or SQL?

Cheers

[Chrome Prince]

Thanks Shoto,

Yes, I'm aware of that method, but it is very very slow and cumbersome when dealing with 2gb of data. I also try to keep the speed of Filemaker as fast as possible by having all calculations done in Access prior to import.

Thanks for the suggestion though.


CP,

Read the article last night which has helped a little. I can now rank all horses in the table. Still not sure how to make it differentiate between races in the table. Is there a mrexcel.com equivalent for Access or SQL?

Cheers

Chinbok,

That is the trick, the differentiation between races.

Here is some help:

Rank: (SELECT COUNT (*) FROM QrySTAKES WHERE QrySTAKES.STAKES > QrySTAKES1.STAKES And DATE=QrySTAKES1.DATE And TRACK=QrySTAKES1.TRACK And RNO=QrySTAKES1.RNO)

This is the calculation I use for ranking horse prizemoney.
QrySTAKES is a query based on date track race horse prizemoney.
QrySTAKES1 is the alias of QryStakes.

QrySTAKES MUST be sorted date ascending, track ascending, race ascending,prizemoney descending.

You won't find much help out there on doing this, as nobody knows or could be bothered finding the answer,believe me I've posted on every Access forum there is internationally, all they do is tell you how to rank a set of records, not within the set like we need.

So I came up with my own derivation by trial and error.

[Chinbok]

Thanks CP, done it now.

I actually have a field called RaceID which is the concatenation of Date&Venue&RaceNo so made it a bit easier. Only problem is how long it takes - a couple of minutes to process 1 month.

I found some code here:
http://www.access-programmers.co.uk/forums/showthread.php?t=55156
which does it in a couple of seconds.


Cheers

[Chrome Prince]

Thanks for the link Chinbok - I'll investigate it later.

Speed is not an issue for me though as I won't be ranking by query (each time that is) just updating a make table for export.

A couple of considerations though....

How will you handle records that have a blank for data and records that have a 0, and tied records etc.

My ranking system is more complicated than a straight code.

I have to consider that two horses with $20,000 prizemoney are not necessarily the same, one may have 20 starts and one may have one start.

So API comes into play.

The same applies with two horses with $0 prizemoney.

It's never "plug and play" :(

[Chrome Prince]

Only problem is how long it takes - a couple of minutes to process 1 month.
Cheers

Mate, that's a luxury, when I first started doing rankings in Excel 6 years ago, some "bright spark" told me to do a sumproduct and array calculation which took more than 8 hours for a few months data!!!

It only dawned on me later that a simple sort and rank formula would take less than a few minutes - and that was the copy and paste time ;)

:D