Access question

[raybo]

When you import an Excel file into Access, does it just import the cell values, or does it import the underlying formulas? Do I need to do a copy/paste special-values before importing?

I have Excel 2007 now and can put a complete Bris data file (.mcp) on one row. I'm thinking about loading all my .mcp files and the corresponding .xrd results files into Excel 2007 in this manner, then draw each category of data into : Race, Horse, Trainer, Jockey, Workouts, (a post handicapped sheet with my own numbers and selections), Results sheets, etc. via formulas referencing the data in the main sheet that has all the cards and results files in it. After that I want to import all these sheets into like tables in Access.

I'll use Excel macros to cycle through each card and each race and probably an Access macro to grab each race from Excel.

Is this feasible?

[Donnie]

to your first question = just the computed data, not formulas.
to your second question = depending on the number of fields, sounds like what you are doing can be done in Access completely. If there are too many fields in the excel table, you can always do the import into Access using two different import specifications files sending the data to 2 tables. Then you can bring all the data you wish to see into one query, including your calulated fields. And this can ALL be automated thru macros in Access as well.

[raybo]

Originally Posted by Donnie@HTR

to your first question = just the computed data, not formulas.
to your second question = depending on the number of fields, sounds like what you are doing can be done in Access completely. If there are too many fields in the excel table, you can always do the import into Access using two different import specifications files sending the data to 2 tables. Then you can bring all the data you wish to see into one query, including your calulated fields. And this can ALL be automated thru macros in Access as well.

Thanks for your reply, Donnie. I'm not a database guy, yet, and would find it very difficult to do "import specifications" (don't even know what that means at the moment). I thought I'd start with Excel since I've been using it for horse racing for many years and then ease into queries in Access as I become more skilled.

[Tom]

raybo, I'm far from an Access expert, and the world's worst user can still see me ahead of him but, when you set an import, you can save a set of criteria as a spec that will exclude and include fields of your choosing. I do this on several comma files I import, based on what I want the table used for.
Set it up once, then every time you import, just select that template.

[Jeff P]

There are 1435 fields in most Bris data files...

254 in an XRD Results file (but only 122 of those are currently in use. The rest are listed as reserved for future use.)

While Excel 2007 allows you to handle that many fields in a single record, Access does not. A standard Access table alllows 255 fields.

Two strategies come to mind:

1. Create multiple tables. This will allow you to store all of the fields. Then, join multiple tables whenever you write a query.

2. Decide ahead of time which fields are important to you and grab just those.

Number crunching in Access is done in a slighly different manner than Excel. Access supports VBA code... VBA being an acronym for Visual BASIC for Applications (Microsoft.)

Using VBA, once you understand what you are doing... if you can envision it - you can create it. When it comes to number crunching the kind of info read from data and results files, there isn't anything you can't do using VBA in Access. It's almost identical to Microsoft Visual Basic.


-jp

.

[raybo]

Ok, if I want to import the comma-delimited file (.mcp file) do I need to rename it and use .csv instead? I see no way of importing the file as is.

[podonne]

Without playing the "don't use access" card, My entries table only has 116 columns, and my pp table only has 104, so technically you should be fine once the data is loaded into access. It's the initial load of the CSV program file that is the difficulty. My staging table that i read the csv file into has 1461 columns.

Depending on how you reference the cell in Excel's VBA you'll get the formula or the value (.FormulaR1C1, etc...)

BTW, VBA might be like the older visual basics, 6 or before, but the later versions are far superior.

[plainolebill]

.txt

[TampaJim]

Instead of a 'staging table' I just use an array to load the enitre card into memory then suck the data out of the array into my various tables in the database.

a la...

Public Const Max_Races_on_Card = 40
Public Const Max_Horses_in_Race = 25
Public Const Max_Input_Size = 10000

with a later redimensioning of the CSV array...

ReDim RB(Max_Races_on_Card, Max_Horses_in_Race, FieldCount)

Then its simple to load it up with nested For..Next loops.

Once the memory array is loaded for the card, you can create new DB table entries for RaceData, HorseDataForRace, and BackRaceHorseData. If you calculate things you need from BackRaceHorseData (i.e. Avg-Speed-Last-3) and include it as a field in HorseDataForRace, you don't need to keep your BackRaceHorseData in the table, reducing the DB size substantially.

When you're done with a card, you redim the array to (1,1,1) then back to max and import the next file.

Adjust your 'FieldCount' based on the type of file you're importing then do something like..

	 
 StartFile:
 
   fnum = FreeFile
   Open fnam For Input As #fnum
   
   RaceNum = 1:  HorseNum = 1: FieldNum = 1
   
   Do While Not EOF(fnum)
 	 
 	 ' Assume that we are starting with a valid file
 	 ' Process until one of [Date,Track,Race] is different
 	 
 	 
 FirstRead:
 	 Input #fnum, currtrack
 	 Input #fnum, currdate
 	 Input #fnum, currrace
 	 FieldNum = 4
   
 	 lasttrack = currtrack: lastdate = currdate:  lastrace = currrace
 
 StartRace:
 	 Select Case Right$(fnam, 4)
 	  
 	  Case ".PCS", ".MCP"
 		FieldCount = 1435
 	  Case ".XRD"
 		FieldCount = 127
 	  Case ".ETD"
 		FieldCount = 69
 	  Case Else
 		FieldCount = 4
 	 End Select
  ReDim RB(Max_Races_on_Card, Max_Horses_in_Race, FieldCount)
 	 
 	 
 	 For HorseNum = 1 To Max_Horses_in_Race
 	   RB(RaceNum, HorseNum, 1) = currtrack
 	   RB(RaceNum, HorseNum, 2) = currdate
 	   RB(RaceNum, HorseNum, 3) = currrace
 	   For FieldNum = 4 To FieldCount
 		 Input #fnum, RB(RaceNum, HorseNum, FieldNum)
 	   Next FieldNum
 	 
 	   Input #fnum, currtrack
 	   Input #fnum, currdate
 	   Input #fnum, currrace
 	   If currtrack <> lasttrack Then GoTo NextRace:
 	   If currdate <> lastdate Then GoTo NextRace:
 	   If currrace <> lastrace Then GoTo NextRace:
 	 Next HorseNum
 	 
 NextRace:
 ' We should have gotten here because we finished loading a race
 	 ' Populate Tables as appropriate
 	 Select Case Right$(fnam, 4)
 	  
 	   Case ".PCS", ".MCP"
 	   
 	   Call UpdateRaceInfo(frm, RI, RB, RaceNum)
 	   ' Process all 10 available Back Races for Each Horse
 	   For XX = 1 To HorseNum
 		If Not IsNull(RB(RaceNum, HorseNum, 1)) And _
 					  RB(RaceNum, HorseNum, 1) <> "" Then
 		  Call UpdateBackRaceInfo(frm, BRI, RB, RaceNum, XX)
 		  Call UpdateHorseInfo(frm, HI, RB, RaceNum, XX)
 		Else
 		  XX = HorseNum + 1 ' escape out
 		  Exit For
 		End If
 	   Next XX
 	   
 	 
 	  Case ".XRD"
 
   ..
   ..

[raybo]

I appreciate all of your suggestions/points of view, but, I'm afraid I'm lost. This is the reason I've not created a racing database before now.

This database will not be used for handicapping, only for research purposes. I do need to include my own numbers, however. All my handicapping is, and will continue to be, done in Excel.

I've read, several times, "Designing Relational Database Systems" from Microsoft Press (1999), and "SQL A Beginner's Guide" by Forrest Houlette, Ph.D. (2001). The SQL book was written for use in SQL Server, not Access.

Both of these books were and are way over my head and they were written primarily for programmers , administrators, etc, with business/institutions in mind. Trying to adapt the teachings for what I am trying to do just gets me going in circles and accomplishing nothing.

I've begun and quit designing databases many times, as I have become frustrated/pissed that I can't grasp the theory well enough to move to the next step and actually create a database.

This is the reason I decided to create the tables in Excel and then import them into Access as separate tables. I assume I can create my own numbers in Excel, in the proper table, and then import them along with the other data from the Bris file.

I have already created an Access table named "Card" which contains all of today's racecard data. This table contains 250 fields. The first problem I ran into with the table is the creation of a race ID so each race can be linked to the other tables. Should I create the "RaceID" in Excel before importing to Access or should it be done in Access after importing? I'm assuming "RaceID" would include track,date,race# ie: HOU010420041, HOU010420042, etc..

[raybo]

I'll try to attach a screenshot of the "Card" table I have in Access.

[sjk]

I don't understand the necessity of having a race identifier since you can link on the three fields track, date and race number with what you have.

[Jeff P]

Ray,

You wrote:

This database will not be used for handicapping, only for research purposes. I do need to include my own numbers, however. All my handicapping is, and will continue to be, done in Excel.

and:

I've begun and quit designing databases many times, as I have become frustrated/pissed that I can't grasp the theory well enough to move to the next step and actually create a database.

I can tell you from experience that creating a racing database is a journey... one that can provide rewards... but a journey nonetheless. And that journey was (in my case) far far longer and more involved than I ever would have imagined when I first started out.

By the time you are done - in truth you are never actually done - you will always discover new things that you want to add and test... You don't have to be a programming mastermind to do something like this. But you might become one simply from the journey itself. So instead of "By the time you are done"... I'll say By the time you are further along you will have a thorough understanding of Access (or whatever tool you decide to use.)

While not the fastest/most efficient database product out there, Access is something widely accepted.... and it's easy to find lots of available instructional reading material. So for the non programmer I'd say Access is probably a good choice. But that's going off on a tangent. Without question Access CAN handle the task I think you are describing.

Here's my suggestion:

Instead of trying to invent the wheel all at once, bite off small chunks at first. Think baby steps.

Why not design your database (at first) to be a really simple research tool?

Maybe one table with just a handful of factors. You can always add more later.

Maybe a table structure that looks something like this:

Field Name	Data Type	Description
--------------  -----------	-----------------------
Track		Text		3 character Bris Track Code (BEL=Belmont,AQU=Aqueduct, etc)
Date		Date/Time	Date of race
Race		Number/Integer	Race Number
Dist		Number/Decimal	Distance of race
Surface		Text		Surface: D,T,A
TrackCond	Text		Track Condition: FT,FM,SY,MY,GD,etc
RailPosition	Number/Integer	position in starting gate from the rail out
ClassDescriptor Text		C=claiming, M=Mdn Claiming, A=Alw, etc
RaceCond	Text		N2L, N3L, etc
ClaimPrice	Text		Claiming Price
Odds		Number/Decimal	Final Odds
FinPos		Number/Integer	Official finish position
Win		Number/Decimal	Win Mutuel
Place		Number/Decimal	Place Mutuel
Show		Number/Decimal	Show Mutuel


Ray1		Number/Decimal	Ray's first factor, numeric value
RankForRay1	Number/Integer	Ray's first factor, rank... top horse in field rank=1, 2nd best horse rank=2, etc.
GapForRay2	Number/Decimal  Ray's first factor, gap...  diff in value from top horse in field for this factor

Ray2		Number/Decimal	Ray's 2nd factor, numeric value
RankForRay2	Number/Integer	Ray's 2nd factor, rank... top horse in field rank=1, 2nd best horse rank=2, etc.
GapForRay2	Number/Decimal  Ray's 2nd factor, gap...  diff in value from top horse in field for this factor

From a workload standpoint, you'd import Bris card files into Excel where all number crunching is done... just like you do now.

After number crunching then an export to an Access table gives you a record of what happened for each horse in that Bris card file.

Accumulate a significant number of records in the Access table and get to querying.

For example, if the only thing you ever created was the above table... but you were meticulous about using it to capture data from every race card that you've ever played...

You would have a complete set of records that you could query - which could be used to tell you how best to exploit factors Ray1 and Ray2 - provided of course they are exploitable in the first place.

Hope I've helped...


-jp

.

[Tom]

Here is a suggestion....do your work in Excel where you are familiar, then import the spreadsheets into Access, including your calculated numbers. This will allow you to avoid duplicate records, and new tables as you go along and link them, query the data......in time, as you use Access, you will get comfortable with it and learn new tools. I have a db with tables from CJ figs, HTR, and BRIS, plus Beyer numbers - once linked,there is a lot of information available.

[raybo]

Originally Posted by Jeff P

There are 1435 fields in most Bris data files...

254 in an XRD Results file (but only 122 of those are currently in use. The rest are listed as reserved for future use.)

While Excel 2007 allows you to handle that many fields in a single record, Access does not. A standard Access table alllows 255 fields.

Two strategies come to mind:

1. Create multiple tables. This will allow you to store all of the fields. Then, join multiple tables whenever you write a query.

2. Decide ahead of time which fields are important to you and grab just those.

Number crunching in Access is done in a slighly different manner than Excel. Access supports VBA code... VBA being an acronym for Visual BASIC for Applications (Microsoft.)

Using VBA, once you understand what you are doing... if you can envision it - you can create it. When it comes to number crunching the kind of info read from data and results files, there isn't anything you can't do using VBA in Access. It's almost identical to Microsoft Visual Basic.


-jp

.

Ok, I've decided to create several tables in Excel, separating the data as needed and including my calculated numbers, rather than just creating one or 2 tables by including only some of the data from the Bris file. I decided to do it this way because I won't know which data I may want available in the future, so I want all data to already be in the DB from the get-go.