Access Coding Question

[Cliff]

I'm trying to do some analysis without the MLO favorite but am not sure how to code the "expression" or query to avoid these guys for each individual race. Using Access 2000. Am sure someone has this in use already. Would also like to eliminate horses (for analysis purposes) of over a certain MLO. Am assuming it would work basically the same way.

Any suggestions?

Thanks!

[Rick]

Check out this thread for using more than one table in a query:

http://www.homebased2.com/forums/showthread.php?threadid=89

The Impact table has the field rMLO which is the ratings for MLO. To eliminate the MLO favorite all you have to do is put in criteria of NOT EQUAL to ONE (<>1).

If you don't want to use both tables in your query, I think MikeDee has something for doing your own ratings of a field.

To remove horses above a certain MLO just pick the nMLO field and use a criteria of LESS THAN 20 (<20).

If you combined the two tables in your query and used the two fields as part of your query it would select all horses that were less than 20:1 MLO that were not the MLO favorite(s).

[Cliff]

Well,

Thanks Rick. That certainly looks like it would work. The only problem is that I have not been exporting the Impact data yet. All I have is the data exported by HTR4MSAC. Is there no way to accomplish this with this data?

[George]

Cliff,
The MLO field is not "ranked" in the basic htr extract but the field nFAV contains a code.
Code 1 means horse was favorite, 2 means even money or less fav. This means you must
test for (nFAV =1 OR nFAV = 2).
Could also test (nFAV > 0 AND nFAV < 3) to achieve the same thing.

In htrmsa4 you can elim longer prices by testing MLO > nn.n with nn.n = say 4.5.

You can also do your own ranking using VBA which probably won't be learned from reading this bulletin board.

[Carl]

Cliff,

If you want to post an e-mail address, I will send you a "make your own" MLO ranker that Mike Dee created. Worked good, I don't use it now because I merge the two tables and IMPACT contains a MLO ranker built in.

later,
Carl

[Cliff]

Carl,

Yes, thanks....sounds like that's what I need for a short-term solution.

cliffhartnitt@home.com

[MikeDee]

Depending on how much time we have at the seminar and group interest we will go over how to use VBA code for creating your own compound factors from HTR factors, how to rank unraand factors and how to find a difference in the valuse of factors. In the interium if you have questions on what Carl sends you you just email me mrdezo@ameritech.net

[Carl]

MLO ranker sent Cliff. I sent Mike's instructions over as well.

(Any questions about it you are probably much better off asking Mike. I am more the plug and play type.)

later,
Carl

[fred4now]

Regarding nFAV, is this off odds favorite or MLO favorite? Also to include all favorites do you have to include 1 and 2?

[Rick]

The nFAV is:
21. Favored Last nFAV 1=favorite, 2=odds on favorite
0=was not the favorite in last

[fred4now]

Thanks Rick, something strange about this nFAV. If I search for 1 or 2 I get alot of results that were definetly not favorites, like 20-1 shots. Will keep looking at this.

[George]

Fred4now......OR statements can be a little tricky but suspect your results are a more basic problem. When creating the database most numeric fields default to text fields internally. A query then gets strange answers because large numbers and blanks will test "less than" what you are comparing too.

Try changing the nFAV field definition to numeric and then re-type your query. Important to retype as the original query will still have stored the field def as text.

[fred4now]

Thanks for the reply George, the field def. is number. It comes up weird whether I put "1" "2" "1 or 2". I don't think the favorites went off at 20-1 or 15-1.

[Rick]

The nFAV field is from the horses last race.

It sounds like some may be trying to use it as a ranking for the present race.

I know the field is for the last race but I really don't know what numbers mean. It doesn't matter to me if I understand or not. I would test the field. If it worked I would use it and of course I would not use it if it didn't work for me.

If you use the field, just be sure that you know it is giving you info about the last race and not the present race.

[George]

OOPS! Guess I led them down a bad path. Did not look at field very hard and assumed it was for current race. The MLO rank routine is the only solution then. Thanks for the info Rick.

[MikeDee]

Just looked at the documentation the nfav =1 means it was the fav in the race previous to the one you are looking at and nfav=2 means it was a odds on fav. Does not mean that the runner is the no1 mlo rank runner is the current race.

Only was to get what you want is to link with impact data or incorporate the mlo ranking vba module into your db.

[fred4now]

Ohhhhhhh!

Thanks