How to query, how to Import outside of HTR.

[Carl]

Now that I have "mastered" horse handicapping, hehe, I am planning on "spreading the wealth" and lose some on my money sports betting.

Unfortunately, I don't know how to import and query that good.

1) The types of downloads I am dealing with are attached. I can't in some instances get Access to break line properly, sometimes I can.


Mike or anyone that wants to help, please tell me how I can get a field like

"W 93-101" to break up into three fields?

I can break out the "W" by using a slight modification of the Pgno query, but how do I do the "93" and "101" as separate fields?

Thanks guys if you know PLEASE post. If I ever figure out what I am doing, I'll share.....

[Donnie]

Carl, in your query use this (it is a TRIM function):

Left([FieldName],1)

This return the first character from the left side and drop it into a new column. This will return the first 4 characters:

Left([FieldName],4)

So in your example, the first function will bring back "W", the second function will bring back "W 93" asuming there is a space in between the "w" and the "9".

[MikeDee]

Carl there was no attachment to your post. Can you attach a sample?

Are the values you are importing always fixed in the from W xx-yy or do they vary say W x-y and or W xxx-yyy?

[Carl]

Donnie,

Thanks. Never heard of "trim", but sounds like it might be just what I need. Will try and post back how it goes.
==============================

Mike,

The file I tried to attach was an "invalid one", don't know what it was called, but it makes a paper with the corner folded and a "e" on it. On a copy it looks like the below. I can get some of the breaks right on access, I used Frontpage when I copied of the web, and I hope Donnie's "trim" will do the rest.
===============================
4/21/1996 Home New York L 111-122 L 3 O 203
4/19/1996 Away New Jersey W 112-106 W 2 O 206.5
4/17/1996 Away Washington W 121-106 W -8 O 215
4/14/1996 Away Detroit L 96-105 W 10 O 196
4/12/1996 Home Toronto W 136-108 W -9 O 212
4/10/1996 Home Washington L 108-122 L 3.5 O 207
4/9/1996 Away New York L 93-97 W 8 P 190
4/7/1996 Home Detroit W 98-97 W 4.5 P 195
4/4/1996 Away Orlando W 100-98 N N
4/2/1996 Away Atlanta L 89-109 L 10 U 199
3/31/1996 Home Atlanta L 92-93 W 5.5 U 202.5
3/29/1996 Away Philadelphia W 101-95 W -2 U 214
3/27/1996 Away Minnesota W 121-116 W 5.5 O 207
3/26/1996 Away Indiana L 96-103 W 11.5 U 203
3/24/1996 Home Miami L 95-111 L 5.5 U 207.5
3/22/1996 Home Denver W 99-98 N N
3/20/1996 Home Orlando L 90-112 L 6 U 208
3/17/1996 Home New Jersey W 107-106 L -3.5 O 199
3/14/1996 Away Cleveland L 73-98 L 6.5 U 185
3/13/1996 Home Philadelphia W 110-100 W -8 U 213
3/10/1996 Away Milwaukee W 110-103 W 4.5 O 204
3/8/1996 Home Cleveland L 91-96 L -2.5 P 187
3/6/1996 Home LA Clippers W 110-97 W -5.5 U 213
3/4/1996 Home Milwaukee W 105-98 W 10 U 209
3/2/1996 Away Chicago L 75-107 L 15.5 U 207
3/1/1996 Home Seattle L 96-106 L 7 U 211
2/28/1996 Home Charlotte W 121-116 W -3 O 215.5
2/26/1996 Home Indiana L 119-122 W 6 O 203.5
2/25/1996 Away New Jersey L 105-111 L 4 O 198
2/21/1996 Away Phoenix L 107-120 L 10 O 214
2/20/1996 Away Utah L 98-112 L 11.5 U 211
2/18/1996 Away Denver L 93-117 N N
2/16/1996 Away Sacramento W 130-102 W 6.5 O 207
2/14/1996 Away Golden State L 103-106 W 7 U 217
2/13/1996 Away LA Clippers W 108-94 W 1 U 212
2/7/1996 Home San Antonio L 89-99 L 5.5 U 212
2/6/1996 Away Cleveland L 73-91 L 7.5 U 198.5
2/3/1996 Away Miami W 100-99 W 10.5 U 203
2/2/1996 Away Indiana L 108-116 W 12 O 207.5
1/31/1996 Home Vancouver W 131-98 W -7.5 O 200
1/30/1996 Away Orlando L 99-104 W 12.5 U 214
1/26/1996 Home Indiana L 90-107 L 4.5 U 207
1/24/1996 Home LA Lakers L 107-124 L 2 O 212
1/21/1996 Away Toronto L 95-97 L 1.5 U 211.5
1/19/1996 Away Dallas L 124-129 P 5 O 215
1/18/1996 Away Houston W 108-106 N N
1/16/1996 Away San Antonio L 97-121 L 11.5 O 209
1/13/1996 Away Atlanta L 105-108 W 8 O 203
1/12/1996 Home New York L 92-105 L 3.5 U 199
1/10/1996 Home Sacramento W 113-104 W 1 O 212
1/9/1996 Away New York L 93-98 N N
1/7/1996 Home Dallas L 96-117 L -5.5 U 213.5
1/5/1996 Home Cleveland W 93-77 W 1 U 197
1/3/1996 Home Portland L 110-114 L -1 O 214
12/30/1995 Away Vancouver L 95-103 L -4.5 P 198
12/29/1995 Away Seattle L 85-124 L 12.5 U 213
12/27/1995 Away Portland L 109-135 L 7 O 212
12/26/1995 Away LA Lakers L 91-102 L 8 U 216
12/22/1995 Home Minnesota W 114-113 L -6 O 215
12/21/1995 Away Charlotte L 97-107 L 5.5 U 218
12/18/1995 Home Chicago L 114-123 L 8 O 214
12/15/1995 Home Toronto W 122-103 W -9 O 211
12/13/1995 Home Philadelphia W 111-100 W -8.5 O 210
12/12/1995 Away Toronto W 116-97 W -1 O 208
12/10/1995 Home Atlanta L 103-108 L -12.5 O 206
12/9/1995 Away Philadelphia W 124-98 W -1 O 206
12/6/1995 Away Miami W 105-101 W 11 O 205
12/4/1995 Home Miami W 121-120 W 5.5 O 204
12/2/1995 Away Washington L 107-135 L 9 O 214.5
12/1/1995 Home New Jersey L 123-131 L -5.5 O 194
11/29/1995 Home Detroit W 100-96 W -1.5 U 197.5
11/26/1995 Home Charlotte L 109-111 L -2 O 210
11/24/1995 Home Golden State L 94-101 L -2.5 U 211.5
11/22/1995 Away Charlotte W 129-124 W 7.5 O 207.5
11/20/1995 Home Houston L 93-98 W 7 U 205
11/18/1995 Away Milwaukee W 99-93 W 6 U 202
11/17/1995 Home Washington W 110-100 W -2.5 P 210
11/15/1995 Home Utah L 90-102 L 5 U 207
11/10/1995 Home Orlando L 94-110 L 3 U 211
11/8/1995 Home Phoenix W 113-109 W 4.5 O 210
11/4/1995 Away Chicago L 85-107 L 11 U 205
11/3/1995 Home Milwaukee L 100-101 L -3.5 U 212.5
====================================

Thanks for the posts back guys. Much appreciated.

[TruForm]

Carl
I didn't Know you liked to bet Football?? Hell man just ask me anytime and I will use the Globes/ Times sport D/b

[Carl]

Donnie,

I think a "trim function" is what I know how to do in that Mikes Pgno query does that to pick out post position. It uses "2".

What I don't how to do is get rid of the "W" after I have selected it out OR how to skip the first character, the "W" and just get say the second and third in a column.

Like in the set

"W 93-101"

I know how to make the "W" a column by itself, I don't know how to make the "93" or "101" as separate columns.

[Carl]

*******,

Thanks for the offer buddy.

Not really a problem with getting information, there is a lot of it on the net, some of it in a queriable format as Al hopes to do with your site.

I just am trying to find out how to load it into access for a download that is all. I say "comma delimited" and they ask me about my punctuation skills.......

[MikeDee]

Carl
functions like trim left right and mid are not going to work on your data because the data appears in different positions on the line for example
4/21/1996 Home New York L 111-122 L 3 O 203
4/19/1996 Away New Jersey W 112-106 W 2 O 206.5
see how the "W" is not in the same line position as the "L" this is because of the length of the names New York and New Jersey and other teams vary.

The way to approach this is to make a csv file (a comma delimited file) just like the htr file.
You can do this with Microsoft Word by using the edit find replace comamnds in word. first you need to take team names that are 2 words and make then one word with no spaces. For example Find New Jersey replace with NewJersey.

Once this is done you do a find on a space and replace with a comma. Then you have to find the - and replace with a comma. finally you find ,, and replace with a ,-. This last one is required becase some of the numbers are negative.

by doing the above the following
12/27/1995 Away Portland L 109-135 L 7 O 212
12/26/1995 Away LA Lakers L 91-102 L 8 U 216
12/22/1995 Home Minnesota W 114-113 L -6 O 215

Will look like this
12/27/1995,Away,Portland,L,109,135,L,7,O,212,
12/26/1995,Away,LALakers,L,91,102,L,8,U,216,
12/22/1995,Home,Minnesota,W,114,113,L,-6,O,215,

once you have done this save it as text and you can bring it into access like a htr file as each data item is seprated by a coma.

If you want to go this way email me and I can give you more detailed help

[Carl]

Thanks buddy.

Will digest on the overnight and take a look at it in the morning.

I never realized a comma delimited file could be "made" before. Everybody likes to knock Microsoft, but as far as I can see they can do a lot more cool things than I will ever learn how to do.

And that's cool.

I'll post back in the a.m., then maybe to e-mail.

[Carl]

Mike,

I don't know if this helps or not, but most of the data does break out into fields O.K.

Attached is a text translation of the access db I can now make, using the "trim" query to break out the "W" and "L".

I'll see how this one attaches. It is from a text export of the access db.

[MikeDee]

I see what you mean that access takes care of all the fields except one, but the basic problem still exists. In the following:

108-122
93-97
96-105
100-98

I do not know how to deal with the above with the parsing functions, you connot slice of a fixed number of characters of off either side becasue the data does not appear in the same place in eack line of data. Only 2 ways I know of to deal with this.

Fix it first by making it a csv file before you bring it in to access.

OR

write a vba module that will test the field in each data line to find how many data characters there are before and after the "-" and then parse two or three characters off of either side of the dash. based on the test results.

[hurrikane]

Carl,
did you get it in. If not, unless you are going to be doing a ton of these think it's best to use Mikes idea. Just open the file in wordpad...do a find and replace on the "-". you should also do one on the spaces. You have one after the date time...

Then import text file. You will find it's real easy.

If you are going to do a ton of these then you would probably like to go the vb module route.

Good luck.

[Carl]

Will go further into it this afternoon.

These particular files are not the ones I end up with, I just want to use them to check things like
1) Percentage of times dog of >=5 pts wins outright to see what money line I need to make a bet.
I want to varify this sort of question from more than one source, there is no "equibase" in sports betting. Most stuff is home brewed.

Amazing how little (it seems) both the books and the players know compared to horseplayers about basic stats. Or maybe "those who know will not say". Don't think so though.

Will be back later. Thanks for the help and hints.

[Glen]

Carl,

You may want to query this stuff.

http://www.kiva.net/~jsagarin/p2wjeff.htm

He is supposed to be the King of Stat?

[Carl]

Thanks for the link Glen.

Had been there before but forgot about it.

I think he is fine, and the stuff he has should be good for a access download query, maybe I could link his "strength of schedule" and power ratings into some other stuff. Will try and use it.

So many numbers, so little time....(BTW, check out my "redboard of the day" on the main thread. I posted seven horseys to impress my sports bettor buds and this was one of them. "Somedays you're a Louisville slugger baby, somedays you're the ball....")

[Carl]

Looks like I am finally getting an NBA db set up, cool.

I never knew about "replace" before this thread, that has been a big one for me, thanks.

Interestingly enough, I haven't had to use word after all. Excel has proven to be very valuable here, as it does both "replace" and is very good in allowing you to break up fields into more fields with suggested break lines, the whole bit.

If I ever find a NBA winner, I'll post it in appreciation........

[HCAP]

Carl
Been lurking on and off over the last few
months -by the way great info-interesting board.

I noticed this thread and realized I could
maybe try to work out your problem in Excel.
I have very little expertise in Access Vba-
but am pretty good in Excel Vba.
Anyway I set you up a quick Excel program to covert text to data.
Tried it on the stats you posted.
Seems to work. I may need more to debug it. Contact me

By the way guys don't underestimate Excel-
I have used it successfully to query on up to
100+fields and up to 40,000 records in under 1
minute-with an old 233 mhz 128 meg pc.
Usually what I do is export Access after doing
multiple table queries to Excel and use the
"advanced filter" option.
Can easily change parameters and graph my
historical "roi" by per% of bankroll.

Of course "history" is history and very rarely repeats !!

[Carl]

Yeah, Excel is very nice for this kind of thing HCAP. I am getting a year in for a NBA team now in ten minutes using Excel, so I am quite comfortable with that.

Before I got into this I always thought Excel was a "poorer simplier version of access", which I can now see isn't even close to true.

I "like" learning more about Excel and Access, and using stats for gambling purposes as my means of so doing. The stats I am presently using come from http://www.covers.com and I can copy/paste into excel directly, do my calculations and move into access from there.

Glad you are enjoying this board. What are you presently using for stats to cap horses?

[HCAP]

Carl

The output from Excel that I get looks
like this:

04/21/96 Home NewYork L 111 122 L 3 O 203

All in seperate columns.
Seems correct on all the data I checked.

I set up a sheet so you copy and paste your data into place,click a button
and data output is generated.
Let me know if you want it.

I am using stats from a 30,000 race database.(approx)
Generally I do track specific queries.
As I mentioned I use the "advanced filter"
database feature in Excel and have set up a "user interface" in Excel that suits me.
Much easier than Access for quickly changing queries, and much easier at least for
me in analysing the results of the query.

[Carl]

Thanks HCAP, will be glad to try it.

Somethings I can make work, some I can't, and I would like to try.

E-mail is person4762388@Yahoo.com If I can save a few steps, I am all for it.

A few of the guys here use Excel in conjunction with Access too, but I am not sure how.

I'll post back as to how I do, thanks again for the offer.

[HCAP]

Carl

Check your email

[Carl]

Thank you very much for your help.

Unfortunately, my background on both my parents sides is Finnish.

My wife got me a sweatshirt which says "You can always tell a Finn, but you can't tell them much".

And that's the way it is for me, I have to learn EVERYTHING in my own plodding style in my own plodding time.

Don't be a stranger, and post when you see something of interest to you.