More Spot Play Questions

[Cliff]

Guys,

I have just started looking at potential spot plays using Access 2000. I only have a small amount of data, but decided to see if I could find any common characteristics of the $50+ bombs that come in occasionally. Can anyone confirm/dispute the following:

1) This group of winners is hard to project based on any one or more commonalities, maybe impossible.

2) They might have a little higher average field than norm, and obviously don't rank high in any of HTR's standard rankings.

3) There might be a slight bias towards S and P run-style horses in this group.

4) This group's common factors definitely vary by track.

5) One's research time is better spent in other areas.


A couple more questions:

1) Does someone have a macro, query, or whatever you call it to determine duplicate records in a database and ultimately delete those duplicates?

2) When analyzing a certain group like $50+ winners and most (if not all) of the factors exported to Access, how do you get the table in a format that is printable? I have resorted to the ol' print, cut, and tape together method. Is there any better way?


Thanks in advance for your help!
Cliff

[Carl]

Cliff,

You have mail.

As to the "predictability of longshots", Tommy C. is obviously predicting something and using HTR/Access as a tool.

Ken has a great article on longshots posted, it is from HTR report a year or so back. In it he lists all of the HTR factors in order of their ability to pick longshots. An excellent read, I have gone over it three or four times, and I don't think a fifth or sixth reading would hurt me any.

If you can't find the longshot article, post back and I will dig it up for you.

later,
Carl

[Rick]

Cliff,
The easiest way to keep duplicate races out of your tables is to set a primary key that is unique. That takes four fields: date, track, race, and post position (or program #).

Of course you have to get rid of the duplicate files to be able to make the changes to your tables.

What I would try is creating a copy of your table without records. Make the changes and then copy the records from your old table to your new table. Delete the old table and rename your new table to your old table.

I am not sure this will work but it would be a lot easier than trying to delete the duplicate records manually. Its easy to do a query for duplicate records, in fact its one of the Access Wizards. You can click on Insert, Query, Find Duplicates Query Wizard, to do a query to find duplicates using the same fields as listed for doing a primary key.

You will have to explain what you mean by getting a table in a printable format. Why would you want to print your table?

Post any questions.

[George]

Rick, sounds like he is not familiar with Report feature?

[Cliff]

Carl,

I found the longshot article. It is in October, 2000. It reports a 21%-23% identification of longshots via a 1 or 2 ranking in FR1. I am showing a 20.9% in the $50 + payout range, so, apparently that statistic holds up pretty well.

Rick,

I will try your elimination of duplicates method when I get a little more knowledgeable about Access. I think it's a little over my head right now. The printing problem I referred to was my query of nWIN$>50 with virtually every factor that is exported to Access. Ended up with 110 rows and 45 columns. Too wide to fit on regular paper, so, I just printed out a jillion 8 1/2 by 11 pages and cut them and taped them together. Query table is now approx. 3 feet by 11 inches!

George,

No, not familiar with the Report feature. But I looked and it's a couple of chapters away in my "Access for Dummies" book. Will get there soon!

Thanks again.

[George]

Cliff, there are many ways to find the "bombs" within HTR. In any of them don't expect to hit much better than 15-20 percent with 15% more of a "norm" for the really long price stuff. That means limiting wagers to a very small percent of bankroll. Long bad streaks are certain.

A very easy approach to hit some medium price ($20-$40) plays is the nESD field in IMPACT. Play nESD >= 9 AND (rTRN = 1 OR rTRN > 4). This play will hit 26% with +30% roi week after week.

If you need help on eliminating your duplicates e-mail me with your phone number and will see if we can talk you through it. Not that tough!

[Carl]

George,

Thanks for the post and the nice two factor play.

But, as with any play, you might want to be sure you check out nESD >=9 and rTRN 1 or rTRN >4 on your home track before playing.

I ran it through a 38,000 race all tracks db I made up to check my spot plays in combination. It covers all races all tracks from July 1, 2000 to March 31st, 2001. Here are the results I got:

ALL_MSA Misc Query ROI Report
All tracks July 2000 to March 2001
nESPD >=9 and rTRN 1.

Total Bets 1770
Total Amount Bet 3540.00
Wins 577
Pct. 32.60%
Amount Won 3306.40
Profit/Loss -233.60
Pct Profit Loss -6.60%
Avg. Payout 5.73

and

ALL_MSA Misc Query ROI Report
All tracks July 2000 to March 2001
nESPD >=9 and rTRN >4

Total Bets 3945
Total Amount Bet 7890.00
Wins 661
Pct. 16.76%
Amount Won 8107.70
Profit/Loss 217.70
Pct Profit Loss 2.76%
Avg. Payout 12.27

Both sets of results are IMO very strong for a two factor play. And like you reported in an earlier post, the nESD does seem a little better at a route, and with only two factors probably better on the dirt.

nESPD is turning out to be quite the little number, that's for sure. Congrats again to Ken for developing and sharing same.

later,
Carl

[George]

Thanks Carl! Was wondering how that would hold on a BIG db. Agree it's not that bad. A little tweak might make a great play out of it. Maybe route or track specific?

If you have time would appreciate a run of that play on rTRN = 11 and another on rTRN = 12. Had a little statistical abberration on those and curious if large amount of data will straighten it out.

[MikeDee]

Cliff,

Here is the easiest way I know to eliminate any duplicates in a table.

1. Make a copy of your table by highlighting it and doing a file copy, access will name it "copy of your table" or you can name it any thing you like.

2. next you want to delete all of the records in the copy of your table. There are 2 ways to do this.
2A open the table in the normal manner go the the very small square the one in the left corner above the row number and to the left of the first column (first field name) and click on it. This will highlight all of the records, now just hit the delete key and all of the records will be removed. (this may take a while depending on the size of your db, if db is small this is the easiest way).

2BThe other way is to make a new query using the copy of your table in the design grid. Put any field down in the first design column and change the query type to a delete query and run this this query by clicking on the "!". This will delete all of the records in your copy. (this is the fastest way to delete date if you have a big db).

3. now go to the copy of your table and open it in the design mode. You need to set 4 fields to key fields. You need to highlight date, track, race no and ppo number. You can do this by holding down the crtl key and clicking on each of these fields one at a time. All four fields will now be highlighted.

now go up above and click on the key icon and save your table. You have now created a unique key for your table and you cannot have any duplications in it because Access will look at the four key fields together and will not permit the combination to be duplicated as you add records.

you are now ready to enter your records into the table with the key fields set . This is done by running a append query on your original table and appending all of the records in the original table(with duplications) to table with the key fields that you have set.

4. To do this create a new query. Put your original table in the design area for the table. Now put all of the fields in the table down in the design columns. Go to the field type icon and select the append query. Access will ask you for the table to append to using the pull down button select the copy of your table that has no records in it but has the 4 key fields set.

When you close this pop up window you will see two rows of field names in the query. Access is telling you the field names for the table in the design window that it will append to the table that you named in the pop up window.

Now run this append query by clicking on the "!" It will append all of your records in your original table to the new table. When access encounters the duplicate records it will tell you how many duplicates it found and will ask you if you want to continue, tell it Yes. When it completes the append you now have a table with no duplicates and key fields set to prevent any future duplicates from ever getting in your table.

Visually inspect your table to make sure it has all the data you expected.

5. A final step is to replace your old table with your new one. To do this close your new table and with its name highlighted change the name of this table to the name of the original table you know the one with duplicates in it. Access will tell you a table already has this name and do you want to replace it, tell access yes.

mission complete you now have your table with no duplicates.

P. S. This is a lot easier to do then it is to write about

[Carl]

George,

Two additional queries as requested. The numbers are small even over 38,000 races, doubt if I am "statistically significant", but here you go buddy:

ALL_MSA Misc Query ROI Report
nESPD >=9, rTRN 11
Total Bets 69
Total Amount Bet 138.00
Wins 7
Pct. 10.14%
Amount Won 143.00
Profit/Loss 5.00
Pct Profit Loss 3.62%
Avg. Payout 20.43

and

ALL_MSA Misc Query ROI Report
nESPD >=9, rTRN 12.
01-May-01
Total Bets 19
Total Amount Bet 38.00
Wins 0
Pct. 0.00%
Amount Won 0.00
Profit/Loss -38.00
Pct Profit Loss -100.00%
Avg. Payout

later,
Carl

[Rick]

Cliff,
An even quicker way to create a new table without records is to do a right click copy on your tables name, then to a paste. A box will pop up asking for the new table name. Name it something like Copy of Table or Table2. The box also offers three options; click on Structure Only and click on OK. This will create a new table without any records.

Now follow the rest of MikeDee's instructions.

[MikeDee]

Hey Rick, nice tip on the new table creation....you can learn something new on Access every day....thanks

[George]

Mike, great job on the writeup. Am amazed how difficult it can be to lead someone through a process over the phone, let alone try to specify it on paper. Very impressive. Will, like everyone else, print it out for future reference.

[MikeDee]

Thanks George - I'm saving all of these write ups for my new book "How to spend all of your waking hours running access and playing the ponies"

[Carl]

Thanks guys.

The way I skin the cat of getting a large table's headings copied including indexes is to
1) Go to import data (same place as getting HTR data)
2) Import the table from where ever you keep your access files (mine are in my documents).
3) As I am importing the table back to it's own db, I click on "options" at the same place you choose which table you want to import, and click on "import table definition only".

This takes about 15 seconds and works good too.

later,
Carl

[MtKen]

Carl:

Are you "Ask Jeeves" too? I always feel like I'm imposing or being presumptuous when I ask for more info on these queries but if you have time & are inclined to look.....

Could you break that query down into sprints ( with & without MDN & MC) & routes (all)?
How about actual odds from 4/1 to 8/1, 9/1 to 20/1, & 21/1 to ??

I'm curious as to how the MDN/MC sprinters hold up & how routers do above 20/1.

How about trainers 1-4 with actual odds above 9/1 & above 20/1. I can understand why the popular trainers would have a lower Roi with ALL horses but I wonder if using actual odds will level the playing field a bit.


How about track-by-track & by info off the shoe board???? Just kidding...but if you can do any of the above queries I would greatly appreciate it. It is always very interesting to see what a large db comes up with compared to mine.

I did write to HDW to see about the availability of past files beyond the month cutoff.
There are contractual problemas they are trying to resolve but maybe someday they will be able to offer them.

Thanks,

Ken

[Carl]

Hi Ken,

Yeah, that's a wish list alright. And you're right, they are all interesting enough queries and sub sets of queries.

Unfortunately Jeeves I am not, but I will run a few of them for you later. I'll do the routes and sprint breakdowns in any case, and maybe a few off-odds catagories as well.

Bettcha that's why you are hanging out here doing access. Your wanting to be your own Jeeves right? That's how I got into it anyway. I wanted to know the answers to everything.... (And here I sit, a pebble looking out at a distant shore. Think Albert of one of them brainys said that.)

later,
Carl

[Cliff]

Guys,

After eliminating all the duplicate files (thanks for all the suggestions BTW), I am attempting to do an ROI for spot plays in Access 2000. Seems to me there should be a way to do it right in the query but noticed you guys run it in the report format. Any particular reason why?

I have the ROI report from Glens2.mdb but can't get it to work. Do I need to set up all the names (Count, Grand Total Bets, Grand Total Win, etc., etc.) as fields in the table. I think I'd rather have it calculate right in the query if possible.

Mike,....know what you mean about this taking a lot of time. It is starting to affect my real job, my relationships, my sex life.....well, not that I had any of those to start with. But, you know what I mean.

Later,
Cliff

[Carl]

Cliff,

I think that lives are highly overated. I got my wife re-addicted to soaps (I tape then for her), that has bought me a little extra time, although they do excite her occasionally, which kinda takes the time back. But......."A Geek's got to do what a geek's got to do."

So when you say that the ROI query "doesn't work", what does it do? For instance, what result does it give you when you leave all the fields blank? When you put a date or a rank in one of the fields?

[Rick]

Cliff,
The reports in both Glens.mdb and Glens2.mdb should work with the MSA4MSA table. You have to make the query and save the changes for the report to work.

Glens.mdb is for Access2000. Both versions should work for you.

I have used the same query with the Impact exports by just adding the table to the query. I have also made a separate report just for he impact table. It gets a little tricky finding all the table references to change but once you change them all and rework the field to match the fields in Impact it works great.

You might have to combine several queries but there is no reason you shouldn't be able to do it all in queries. I use three queries to do the longshots report. I have another three queries put together to run a query by track.

If you have a problem you have lots of help as close as this board. Most of us like to show off and also learn new things.

[MtKen]

Carl:

Just want to say again that as a novice I GREATLY appreciate what you & the other db gurus post for public consumption. It has certainly helped my bottom line &, maybe even more importantly, expanded my own handicapping exploration. It's a rare week when a great idea about some aspect of handicapping or wagering, ect. doesn't come up on this board.

Right now I'm still groping around in EXCELL, need to make the switch over to Access. It is amazing how you get an idea & how fast the time flies when you run a couple of queries...My 11yo can have his Nintendo addiction, I'll stick to db's.

Ken

[MikeDee]

Work is highly over rated to, once you give that up you have more time for sex, golf and horses.

Carl tried your shortcut to copying the table structure it worked fine.

Rick when I tried your "right click on the table" I only get the option to cut or copy ..no paste. Am I doing someting wrong?

[Rick]

MikeDee,
Right Click on table name and click on Copy.

Move cursor to open area of white box. Right Click. Click Paste. The option box should pop up.

Looks like this option could also be used to append records to new table after the changes have been made (make primary key in this case). I haven't tried it though.

[MikeDee]

Well that's real neat Rick works fine, not an intutive move to the white area. how did you find this one?

[Rick]

I really think I just found it by trial and error.

I checked my book Access97 Bible, and it shows doing it from the menu as Edit, Copy and Edit Paste.

I use WinZip and often drag zipped files to the white area to extract them in present directory. I guess I just find trying something like that natural. The first time I tried it I was probably just trying to make a copy of the table and deleting the records manually. But then again, I might have read it some where.

[Donnie]

Sorry guys, haven't been able to follow the board as close as I would like...it's that sex thing Mike was talking about!! LOL

Here is how you can use the above trick to remove duplicates from your tables....

right click on the table name and choose COPY. Right click in the white area and choose PASTE. Give the new table a new name and make sure you choose "Structure Only". This copies over the structure of your table and not the data within. Go into the design view of your new table and assign your primary key. Save and close the new table. Go into the Query view and start a new query based on your original table. If you double click on the asterick at the top of the table box it will query all of the information with one click. Before running your query, click on the word query in the menu system at the top of your screen. Choose APPEND Query. It will ask you which table you wish to append the table to. Choose the new table structure you just saved. Now run your query. If there are duplicates in the primary key field it will paste over just the individual records with no duplicates into the new table.