HX 7?

[dehere]

Hey, I was wondering if sometime there could be a database download which would simply include "raw" data rather than a database which is based on various HTR ratings? It would be nice to be able to look at race fractions, beaten lengths and such on a historic basis. Is that possible and reasonable?

[km]

Try HX5 - it's all there.

[dehere]

Thanks KM, I can see that fractions and beaten lengths are part of HX5, but it seemed to me that the purpose of HX5 was to create past performances for a current race rather than a historic testing tool like HX4. My Access capabilities are in such a beginning stage that it is hard for me to imagine running a test using fractions and beaten lengths and tieing together data from HX4 and HX5 to get the intended results. Is that possible?

For example, lets say I think I see a trend where 3 year old horses who have a turn time between 22 2/5 and 23 seconds in a nine furlong race on the turf end up wining (or losing for that matter) an inordinate amount of time irrespective of post time (or morning line) odds. I want to test if that perceived trend actually occurs. I don't think that test is possible using HX4 alone. Is it possible to do a test using all races for a given period of time at one track by tieing together data from HX4 and HX5?

I hope this inquiry made sense.

[km]

Dehere;

Rick, MikeDee or Donnie can answer your question about merging the HX4 and HX5 (yes, it can be done). I cannot put them together from my end because the maximum number of data fields is 256 that can be imported into Access and both of these exports have already reached the limit individually.

[Rick]

Read this then ask questions

http://www.homebased2.com/km/pdf/Relationships%20in%20Access.pdf

Donnie put that together.

It was written for HTR4MAS but just substitute HX4 and HX5. The ideas work no matter what the table names are. :D

[dehere]

Thanks KM and Ricks. I've been working with your suggestions and now have a completely new set of HX4 and HX5 databases to play with and, miraculously enough, there are almost an identical number of records in each of these two databases (they're off by one for some reason). So, I go to set up the relationship and a problem develops. It seems easy to set up one relationship between say "tdate" and "tdate", but I cannot figure out how to set up multiple relationships for the other three fields. Do I set up a new relationship for each? or is there a way to incorporate all four fields in one relationship? I don't wanna mess this thing up now that I've gotten this far. Once again, your help is appreciated.

[Rick]

This is the way I do it. I make a query and include both tables at the top. I then create the relationship between the two by dragging the field from one list to the matching field in the other list.

You do that by left click, hold click, and drag the field to the other list.

This creates a one to one relationship. That means that it will only use the records that are in both tables.

[dehere]

I got that Ricks, but I think your answer applies to only one link. My question was, what do you do to get all four (tTRK, tDATE, nRACE and tPGM) linked? Do you create four relationships or is there someway to link all four at once? I tried creating a new relationship three more times and, when I open a query design view, it looks as if all four are linked. I haven't tried doing a query yet so I can't say for sure, but it seems to be working. I think this is exactly what I was looking for.

[Rick]

My answer pertains to one way of creating a relationship between the two tables in a query. I have no trouble dragging the four fields to their matching fields in a query. When you do this you should see a line from between the two list.

You can also click on Tools, Relationships, and create the relationship through there.

Me, I just try to take the easy way out. I took the original ALL_MSA Misc Query and joined the two table together in there cause it already had an ROI report and I did not like the idea of having to create a new ROI report.

Whatever work for you.

[Donnie]

Dehere-
If you make the relationship in the Relationship window, as Rick had instructed above, it is considered a "permanent" relationship. In other words, everytime you create a new query based on those two related tables, the relationship will be "brought forward" and you don't have to manually create the link with each new query. Also it's easier to link in the relationship window. As soon as you pull on field onto another, a box opens up and there are two columns under your fields you just linked. Click in the first empty cell and use the down arrow to select your second linking field, then find the corrosponding field in the second table. Do this until all four fields are linked. Save the relationship window before closing. Now go query your brains out!

[Rick]

dehere,
I went back and reread your message.

In making the Relationship from Tools, Relationships, after you drag one field from one list to the other and the Edit Relationship box opens:

Just click on the blank box under tDATE and click on the arrow to open the drop down list. You can then select the other three fields by doing this.

I hope that answers your question.

[dehere]

Good news, bad news. The good news is that process worked! I can get data from both HX4 and HX5 by linking them together. The bad news, however, is that it doesn't do me any good.

While I can link these two tables, I cannot accomplish my objective Essentially, it makes no real sense to link HX4 with HX5 because the data included in HX5 is historic data for the last ten starts for each horse in the race with nothing for the current race other than post position and other things which are already included in HX4. What I am looking for are internal fractions for the race for which the other chart data is provided at the end of the HX4 table. HX5 does not include any charts or other results data so there is nothing in that table which provides the internal fractions for the race which is being run. Data in HX4 provides results and beaten length data but that beaten length data has much less utility absent the race fractions. If race fractions were included I could then calculate internal fractions and ascertain the relationship between those internal fractions and the race outcome.

Of course its possible that I'm missing something, but it looks as if I need three of four more columns in the HX4 database for race fractions. Alternatively, an HX7 or some such export could be created which would only include chart data, including race fractions. If something like this were created it might also be nice to include exotic pay-offs and race comments. Creating a new, comprehensive charts table would also reduce the size of HX4 and, then we could simply link the two tables with the relationship process and do all sort of testing and get all sorts of nifty reports. Anyway, just my 2 cents.

[km]

I understand what you want dehere, and it IS possible to accomplish a long term task with HX4 + HX5 to get the information you want, but it will not include the 'current race', but would match the HX4 + HX5 with all the previous races for the horse.

What you are asking is to parse the 'current race' data from the chart and insert that immediately into the HX4 export when the results come in. We already pull the finish pos. and final odds, why not the fractions and BL too?

Parsing text chart data, especially fractions and BL is a nightmare of programming. There are so many variations and possibilities that error checking and verifying every corresponding fraction + BL location for all possible distance/surfaces situations from the chart would take weeks of work, debugging and beta-testing. There is no demand for this from other users.

I don't see an advantage to knowing the current race fractions and BL as opposed to the ability to test massive amounts of past data (HX4 + HX5) for the same information. These 'current-race' chart extractions would be "raw" numbers with no adjustments and not comparable to other races or horses without variants, distance adjustments, etc.

If you want a raw data listing of the fractions and 1-2-3 finisher's BL, those can be easily extracted from the Track Profile files in a custom export and even converted into velocity fps.
Other users have commissioned such an export in an effort to create their own pars and speed figures.

Contact me via email after the New Year if you still want to go after this and we'll work on a special project for you. Write down the specific items you want extracted in a list.

[dehere]

KM, Thanks for posting back. I appreciate what you are saying and realize that it doesn't make sense to go through all of this unless there is a groundswell of support so I'll let it drop for now and deal with it later if need be. There is certainly enough for me to work on without dealing with that issue at this point.

I think I need to make one point of clarification though. I was not asking for internal fractions for each horse to be provided. All that I was looking for was the overall race fractions (identical to that which shows up now on the text charts). With the three or four internal fractions and the final time I can develop my own internal fractions for each horse based on data which is already included in HX4.

Anyway, on to better things. I'm having a ball running various tests with HX4 now that I've finally gotten the feel for things. I do have a couple of questions though about Access and will post them elsewhere in the group. Thanks again.