Database and Excel Spreadsheets

[classhandicapper]

I have a few questions for the people that have built their own database or are using Excel spreadsheets to keep data and use them as a way of learning and doing studies.

1. What is your source of data?

2. Which database do you use?

3. If you use Excel, how many worksheets does that involve.

I would like to spend some time doing research, but it seems like such a ridiculously large project, I may be better off paying someone else to do the research for me if I can find someone wiling to do it.

I have access to free data downloads from Formulator. But each race card gives you about 7 data files with different pieces of race information. To manually download one card and then import each of the files into a spreadsheet would probably take 15 minutes a night. Then I still have to download results information, figure out how to link it all together, and then figure out how to do the inquiries that would allow me to study various factors. That's just one track!

I am not familiar with Access, but I have enough DP experience that I'd probably pick it up quickly. But even in that case, even if it makes the inquiry functions etc... a little easier to code and do, it sounds like a lot of time each night just to keep the database.

Is there an easier approach?

[cj]

I have a few questions for the people that have built their own database or are using Excel spreadsheets to keep data and use them as a way of learning and doing studies.

1. What is your source of data?

2. Which database do you use?

3. If you use Excel, how many worksheets does that involve.

I would like to spend some time doing research, but it seems like such a ridiculously large project, I may be better off paying someone else to do the research for me if I can find someone wiling to do it.

I have access to free data downloads from Formulator. But each race card gives you about 7 data files with different pieces of race information. To manually download one card and then import each of the files into a spreadsheet would probably take 15 minutes a night. Then I still have to download results information, figure out how to link it all together, and then figure out how to do the inquiries that would allow me to study various factors. That's just one track!

I am not familiar with Access, but I have enough DP experience that I'd probably pick it up quickly. But even in that case, even if it makes the inquiry functions etc... a little easier to code and do, it sounds like a lot of time each night just to keep the database.

Is there an easier approach?

First, there is an option for a single file rather than the seven files.

Second, use Access (at the least) instead of Excel. Query power is what you will need. There are free database programs like MySQL, but Access is very simple to use.

Third, you can write a simple script to merge all the exports into one, and then import the file into Access in a few seconds.

[cj]

First, there is an option for a single file rather than the seven files.

Second, use Access (at the least) instead of Excel. Query power is what you will need. There are free database programs like MySQL, but Access is very simple to use.

Third, you can write a simple script to merge all the exports into one, and then import the file into Access in a few seconds.

You don't even need a script to merge the exports, just open a command window and do it in a few seconds. I don't remember the extension of the exports, but something like this:

copy *.drf allexports.txt

[Tom]

I use the HTR exports and the HDW charts for source data, and BRIS files - I have a program that extracts a set of data. I also hand add Beyer numbers to an Excel file for each track I follow. I will export various queries to Excel to work with.

Access is my database , but I export a lot to Excel to do work in. Access is very easy to use. I picked it up pretty quickly with just the tutorials from HTR on how to do it. I use it as is - no vbasic or scripting or anything advanced.

One program I find very helpful is Simple file Joiner - freeware. I use it to join text files - maybe a 100 at a time - all the daily chart files - to make one file to use for importing.

[MJC922]

Tough questions. Trackmaster is my source for charts, not to be taken as an endorsement as I'm sure what you're already using would be fine too. CJs tip is great to join files, jot it down for yourself, I remember that trick was the only way I was able to push a massive amount into Excel power pivot without it choking.

There are VBA routines you can put together to automate importing a whole folders worth of data into an Access db. If you're willing to work through frustrations (and being a horseplayer I'm sure you are) then much of this stuff is well within capabilities. I would say for anyone even kicking around the idea most definitely make a new years resolution to learn more about Excel, Access, VBA, macros etc. It's rewarding to gain the skills even if it's not just racing you may end up researching other sports or whatever. There are good books out there, tons of tutorials online, I'm sure people on this board will help sometimes too.

For making product I use SQL Server, Access and Excel. For research, I will pull data out of the production SQL DB which at any point is live and current each day, put it into an Access db, play around with queries in there and then last of all bring subsets of that into Excel. So the final stop for research is Excel for me.

How 'many' workbooks varies, probably half-dozen or more along the way. Just to setup for research I'm constantly moving around data because workbooks get huge and bloated from formulas and I want to start fresh sometimes with just 'values'. Formulas in excel create overhead, especially lookups. Sometimes you use 20 columns of formula to get the data how you want it to be and then finally paste the 'values only' to a new workbook to release memory. If you just want to use one app then Excel can practically be used as a db these days with its million row limit since 2007.

It depends on your requirements too, if by research you mean you personally need a live db for researching tomorrows card then it's going to be a challenge to keep it current, again in that case you have the nightly importing piece. If you can use old data like from 2012-2013 and research with that to gain some useful insights then it's more practical.

[MJC922]

To add to what I've already stated, you were asking if there's an easier approach to this, and I don't know what's commercially available these days but my guess is no. It's always been a hard road (at least for me) but the desire for answers pushed me on. Having someone else do the research is unthinkable to me even though on the surface it seems possible. I think you'd find out that you'd give some person with a db a list of queries and get back results that make no sense at all to you and that requires further investigation. You really need your own db unless you're looking for really simple things. I would have to know more about what you're looking to find out to make a better call but I can understand if you'd rather not discuss it in public.

[classhandicapper]

First, there is an option for a single file rather than the seven files.



Thanks.

I forgot about that. I used to use that option in the old version of Formulator for your product, but I didn't even realize the new version had it.

The single file looked like a mess when I looked at it because each of the record types has a different layout. I'm having a tough time imagining how importing that mess into a database can be easy.

I will probably have a few questions for everyone later in the process.

One question I would already have is on PP data. Let's assume I load all the PP data for some horses that ran yesterday. Then they run a few weeks later. A lot of the information on their old races will be duplicate. I assume if you import it a second time it just dismisses it.

[Capper Al]

I use both ACCESS and EXCEL. ACCESS is the must have. I like EXCEL for reporting. Much of what can be done EXCEL reports can be done just as well in ACCESS reports.

[sjk]

I use Access and data from HDW via HSH. Years ago when I had to give up chart parsing due to the format change Dave S wrote some custom code into the then current version of HSH that helped me populate the fields of my existing database.

I have never found any limitations in Access that I could not work around. It is extremely powerful.

[MJC922]

A lot of the information on their old races will be duplicate. I assume if you import it a second time it just dismisses it.

Removing duplicates will be the least of your problems. Excel even has a dedicated button for it in the ribbon. For example save an import spec for your file, record a macro, walk through running the import spec and then remove duplicates, click stop recording on the macro. The next day repeating the process is nothing more than running the macro. You will have fun -- sometimes :)

[classhandicapper]

You will have fun -- sometimes :)

Somehow I doubt it. ;)

The alternative is a manual study. I've done that 2 ways.

1. I would create a folder for a specific race (ex. Graded Stakes, Routes, Grass, Older horses). I would include the PPs, result charts, and a handful of notes for every race in that category for a year or two. Then one day when I was really bored I'd review each race with pen and paper to see if I could decipher them.

2. Manually enter the specific data I want to study in a spreadsheet and when the sample was large enough, see what I could figure out. I just did that kind of thing for the recently concluded mule racing meets on the California Fair Circuit. That study was so enlightening I entered some of the data for the previous 3 years also.

[MJC922]

Removing duplicates will be the least of your problems. Excel even has a dedicated button for it in the ribbon. For example save an import spec for your file, record a macro, walk through running the import spec and then remove duplicates, click stop recording on the macro. The next day repeating the process is nothing more than running the macro. You will have fun -- sometimes :)

My mistake an import spec is for Access but no worries, Excel's import process can be recorded in a macro along with the duplicate purge. In Access the duplicates should never make it through the import provided you have setup a unique index on the proper field or fields.

[raybo]

I have a few questions for the people that have built their own database or are using Excel spreadsheets to keep data and use them as a way of learning and doing studies.

1. What is your source of data?

2. Which database do you use?

3. If you use Excel, how many worksheets does that involve.


1. JCapper/HDW data files
2. Excel
3. 1 workbook per track, 1 record keeping worksheet (with rankings method summary) per rankings method (11 total), with an additional record keeping sheet for variable rankings methods (customizable "what ifs"/experimentation) not already hard-wired into the auto-recording routine, 1 database worksheet, 1 summary worksheet containing all rankings methods' summaries from their own individual record keeping sheets.

[DeltaLover]

Raw Data

The main data source I am using is BRIS while I also have some data that I collect in real time by scrapping odds feeds; these data are stored in a dropbox following a well know directory structure and specific naming conventions that simplify data discovery and retrieval

VM

Instead of relying on physical boxes, I use Virtual Machines (vagrant), that are easy to build from scratch, in a matter of a few minutes

Data Bases

My primary data bases are mysql, sqlite and mongodb although I also use flat files, which in some cases are best for performance reasons. I use Open Office for research and presentation purposes, although I try to not write lower level code for it

Coding

In most of the cases I use python for coding. Depending on the needs there are some cases where I also use languages like C++, lisp and octave. All the source files are kept in version control (github)

[DJofSD]

DeltaLover, you mentioned Mongo. I just learned about it but not used it. When I look at the capabilities of the database, I don't immediately see where I could use it. What are you doing with it, what do you stuff into it that you can not use a traditional RDBMS to do?

[Tom]

Class, visit Raybo's site and look around - might be worthwhile to you.

* * *
Mongo crunch numbers!

[DeltaLover]

DeltaLover, you mentioned Mongo. I just learned about it but not used it. When I look at the capabilities of the database, I don't immediately see where I could use it. What are you doing with it, what do you stuff into it that you can not use a traditional RDBMS to do?

Mongo belongs in a family of database technologies called NoSQL, that differs from the classical relational databases in the sense that it does not use the tabular representation and foreign key relation found on them. In mongo every 'record' is self-suffiecient and can be visualized as a tree of key – value pairs that do not need to comply in a predefined schema but can instead contain any kind of fields avoiding the homogeneous representation is a requirement in RDBM systems.

An example might help to understand the differences a little better..

Let's say that you have a European Shipper and need to stored his past performances in the database.

Note that an European pp differs from a North American since it is missing some data (like fractional times, beyer speed figures, and track variant for example) , while it might have some other Euro Specific data (like Racing Post Rating for example).

If you employ a RDBM then you will have something like a table called Starter where you store information like horse name, jockey, trainer etc and another table called past performances where you store things like track code, date, first fraction position and time etc... As can you see now, for the Euro past performances, you either are going to use some dummy value for the missing values and even need to create some new 'columns' for the missing data or you might even create another table called Euro Past Peformance and link to it by a foreign key... Using mongo, you do not have any of these problems , since there is no schema that you need to comply and you just save the past performance exactly as it provided to you..

An additional benefit of mongo, has to do with the fact that you do not need to create modeling classes, which will have to be populated from the databased using some middle layer coding serving as class factory. Based in the organization of the data in key – value pairs, there exist generic libraries (depening on the language of choice) that provide you an automatically created object exposing all the available properties.. This feature makes it possible to treat the data base data structures as first class objects of the language you are using.

If interested you can find tons of related free documentation on the Web and you can of course download and start experimenting with mongo in a matter of seconds by downloading from: http://www.mongodb.org/

[DJofSD]

Sorry, I guess I did not ask the question properly. Yes I am aware of the SQL v. NoSQL.

What I wanted to know is how you are using Mongo -- what type of data are you keeping in it and how does it fit into your handicapping process?

[DeltaLover]

Sorry, I guess I did not ask the question properly. Yes I am aware of the SQL v. NoSQL.

What I wanted to know is how you are using Mongo -- what type of data are you keeping in it and how does it fit into your handicapping process?

One of the main things I am using mongo for, it to store entire DRF cards as they come from BRIS, something the makes it very simple to iterate through them and do back testing for handicapping factors, ratings etc. As I explained in the previous post, the nature of the past performances is such that they no fit well in a classical relational model and it would had been a lot of work to design both the model and the import - retrieval processes.

[DJofSD]

OK, thanks. So you treat the entire data set as a BLOB and then stuff it into Mongo for quick/easy retrieval -- close?

[DeltaLover]

OK, thanks. So you treat the entire data set as a BLOB and then stuff it into Mongo for quick/easy retrieval -- close?

I have a process that reads the drf, assigning the field names for each row and field and creating a dictionary containing other nested dictionaries which are then stored in mongo creating a json like representation of the whole card

[DJofSD]

Interesting.

My immediate take away is using the dictionary of dictionaries along with the Mongo database allows you a way to represent many-to-many relationships or at least makes it easier to handle such relationships than if strictly using a traditional RDBMS.

[classhandicapper]

Class, visit Raybo's site and look around - might be worthwhile to you.

* * *
Mongo crunch numbers!


Thanks. I intend to.

I'm on vacation for a week starting this morning. I'm planning to install Access and pick up one of the "dummy" books today. I want to be in a position to do something decent for next year, even if it's just loading the database.

[MJC922]

Thanks. I intend to.

I'm on vacation for a week starting this morning. I'm planning to install Access and pick up one of the "dummy" books today. I want to be in a position to do something decent for next year, even if it's just loading the database.

:ThmbUp: Enjoy your vacation. If you get stuck on anything Access related (excellent choice btw) don't hesitate to ask. I don't know it inside and out, like reports and what not, but I have a rather good amount of experience to draw from.

[sjk]

:ThmbUp: Enjoy your vacation. If you get stuck on anything Access related (excellent choice btw) don't hesitate to ask. I don't know it inside and out, like reports and what not, but I have a rather good amount of experience to draw from.

ditto

[raybo]

Thanks. I intend to.

I'm on vacation for a week starting this morning. I'm planning to install Access and pick up one of the "dummy" books today. I want to be in a position to do something decent for next year, even if it's just loading the database.

Good luck with Access, I tried several times to create a racing database in it, without accomplishing anything, couldn't even get the tables set up.

[classhandicapper]

Good luck with Access, I tried several times to create a racing database in it, without accomplishing anything, couldn't even get the tables set up.


Yikes.

[DJofSD]

Good luck with Access, I tried several times to create a racing database in it, without accomplishing anything, couldn't even get the tables set up.
Try using Open Office Base. It has a nice UI with wizards that will allow you to create and use tables and databases without too much effort.

It's a good sand box and should allow you to master some aspects of RDBMS topics. It might help you get over the hump to retry using Access.

[DeltaLover]

I really do not think there is anything special or complicated about using Access, Mysql, SqlServer or any other similar technology, to store horse racing related data, in contrary I find the whole process as very straight forward and simple... Again, there is no reason tom use the antiquated Access since there exist more powerful, standardised and free of charge alternative like mysql, mongodb and sqlite, just to name a few..

[Tom]

DJ....can OO open Acess DB files?

[traynor]

For non-database users, the first steps are critical. Both Access and Open Office make those steps quick and easy, via wizards. Once the initial learning process (REALLY short) gets one up and running, switching to another database (at some later date) takes little more than clicking a few buttons. Both Access and Open Office are intended for new users. Both are simple, easy to use, and near perfect for learning the basics.

I would lean heavily toward Access, because as one becomes more familiar with the processes, it is extremely simple to write short VBA code blocks to make Access do whatever one wants to do.

It is well to bear in mind that the value of databases is in querying the data, not just holding it in one place. Access makes writing those queries both quick and easy. It is a perfect example of the Microsoft philosophy of writing user-friendly apps that make it easy for new users to get started, and to enable them to do fairly complex stuff in a relatively short period of time.

[DJofSD]

DJ....can OO open Acess DB files?
It appears it can -- however, I do not have Access (any more) so I can not test it to confirm.

See: https://www.openoffice.org/FAQs/ms-access/ms-access.html

[DJofSD]

For non-database users, the first steps are critical. Both Access and Open Office make those steps quick and easy, via wizards. Once the initial learning process (REALLY short) gets one up and running, switching to another database (at some later date) takes little more than clicking a few buttons. Both Access and Open Office are intended for new users. Both are simple, easy to use, and near perfect for learning the basics.

I would lean heavily toward Access, because as one becomes more familiar with the processes, it is extremely simple to write short VBA code blocks to make Access do whatever one wants to do.

It is well to bear in mind that the value of databases is in querying the data, not just holding it in one place. Access makes writing those queries both quick and easy. It is a perfect example of the Microsoft philosophy of writing user-friendly apps that make it easy for new users to get started, and to enable them to do fairly complex stuff in a relatively short period of time.
I can not disagree with anything you've said. However, I think it needs to be said that beyond the power gained vis-a-vie the query engine, it only will be capable of fulfilling that promise if the database and the tables making up the database are well formed. So, learning a bit about normal forms and what joins are will help get things off on the right foot. The wrong foot could very well mean your data is in a database but you can't use it for anything meaningful.

A long time ago, back when Paradox with the RDBMS I was using, the nice thing it offered was QBE, query by example. It allowed you to form queries by checking boxes (and a few other things) which shielded you from having to learn SQL. It help flatten the learning curve. I don't know if anything like QBE is around any more but it was what I thought of when I tried OO Base the first time.

[JJMartin]

In terms of flexibility and level of complexity for queries, access and other database programs are no match for excel in my opinion. That being said, it would probably prove more challenging to set up a basic database in Excel than access for most people. In my program I can write formulas and macros to do anything I can think of and processing speed is only a minor issue when looking at individual tracks. I would not recommend mixing every track and year you have and then doing a 50000 race query, it will amount to nothing useful. It is better to focus on an individual track and then be as creative as possible with the queries. Of course it may take a long time before you generate enough experience to find something of value, and you will have to start off slow and very basic but this type of thing requires a lot of work, there is no question.

[traynor]

Simply storing data is like having a stack of DRF back issues. There might be something useful there, but finding it takes more than the cardboard box they are stored in. It is in the querying process that "new and useful insights" are gained, not in the storing process.

Once the data is in Access, it is fairly simple to extract blocks of code with simple queries (all created with the help of the friendly Microsoft wizards) and run them through a secondary app like WEKA or Anaconda 3 (for data mining). Those apps will generate stuff out of the box that would take immense amounts of time to code in SQL (or JSON, or whatever else)--that also requires one be knowledgeable enough to know up front what he or she is looking for, and exactly what processes will uncover that information.

Learning to use Access or Open Office is simple. They also make it simple (by providing structured data clumps) to use apps like WEKA and Anaconda 3--either of which will crank out more statistical analyses of that data in seconds than anyone really wants or needs. All with a few button clicks.

[raybo]

In terms of flexibility and level of complexity for queries, access and other database programs are no match for excel in my opinion. That being said, it would probably prove more challenging to set up a basic database in Excel than access for most people. In my program I can write formulas and macros to do anything I can think of and processing speed is only a minor issue when looking at individual tracks. I would not recommend mixing every track and year you have and then doing a 50000 race query, it will amount to nothing useful. It is better to focus on an individual track and then be as creative as possible with the queries. Of course it may take a long time before you generate enough experience to find something of value, and you will have to start off slow and very basic but this type of thing requires a lot of work, there is no question.

I agree, Excel was/is much easier to use, for me. One flat file with no need for normalization, keys, SQL, etc., make it very easy for an intermediate Excel user to get it going. There were a few attempts on this forum to create Access racing databases, and to my knowledge none of them got off the ground. Part of that failure could be that those who really understand database creation seem to feel that giving away that knowledge, or actively participating in the creation, is taboo.

For instance, for someone who really knows database creation in Access, using Brisnet data files, and there are many here, how hard would it be to post the tables, complete, and some basic queries already included, from which others could simply modify those queries to meet their own needs? I never did understand why that has never happened. It's almost like the DB guys only want to associate with other DB guys, and if you don't already know all that stuff they don't want to talk to you, in layman's language that you can understand, without doing a lot of reading and studying about databases (which I have already done and could not understand enough to implement). :bang:

There appears to be lots of talk, and little usable substance, regarding database creation and use here. if it's so easy, then why not just post it? Or, do you want new users to have to jump through all the hoops that you went through? That's rather obtuse , in my opinion, this coming from someone who has always offered help to anyone wanting it. I guess there just aren't that many people who really want to help people.

[sjk]

I agree, Excel was/is much easier to use, for me. One flat file with no need for normalization, keys, SQL, etc., make it very easy for an intermediate Excel user to get it going. There were a few attempts on this forum to create Access racing databases, and to my knowledge none of them got off the ground. Part of that failure could be that those who really understand database creation seem to feel that giving away that knowledge, or actively participating in the creation, is taboo.


At least two of us offered to help classhandicapper should he need it.

When you say "why not just post it" you misunderstand the potential scope of an Access program.

Mine has many hundreds of objects and just to look at it would not allow the viewer any idea what is under the hood.

Plus who would post something that took thousands of hours of development particularly when it would lose its edge with copycat players.
To say nothing of the gross terms of service violation for the data within.

[raybo]

At least two of us offered to help classhandicapper should he need it.

When you say "why not just post it" you misunderstand the potential scope of an Access program.

Mine has many hundreds of objects and just to look at it would not allow the viewer any idea what is under the hood.

Plus who would post something that took thousands of hours of development particularly when it would lose its edge with copycat players.
To say nothing of the gross terms of service violation for the data within.

I guess I'm just a dummy then, for having posted complete workbooks, with all the macros and data in them, to say nothing of the thousands of hours of sweat and tears that came before them. Call the TOS cops, I don't care. :lol:

[Tom]

You would have to post the Access program, and that is not possible. It is not like a spreadsheet one can post and someone can then open. If I just posted the table and a query, you would need to know how to import them into your own program and then open and use them. If you want to try it, I will post a BRIS db, text file input, and a query for you to try out.

What I like best about Access is it is easy to take data from several sources and merge only applicable lines from each into a new output.

Once I run a query, I can work with the results in Excel by simply opening it in Excel. I do the grunt work in Excel and the storage, merging in Access.

[Exotic1]

Simply storing data is like having a stack of DRF back issues. There might be something useful there, but finding it takes more than the cardboard box they are stored in. It is in the querying process that "new and useful insights" are gained, not in the storing process.

Once the data is in Access, it is fairly simple to extract blocks of code with simple queries (all created with the help of the friendly Microsoft wizards) and run them through a secondary app like WEKA or Anaconda 3 (for data mining). Those apps will generate stuff out of the box that would take immense amounts of time to code in SQL (or JSON, or whatever else)--that also requires one be knowledgeable enough to know up front what he or she is looking for, and exactly what processes will uncover that information.

Learning to use Access or Open Office is simple. They also make it simple (by providing structured data clumps) to use apps like WEKA and Anaconda 3--either of which will crank out more statistical analyses of that data in seconds than anyone really wants or needs. All with a few button clicks.

Thanks for posting.

I'll have to look at WEKA and Anaconda 3.

[classhandicapper]

Well, I have the software installed and I'm watching tutorial videos on youtube. Shouldn't be long before I start pulling out whatever hair I have left. ;)

[traynor]

Thanks for posting.

I'll have to look at WEKA and Anaconda 3.

YouTube has a set of WEKA tutorials (basic intro) by the professor who teaches it in New Zealand. In a couple of hours you can learn more about data mining than most college students learn in a year--and have the skills to use that knowledge. I would also recommend looking into RapidMiner as an app (it is great!) but the tutorials for RapidMiner on YouTube are not too good.

[Exotic1]

YouTube has a set of WEKA tutorials (basic intro) by the professor who teaches it in New Zealand. In a couple of hours you can learn more about data mining than most college students learn in a year--and have the skills to use that knowledge. I would also recommend looking into RapidMiner as an app (it is great!) but the tutorials for RapidMiner on YouTube are not too good.
:ThmbUp:

[raybo]

Well, I have the software installed and I'm watching tutorial videos on youtube. Shouldn't be long before I start pulling out whatever hair I have left. ;)

Good luck! I hope you have more luck than I did.

I studied a book on relational databases, and another on SQL, both were about 4" thick, and neither explained things to where I could understand them. A complete waste of time.

[classhandicapper]

I downloaded the Nov 1 Breeder's Cup files out of Formulator.

A few questions came up immediately.

1. I can download all the Formulator data for that card as one file or multiple files. If I download it as one file, I will have multiple record types of different lengths that contain different fields and data. So I can't see how I can import that single file into a single database without causing a total mess. It seems like I will have to download the Formulator data as separate files and import them into separate databases. Does that seem correct?

2. The major file is a horse file. It contains a header record that describes what each field contains and then hundreds of data records. is there any way of importing that header record and making those my field names so I don't have to manually type all those field names in myself?

Other than that, the import of that single file took a few seconds.

[sjk]

I downloaded the Nov 1 Breeder's Cup files out of Formulator.



2. The major file is a horse file. It contains a header record that describes what each field contains and then hundreds of data records. is there any way of importing that header record and making those my field names so I don't have to manually type all those field names in myself?



You can select headers as field names when you import.

[classhandicapper]

You can select headers as field names when you import.

Thanks. I don't know how I missed that. I guess I wasn't looking for it.

[MJC922]

I can not disagree with anything you've said. However, I think it needs to be said that beyond the power gained vis-a-vie the query engine, it only will be capable of fulfilling that promise if the database and the tables making up the database are well formed. So, learning a bit about normal forms and what joins are will help get things off on the right foot. The wrong foot could very well mean your data is in a database but you can't use it for anything meaningful.

A long time ago, back when Paradox with the RDBMS I was using, the nice thing it offered was QBE, query by example. It allowed you to form queries by checking boxes (and a few other things) which shielded you from having to learn SQL. It help flatten the learning curve. I don't know if anything like QBE is around any more but it was what I thought of when I tried OO Base the first time.

The QBE grid is an Access feature, it may have originated elsewhere (I don't know the full history behind it) but I've seen the grid since at least Access 97. It's fantastic to put it mildly. I agree with everything Traynor said, for a beginner especially Access shines like no other and when you bring VBA into the fold Access becomes an outstanding choice even for more advanced users. The impact of VBA should not be underestimated. Where Access is a poor choice is when you're talking about the need for multiple concurrent users or larger size DBs, the limit on the Acesss db is 2GB right now.

Personal opinion here, Normalization with a db of this type is not all it's cracked up to be. A lot of the books on dbs focus heavily on proper design. It's good to know about proper design of course but it's possible do plenty without going that route.

[classhandicapper]

Here's the first issue I need to resolve.

So far I imported the Formulator BC Cards for 10/31 and 11/1 into a database that has 3 tables.

1. Running Line Table - This is the running line information for every race each has run coming into the races on those days (1 record for each race each horse has run).

2. Horse Table - This is horse specific information. It contains things like trainer win%, jockey win%, the earning box info etc... coming into the race. There is 1 record for each horse.

3. Race Info Table - General race info

At some point I might want to know information from table 1 and information from table 2 at the same time when I do a query or produce some kind of report.

The logical way to join them would be on Track, Date, Race #, Horse. For example, if I am looking at a horse's last race, but also want data from his earning record, I'd have to get it from the other table.

I could look at the Track, Date, Race #, and Horse Name, go to the other table, and get the rest of the information for that horse.

The problem is that I don't see how to accomplish that.

I looked at some tutorials on relationships etc... but I didn't see anything for joining multiple fields on 1 table to multiple fields on another. Perhaps I am way ahead of myself. Any insights?

[cj]

Here's the first issue I need to resolve.

So far I imported the Formulator BC Cards for 10/31 and 11/1 into a database that has 3 tables.

1. Running Line Table - This is the running line information for every race each has run coming into the races on those days (1 record for each race each horse has run).

2. Horse Table - This is horse specific information. It contains things like trainer win%, jockey win%, the earning box info etc... coming into the race. There is 1 record for each horse.

3. Race Info Table - General race info

At some point I might want to know information from table 1 and information from table 2 at the same time when I do a query or produce some kind of report.

The logical way to join them would be on Track, Date, Race #, Horse. For example, if I am looking at a horse's last race, but also want data from his earning record, I'd have to get it from the other table.

I could look at the Track, Date, Race #, and Horse Name, go to the other table, and get the rest of the information for that horse.

The problem is that I don't see how to accomplish that.

I looked at some tutorials on relationships etc... but I didn't see anything for joining multiple fields on 1 table to multiple fields on another. Perhaps I am way ahead of myself. Any insights?

Just add all the tables and link them in the query window. Just drag a line from the each field you want to link to the other. You can definitely do more than two. You can do the same thing in the Relationships window so they always link when you add the tables.

[DJofSD]

Is this using Access or something else?

When using a RDBMS and taking the 30,000 foot view, it is useful to think about the databases and the tables from different perspectives. Two fundamental perspectives are (1) how to define, i.e. create, the databases and the tables, and, (2) how to perform tasks using the databases and tables.

To create a database and table, commands/statements/directives are in the category of DDL, or, data definition language. These are used to create a database or table giving it a specific name and characteristics. See: http://en.wikipedia.org/wiki/Data_definition_language .

Once the database with a table exists, getting data into it, running queries and manipulating the content of the tables and database will use another set of commands/statements/directives referring to as DML, data manipulation language. See: http://en.wikipedia.org/wiki/Data_manipulation_language .

Before any one is going to be able to drill down to possibly show how you might be able to use the data, realistically, they'll need to have the DDL for the tables. Then, they might be able to give you the DML in the form of a query.

P.S. Or, some nice graphics with the tables and relationships.

[DeltaLover]

Here's the first issue I need to resolve.

So far I imported the Formulator BC Cards for 10/31 and 11/1 into a database that has 3 tables.

1. Running Line Table - This is the running line information for every race each has run coming into the races on those days (1 record for each race each horse has run).

2. Horse Table - This is horse specific information. It contains things like trainer win%, jockey win%, the earning box info etc... coming into the race. There is 1 record for each horse.

3. Race Info Table - General race info

At some point I might want to know information from table 1 and information from table 2 at the same time when I do a query or produce some kind of report.

The logical way to join them would be on Track, Date, Race #, Horse. For example, if I am looking at a horse's last race, but also want data from his earning record, I'd have to get it from the other table.

I could look at the Track, Date, Race #, and Horse Name, go to the other table, and get the rest of the information for that horse.

The problem is that I don't see how to accomplish that.

I looked at some tutorials on relationships etc... but I didn't see anything for joining multiple fields on 1 table to multiple fields on another. Perhaps I am way ahead of myself. Any insights?

I do not like the idea of storing past performances in a RDBM.

Although doable, it still presents the need for a relatively complicated and awkward to use relational model. Before you continue, think about what exactly you are trying to accomplish here.. In my case I prefer to keep the DRF files either in plain text (csv) format and parce them in real time or to store them in a NoSQL back end which is a perfect fit for it.


You can use RBDM to keep the results of the race, which represents an easier model and can be accomplished with two tables (one for the race specifc data like track, date, classification etc) and one for the starters of the race.

[classhandicapper]

Just add all the tables and link them in the query window. Just drag a line from the each field you want to link to the other. You can definitely do more than two. You can do the same thing in the Relationships window so they always link when you add the tables.


Do I have to define them as Key Fields first?

I see they are Key fields in your diagram, but I thought there could only be 1 primary key. When I created my table I believe it assigned a record number as the key. I could easily change that, but it limits me to one field.

[classhandicapper]

Do I have to define them as Key Fields first?

I see they are Key fields in your diagram, but I thought there could only be 1 primary key. When I created my table I believe it assigned a record number as the key. I could easily change that, but it limits me to one field.

I'm still curious as to how you have the multiple keys in your diagram, but I wrote my first simple query asking for info from each table and it seems to
have worked!

Thanks!

I don't know where I'm going, but I'm on my way. ;)

[cj]

Do I have to define them as Key Fields first?

I see they are Key fields in your diagram, but I thought there could only be 1 primary key. When I created my table I believe it assigned a record number as the key. I could easily change that, but it limits me to one field.

No, you can make multiple fields key fields as you see. It would work without them, but it makes sure you have no duplicate records. In table design, just hold control down and select all the fields you want as part of the key, then press the key button.

[classhandicapper]

No, you can make multiple fields key fields as you see. It would work without them, but it makes sure you have no duplicate records. In table design, just hold control down and select all the fields you want as part of the key, then press the key button.


You are the man.

I'm still clueless, but at least I am learning the product.

[raybo]

You are the man.

I'm still clueless, but at least I am learning the product.

Class, did you just import the Formulator data as a csv file without the field names, or does Formulator offer some way of doing that. When I was trying to create an Access db, I imported Bris data files into Excel first, processed the data into named columns, and then sent it to Access.

[cj]

Class, did you just import the Formulator data as a csv file without the field names, or does Formulator offer some way of doing that. When I was trying to create an Access db, I imported Bris data files into Excel first, processed the data into named columns, and then sent it to Access.

I personally find it easier just to create a table to match the file and name the fields to my liking. It also makes assigning data types and size for storage for each more efficient.

[classhandicapper]

Class, did you just import the Formulator data as a csv file without the field names, or does Formulator offer some way of doing that. When I was trying to create an Access db, I imported Bris data files into Excel first, processed the data into named columns, and then sent it to Access.

On the first pass I downloaded the files in txt format and checked the box that kept the headers in the file so I wouldn't have to define dozens of fields. The column names aren't the greatest, but it sure beats the manual alternative of looking at the record description specs and typing them all in during the learning stage. I haven't tried yet, but there's probably a way to change the names or attributes of the fields to make them more meaningful if I wanted to.

[cj]

On the first pass I downloaded the files in txt format and checked the box that kept the headers in the file so I wouldn't have to define dozens of fields. The column names aren't the greatest, but it sure beats the manual alternative of looking at the record description specs and typing them all in during the learning stage. I haven't tried yet, but there's probably a way to change the names or attributes of the fields to make them more meaningful if I wanted to.


Yep, just go in Design View, you can alter names of fields any way you like, attributes too as long as it doesn't exclude data you've already imported.

[raybo]

Yep, just go in Design View, you can alter names of fields any way you like, attributes too as long as it doesn't exclude data you've already imported.

Can you explain the bolded part?

[sjk]

You will want to take care that fields like date, track, race # in one table have the same meaning in two tables you are joining. If the fields in one table refer to the upcoming race (11/1) and the fields in the other table refer to the attributes of the past races these may not be what you want to join.

You may want to incorporate the date, track, race information for the upcoming race into the other tables so as to be able to recover all of the data through joins.

You are not stuck with the table as you import it. You can use make table and append queries to add additional fields in a new and potentially more useful table.

[MJC922]

sjk touched on one thing that I have found very important to the way I use Access. That is, I import the data from the file into a table pretty much raw, and that's what I essentially call a 'staging' table -- after that I use make table queries against that staging table to get the fields into a brand new table, maybe even into a brand new db, a table which will contain the data the way I want and need the data to be for whatever my specific plans are.

That make table query might involve dozens of calculated fields I've taken the time to set up to do all sorts of things e.g. to get the times into seconds, segments, velocity, pull fields in from other tables whatever.

A basic example, you mentioned that you have a date field a track field and a race field in the staging table, well maybe I want in addition to those a field in the new table that looks like the traditional 22Sep14 9PRX we're so accustomed to seeing. And maybe I want to join on that field later on in a query of some sort as opposed to joining on those three separate fields.

Why? keep in mind performance considerations, three separate joins create more overhead. If you can do it with one join that might make all the difference in getting a query to complete when the db gets large. Indexing can become critically important to query performance, as far as I can recall a join that is only made between tables at the query level is unable index on the join as opposed to if you have set up an actual relationship between the tables on indexed fields.

There are db architects who would scoff at storing anything that can be derived from a calculated field in the database. I pay no mind to any of that. Explore and find through experience what works for you. Please make backup copies of your db all the time too, at least daily when you're running all of these action queries, if something gets messed up you need a way to go back. Good luck. :)

[Tom]

Class, you can also copy and save as a renamed file, and check structure only.
then make changes and add keys, then re-import data and eliminate duplicates.

[cj]

Can you explain the bolded part?

Example would be trying to change a long integer field to integer when already has long integers in it, or restrict a string 10 characters when there are larger in the table already.

[raybo]

Example would be trying to change a long integer field to integer when already has long integers in it, or restrict a string 10 characters when there are larger in the table already.

Thanks CJ! Yeah, forgot about all the data types you have to get right in a DB (for performance enhancement and memory savings, if I remember right), sure am glad I don't have to do that stuff in Excel.

[classhandicapper]

That make table query might involve dozens of calculated fields I've taken

Speaking of calculated fields, suppose I wanted to write a query that looked at various combinations of Beyer Figures.

1. Last Race
2. Best of last 2
3. Best of Last 3
4. various averages
5. etc

The goal would be to scan through the database and see how a few rules, (Beyer figures rule among them), performed.

Is something like that easier to code in a query itself or would you create a new field that could automatically be updated with the appropriate values in one of the databases?

[JJMartin]

Speaking of calculated fields, suppose I wanted to write a query that looked at various combinations of Beyer Figures.

1. Last Race
2. Best of last 2
3. Best of Last 3
4. various averages
5. etc

The goal would be to scan through the database and see how a few rules, (Beyer figures rule among them), performed.

Is something like that easier to code in a query itself or would you create a new field that could automatically be updated with the appropriate values in one of the databases?

You would probably need to create an additional column that would distinguish and rank factors for each individual race but I only work with Excel so its just a guess.

[MJC922]

Tough questions like this already? Damn. There are many ways to skin a cat. Some SQL expert can probably give you much better advice than I will in this case. A relatively straightforward way though is to use Excel. I'm not averse to shuffling data back and forth between the apps for research purposes, these two apps are made to compliment one another. Your query on the Access-side is easy, using a simple select query, for this example we'll choose three fields, we'll use HorseName, DateOfRace, BeyerFig, (for clarity on my own figs it's called PerfPriorRace1 not BeyerFig, doesn't matter though) just set the sorting in the query to HorseName Ascending, DateOfRace descending. Run the query. When it completes, click the external data tab, click the Excel button, choose to keep formatting etc, blast that out to Excel.

HorseName DateOfRace PerfPriorRace1
Gather No Moss 17-Nov-12 22
Gather No Moss 20-Oct-12 23
Gather No Moss 06-Oct-12 18.75
Gather No Moss 31-Mar-12 15.75
Gather No Moss 19-Nov-11 21.25

So now I'm in Excel looking at columns A,B,C above and on my rating scale it's sheet-style i.e. the lower the better, for you it's Beyer-style so you would use MAX instead of MIN:

cell D1 is just a label type: BestOfLast2

In cell D2 use the formula: =IF($A2=$A3,MIN($C2:$C3),"")

Fill that down to the bottom

This means if the horse name is the same in cell A2 & A3 then return the Min of C2 to C3 otherwise leave it blank.

cell E1 is just a label : BestOfLast3

In cell E2 use the formula: =IF($A2=$A4,MIN($C2:$C4),"")

Fill down -- I think you get the idea from here. It's not elegant in the least, it's brute force, but who cares you're getting things done. IMO the object is to get the data into the format you want it to be in for research purposes not to win design points on the way. I'm not an expert in apps or code but I sure as hell don't give up easy. :)

Now back to Access, create a new blank Access db, click the external data tab, where it says Import and Link, click Excel. Import it back as a new table. You may have to go into table design and change some field types to numeric but I think this gets you just about where you want to be.

Surely there are many other paths to get to this point, Autonumber fields linking up to self-joins, vba functions, etc, you can even use Excel functions inside of Access vba code if the correct references are configured.

[JJMartin]

HorseName DateOfRace PerfPriorRace1
Gather No Moss 17-Nov-12 22
Gather No Moss 20-Oct-12 23
Gather No Moss 06-Oct-12 18.75
Gather No Moss 31-Mar-12 15.75
Gather No Moss 19-Nov-11 21.25

So now I'm in Excel looking at columns A,B,C above and on my rating scale it's sheet-style i.e. the lower the better, for you it's Beyer-style so you would use MAX instead of MIN:

cell D1 is just a label type: BestOfLast2

In cell D2 use the formula: =IF($A2=$A3,MIN($C2:$C3),"")

With that formula it will also output the min of the 3rd and 4th row in row 3 and so on and that is fine if that's what you want but if you just want to isolate the last 2 races,you could amend it like this:
=IF(and($A2=$A3,$A2<>$A1),MIN($C2:$C3),"")

This will only show 1 figure per horse's record.

[MJC922]

With that formula it will also output the min of the 3rd and 4th row in row 3 and so on and that is fine if that's what you want but if you just want to isolate the last 2 races,you could amend it like this:
=IF(and($A2=$A3,$A2<>$A1),MIN($C2:$C3),"")

This will only show 1 figure per horse's record.

Yes it's definitely what I personally want but for reasons left out of the example, it's also about as far as I'm going to go... examples on a forum aren't easy to provide and some things are better left to gain through experience. Thanks for providing another formula though, I'm sure there are people out there who will prefer yours for their own reasons.

[sjk]

It is pretty easy to do in Access if you break the problem into pieces. Often you will want to create a series of queries to answer a question and this is particularly true if you are dealing with last, last 2, etc.

1). You will want the data to be sorted by starter and prior race date descending and in a table with a counter. If you data is not set up this way append the data to a table that is.

2). Find the minimum counter associated with each horse to identify the last race.

3). Join that query on the horse name to the data table and set the criterion that the counter in the data table is equal to the minimum counter (for last race only), equal to that or the next greater value (for last two) etc.

4). Create a query that uses the data selected by the previous and does the required calculation of the speed figure or other data point, ie max or avg.

Bear in mind that if you nest very many queries things will bog down and the solve for this is to use make table queries to flatten the data (and if there are many steps involved a macro or code to run through a sequence of such actions).

[MJC922]

It is pretty easy to do in Access if you break the problem into pieces. Often you will want to create a series of queries to answer a question and this is particularly true if you are dealing with last, last 2, etc.

1). You will want the data to be sorted by starter and prior race date descending and in a table with a counter. If you data is not set up this way append the data to a table that is.

2). Find the minimum counter associated with each horse to identify the last race.

3). Join that query on the horse name to the data table and set the criterion that the counter in the data table is equal to the minimum counter (for last race only), equal to that or the next greater value (for last two) etc.

4). Create a query that uses the data selected by the previous and does the required calculation of the speed figure or other data point, ie max or avg.

Bear in mind that if you nest very many queries things will bog down and the solve for this is to use make table queries to flatten the data (and if there are many steps involved a macro or code to run through a sequence of such actions).

Nice work, glad you posted it. This was what I was on about in the last bit at the end with the autonumber (as a counter) and the self-join to the next highest. Lots of ways to skin cats, and frankly to me that's part of the fun, creative solutions to problems, though I can see where for others it's nothing more than frustration and throwing in the towel.

[classhandicapper]

Thanks guys. That should keep me thinking for awhile. :ThmbUp:

[classhandicapper]

This is both related and unrelated to the previous question.

1. I have a table. It just has the horse's name, date of race, race # and some general information about the horse on that specific date.

2. I'd like to physically add several fields to that table because that's where they logically belong.

3. I know I can physically create the new fields in that table.

4. If I have the data that belongs in those new fields in an Excel spreadsheet, can I somehow import that data into the appropriate fields in the table without destroying what's already there?

[sjk]

I presume you have the name in the excel spreadsheet. Import that spreadsheet into a separate table. Create a make table query using the two tables joined on the name and with all of the fields in both tables as output.

[classhandicapper]

I presume you have the name in the excel spreadsheet. Import that spreadsheet into a separate table. Create a make table query using the two tables joined on the name and with all of the fields in both tables as output.

OK. I think I understand that approach.

I think what I'm trying to avoid is creating dozens of queries joining new imported fields and additional fields I calculate with my major tables when logically all I need is 2 tables.

I need one table with all the detailed race info for every race for every horse (basically running line data) and one table with some extra general information about each horse in table 1 for each race date (earnings box data, speed figures etc..).

[sjk]

I have hundreds of queries and hundreds of tables and only three of the tables come from the native data.

The power of Access is that you can build things of enormous complexity and each piece is just bolted on to what you already have done.

[classhandicapper]

I was just looking at Charts file from DRF that contains the results of yesterday's races at SA.

Here's an interesting problem. It contains multiple race types.

The "Header" records contains the Track, Date, and other relevant info about the card.

Then there's another 10 records with some more relevant "Race" level data.

Then there are "Detail" records that contain most of the information I am interested in (finish, payoff etc...). The problem is the detail records don't have the Track and Date of the race in them. They just have the Race Number, Horse's Name etc....

Questions

1. Is there an easy way to break apart a file with multiple record types like that as part of the import instead of doing in manually? Something like "just import records with an "S" in the record type field.

2. Is there a way to automatically get information from one of the record types (like the Track and Date that's in the "header") and add those fields to the "detail" records so I can create an easy relationship between the rest of my data and the results?

I'm picturing myself having to take the results text file, delete the records I don't need, add a few new fields to all the records manually, and then importing it into my table. That would be a tough job to have to do for multiple tracks daily.

[JJMartin]

I was just looking at Charts file from DRF that contains the results of yesterday's races at SA.

Here's an interesting problem. It contains multiple race types.

The "Header" records contains the Track, Date, and other relevant info about the card.

Then there's another 10 records with some more relevant "Race" level data.

Then there are "Detail" records that contain most of the information I am interested in (finish, payoff etc...). The problem is the detail records don't have the Track and Date of the race in them. They just have the Race Number, Horse's Name etc....

Questions

1. Is there an easy way to break apart a file with multiple record types like that as part of the import instead of doing in manually? Something like "just import records with an "S" in the record type field.

2. Is there a way to automatically get information from one of the record types (like the Track and Date that's in the "header") and add those fields to the "detail" records so I can create an easy relationship between the rest of my data and the results?

I'm picturing myself having to take the results text file, delete the records I don't need, add a few new fields to all the records manually, and then importing it into my table. That would be a tough job to have to do for multiple tracks daily.
I could probably do it in excel provided the columns remain consistent on each file, then you could put it somwhere else like access

[MJC922]

I was just looking at Charts file from DRF that contains the results of yesterday's races at SA.

Here's an interesting problem. It contains multiple race types.

The "Header" records contains the Track, Date, and other relevant info about the card.

Then there's another 10 records with some more relevant "Race" level data.

Then there are "Detail" records that contain most of the information I am interested in (finish, payoff etc...). The problem is the detail records don't have the Track and Date of the race in them. They just have the Race Number, Horse's Name etc....

Questions

1. Is there an easy way to break apart a file with multiple record types like that as part of the import instead of doing in manually? Something like "just import records with an "S" in the record type field.

2. Is there a way to automatically get information from one of the record types (like the Track and Date that's in the "header") and add those fields to the "detail" records so I can create an easy relationship between the rest of my data and the results?

I'm picturing myself having to take the results text file, delete the records I don't need, add a few new fields to all the records manually, and then importing it into my table. That would be a tough job to have to do for multiple tracks daily.

Again I come back to advising the use of a staging table. Once you have it in there you can do something with it. Try not to think about filtering on the way in, get it in first, even though it's all chaotic and messy in the staging table you can work with it. For example if there's an S to identify that entire record as race data then a select query on the staging table in the QBE grid with an S added as criteria for that field brings up only those records. Switch that from a select query to an append query to push those records out to a specific table.

[classhandicapper]

Again I come back to advising the use of a staging table. Once you have it in there you can do something with it. Try not to think about filtering on the way in, get it in first, even though it's all chaotic and messy in the staging table you can work with it. For example if there's an S to identify that entire record as race data then a select query on the staging table in the QBE grid with an S added as criteria for that field brings up only those records. Switch that from a select query to an append query to push those records out to a specific table.

Now I get the staging idea! I may have to reread all the comments people have made because some of them were probably ahead of where I was at the time.

Forgive the ignorance, but what is the "QBE grid"?

What about adding 2 fields to the "S" records for Track and Date?

Can it be done in one shot?

[MJC922]

QBE grid is on the Create tab>Queries, click Query Design. Right-click on the big blank area choose Show Table. Highlight the staging table (when you have one) click the Add button.

Now down below that in the grid, is where you add fields that you want to show in the query, for now you can choose the top option in the drop-down list, you'll know it because it has a *. That'll give you all of the fields for the table. Move over to the next column in the grid, add the field which contains the letter we're trying to filter on. Underneath that you'll see a criteria row, type the letter in there. You can run the query to make sure it comes back with what you want.

Then back to View> Design View. Third column in the grid, you can experiment with creating a calculated field. For example, top of column three in the grid, don't choose anything from the drop-down, this time type a name of your choosing such as MyName: make sure the colon is after it. Then type "MJC922" and make sure the quotes are around that as in this case it's simply text. Run the query, you should have a new field called MyName. That's your calculated field. Go back into design mode, right-click where it says "MJC922" choose build, in here is where you play around to piece together your calculated field, delete the MJC922 and have fun, but keep me in your memories. :)

Also, a good site here, helped me out: http://www.fontstuff.com/access/

[classhandicapper]

QBE grid is on the Create tab>Queries, click Query Design. Right-click on the big blank area choose Show Table. Highlight the staging table (when you have one) click the Add button.

Now down below that in the grid, is where you add fields that you want to show in the query, for now you can choose the top option in the drop-down list, you'll know it because it has a *. That'll give you all of the fields for the table. Move over to the next column in the grid, add the field which contains the letter we're trying to filter on. Underneath that you'll see a criteria row, type the letter in there. You can run the query to make sure it comes back with what you want.

Then back to View> Design View. Third column in the grid, you can experiment with creating a calculated field. For example, top of column three in the grid, don't choose anything from the drop-down, this time type a name of your choosing such as MyName: make sure the colon is after it. Then type "MJC922" and make sure the quotes are around that as in this case it's simply text. Run the query, you should have a new field called MyName. That's your calculated field. Go back into design mode, right-click where it says "MJC922" choose build, in here is where you play around to piece together your calculated field, delete the MJC922 and have fun, but keep me in your memories. :)

Also, a good site here, helped me out: http://www.fontstuff.com/access/

Wow, I got it to work.

I started with a Staging table and created a query that added both the track and date to all my "S" records.

I need spend some time playing around until I see clearly how this all fits together. Maybe tomorrow when the BC is over.

Thank a lot. You've been helpful. :ThmbUp:

[classhandicapper]

MJC922,

I'm pretty much lost at this point.

I was able to create a Query using my Staging Results Table (it contains the original data with 90+ fields and multiple record types) that just selects the "S" records and displays the 2 new fields (Track and Date Race) I will ultimately need to add to a Final Results Table as you described.

When I run that query it displays The "S" records, the track, and the date.

Any effort to to use that Query to create a Final Results Table that contains just the full "S" records PLUS the 2 new fields added to them has resulted in a dead end despite reading the instructions on how to do an append and repeated attempts.

Perhaps it's because I don't have a Final Results Table created yet?

The whole process seem unnecessarily clumsy. In Excel, I could easily add a column, put the value I want in the first row of that new column, copy it to the entire column in 1 second, and be done with it. Then delete all the non S records. That's what I was trying to avoid, but it's seems way cleaner than what I am attempting now.

[sjk]

Look at your joins. Be careful that dates relate to the same thing whether upcoming or past races.

[classhandicapper]

Look at your joins. Be careful that dates relate to the same thing whether upcoming or past races.

I'm not even sure what I should even be joining when it comes to using this Query to selectively append records to an existing table. I don't see where a join comes in.

All I am trying to do is say:

1. Look at Results Staging Table (temporary table)

2. Select only the "S" records from that table

3. Add 2 new fields to those records called Track and Race Date

4. Append them to a table called Final Results

Then I will worry about how to join the Final Results table to the rest of the database that contains PPs line etc...

[classhandicapper]

I'm getting so frustrated I'm almost ready to sharpen some pencils and do it all manually.

It literally took me 1 minute to take a results text file, import it to Excel, delete the records I didn't want, and add the 2 fields I wanted with the appropriate values. It took me another minute to import into my Access Final Results Table. :jump:

Then I tried to do the same thing for the next day.

When I specify the source and destination I switched to "Append" instead of "Import to a new database" and it crapped out because it's insisting the first row of my Excel file contains some data that cannot be used for a field name even though I do not not want it to be used. I can't uncheck that parameter.

:bang: :bang: :bang:

[classhandicapper]

Then I tried to do the same thing for the next day.

When I specify the source and destination I switched to "Append" instead of "Import to a new database" and it crapped out because it's insisting the first row of my Excel file contains some data that cannot be used for a field name even though I do not not want it to be used. I can't uncheck that parameter.

:bang: :bang: :bang:

I needed header records. :rolleyes:

I'd really like this to be as automated as possible, but it's going to be race between when I figure out how to do everything and when I'm too frustrated to continue.

[raybo]

I needed header records. :rolleyes:

I'd really like this to be as automated as possible, but it's going to be race between when I figure out how to do everything and when I'm too frustrated to continue.

Well, if it makes you feel better, you have gotten much further than I have ever gotten with Access. That stuff is just way over my head, just like traditional programming languages, they are incomprehensible to me also. Heck, I can't even write a macro from scratch, and I've been working with macros, and macro code for years, it just doesn't "click".

[Tom]

Class, if you import, then you have an option of checking for a header row or not.

[classhandicapper]

Class, if you import, then you have an option of checking for a header row or not.

It had that option available for the very first import (I had no header record so I checked "no" and the field names were assigned as Field 1, Field 2.....). That went fine.

Then when I tried to import the file for the 2nd day, I chose append and it wouldn't let me uncheck that "header" option and it wouldn't process the file without a header record.

I created a header record with those field names and then it worked, but it seems silly to require it on an append if it's not required on the initial load.

[whodoyoulike]

IMO, whether you use Access or Excel before you start programming, you need to have a mental design of what you want to accomplish. Some of the things to know before you start are:

1. what do you want done;
2. what do you want the design to look like;
3. what info do you need to gather;
4. if it's a complicated program do as someone mentioned in another thread, prepare or learn how to FLOWCHART on paper;
5. what should the output look like when viewing or printed out;
6. etc., (I've included etc., because there are probably a number of other steps I haven't listed).

I'm only mentioning these because it seems you're winging it. Even if you're only trying to get familiar with the program you seem to have taken some leaps beyond beginner status (you may just be impatient).

[raybo]

IMO, whether you use Access or Excel before you start programming, you need to have a mental design of what you want to accomplish. Some of the things to know before you start are:

1. what do you want done;
2. what do you want the design to look like;
3. what info do you need to gather;
4. if it's a complicated program do as someone mentioned in another thread, prepare or learn how to FLOWCHART on paper;
5. what should the output look like when viewing or printed out;
6. etc., (I've included etc., because there are probably a number of other steps I haven't listed).

I'm only mentioning these because it seems you're winging it. Even if you're only trying to get familiar with the program you seem to have taken some leaps beyond beginner status (you may just be impatient).

I agree! I always ask myself what I want to end up with, before I start writing formulas and creating macros, and formatting and all that stuff. Once you know what the end result is to be, you know what it will take to get there.

I know Class is just getting his feet wet, but without learning the basics first he's getting ahead of himself, IMO. The reason I never got an Access db off the ground was because I couldn't get the basics down and understood first, so I never progressed to where Class is now. What he is doing can only lead to frustration, because really, you don't know what it is that you're actually doing if you skip the basics and jump in with both feet from the git-go.

I hope he succeeds, but I will not be trying it again, I've had enough of that stuff. :lol:

[classhandicapper]

I have a data processing background. I was a computer programmer for about 25 years (I hated it). I am familiar with creating and working with databases, but I know nothing about Access. So I have a mental picture of what I would like to accomplish, but I'm handcuffed by the format and content of the data I am importing and not knowing my way around the product. That's a recipe for me going nuts. ;)

[MJC922]

I have a data processing background. I was a computer programmer for about 25 years (I hated it). I am familiar with creating and working with databases, but I know nothing about Access. So I have a mental picture of what I would like to accomplish, but I'm handcuffed by the format and content of the data I am importing and not knowing my way around the product. That's a recipe for me going nuts. ;)

Hang in there. It's tough to say for sure but my sense is you're jumping ahead too fast. Let's not look at calculated fields and joins, I'm going to suggest starting fresh and get each of the files imported just sitting in their own staging tables. Then we'll think about make-table queries, joins and all of the stuff you really want to see. At this point the concern is just getting the file the way you want it to be as it sounds like it comes to you broken into several files. Once everything is in its own table then we can think about what we need to do to pull it all together into a query. The 'best of last two Beyers' and all of that other stuff you might want to see, keep it in mind of course, but it comes way later in the process.

[whodoyoulike]

Okay, the reasons for your approach is starting to make sense to me.

[MJC922]

Also I don't expect you can post the files but if you can point me to something like this it would be very helpful:

http://www.trackmaster.com/track/info/chtcomma.txt

Right now I'm flying blind with what your files look like.

[classhandicapper]

Also I don't expect you can post the files but if you can point me to something like this it would be very helpful:

http://www.trackmaster.com/track/info/chtcomma.txt

Right now I'm flying blind with what your files look like.

PPs

http://www1.drf.com/formulator/userguide.pdf


Results

http://www1.drf.com/misc/charts/drfchartfields.pdf



I can get the PPs as one giant file or broken up into the relevant parts. I am choosing to break it up and load the parts I am interested in into relevant separate tables because each is in an entirely different format with no real record identifier.

I believe I have each part of the PPs and Results loaded up into a relevant tables for the 2 Breeder's Cup days. Loading one involved some manual intervention in Excel on my part to add the Track and Date because I knew I would need those fields for Joins later on. That was the part that was a bit frustrating. I'd prefer doing automatically inside Access, but couldn't get it work.

Don't feel obligated to spend a lot of time on this, but I appreciate any direction.

[MJC922]

Ok no problem, this definitely helps, however I'm not following the need to manually add anything in Excel, I'm not sure why you've found that to be a necessary step.

If I'm reading it correctly the exported delimited files are primarily represented by:

Race File Layout
Running Line File Layout
Horse File Layout

I would be leaning toward (minimally) getting these three files into separate tables of their own for now. After that we can do a join in the query for example on fields 7, 8 & 9 of the RunningLines table to fields 1,2 & 3 of the Races table. (Or a relationship to the same) something along those lines.

Are you shooting for having the PPs like what those three files represent as the primary data in your db or the charts files, or both? I do everything with charts so I need to get my head around people who work with daily PPs to compile a db as that adds nothing in my case, the PPs are derived from the charts. The only thing I use the daily PPs files for are the entries essentially to generate product. For back-research there would be no need for today's entries, I guess I have to get a better understanding of your intended usage scenario.

I look at knowing who is running today as kind of a bolt-on final step, just about the last thing to address if at all. Depends upon the type of research you're interested in doing.

[MJC922]

Keep in mind this is your first db, you may simplify things considerably by just bringing in the running line file period and leave it at that, for research purposes an awful lot is already in there, you know the finish position, you know the Beyer figure, the odds, trainer, jockey, fractions, beaten lengths, date, track and race etc... just about everything required to do some decent research is all in that one file.

[classhandicapper]

Here's where I stand.

I have a separate table for Race Info, Horse Info, PP Lines, Trainer Info, and Jockey Info. I broke it up and imported it into these tables because that's the way I receive the data. It's also a fairly logical organization.

I joined them all in a fairly logical way. Typically, it's Track, Date, Race, but there are other joins.

I also have a Results Table.

This is the one that gave me a little trouble. There are a variety of record types in this file, all in different formats. I imported this into a staging table at first as you suggested. I noticed that I will probably only need the "S" records, but those "S" records did not have the Track and Date on them and I need that. That information is located in a Header Record of another format. I was able to create a query that just selected "S" records and added the Track and Date as per your instructions. However, everything I did to try to make those fields permanent parts of the "S" records failed.

At that point I just imported that file into Excel, deleted all the records I didn't need, added the 2 fields I needed, and imported back to my Results table.

AS far as could tell, at that point I was exactly where I wanted to be other than the process being manual. :)

Last night I created a Query that looked up each race in my Race Table, went to the Results Table, and selected off only the horses that Won. It also selected the Win, Place, and Show payoffs and Trainer Name. It then took that Trainer Name, went to the joined Trainer Table and picked up his Win Percentage as of that date. It worked beautifully. So I know that I am doing some of the relationships correctly....so far. :jump:

I think dealing with the manual process of the Results is the one stumbling block until I start getting into more complicated stuff. If it has to be manual, so be it.

[raybo]

I don't know the Access solution to that problem, but I do know, at least I'm pretty sure, that you can use VBA in Access that could probably automate the Excel work you're doing manually now, ie: open Excel, import the file, create the 2 new fields, delete what you don't want, import/export to Access, close Excel, etc..

But then, if you can do all that you should be able to create the 2 new fields in Access alone, grabbing those fields from one of the other files in Access.

[MJC922]

Are you sure you need to bring in the results in addition to the PPs? If the db has to be current (like up to the minute) then I understand why you need to feed it the daily results. However if you just want to do back-research class hierarchy, pars, whatever, there's plenty of results already in the running lines. Think of it this way, when you open up the form (even the paper version) the top line in any horse's record is the result for the prior running line. When you're bringing in massive numbers of PPs with running lines the only results you're ever 'missing' is for the top line in each horse's record.

If I have a few years of data in the db I could care less about that top line's result. In my research DBs I bring in 2 million lines which lands near the 2GB limit, this is why when I do post up some of these stats the samples are huge. I mean you can't have gaps in the data either, so I don't know what your plan is there, if you're purchasing archived files or if you've been keeping current all along.

As mentioned in a previous post I only bring in the charts, but then I'm making my own numbers. This is a clear difference because you want to do a bit of research on the Beyer figs and those I take it aren't in the charts. Again that's a notable difference -- I have to remind myself about why anyone would want to use the PP lines as a source.

[sjk]

I use charts too. The downside is that a db built from charts needs to be complete to be able to recreate pp type information to handicap races. I need to get charts from every track every day and if I am out of town it takes time to get caught up but there is no way around it.

[cj]

How do you test what works if you don't link PPs to results?

[classhandicapper]

How do you test what works if you don't link PPs to results?

Exactly.

I plan on creating information based queries, but I also plan on creating my own ratings using that data and then testing the win%, roi etc.. of those ratings to see how well they work so I can continue tweaking them.

That will be the biggest challenge from where I am now.

I'm not even sure how to write a query that looks at each horse's last 3 races (for example), takes the best of last 3 for each, and determines how the top rated horse does (let along create my own ratings based on all the data).

[DeltaLover]

I'm not even sure how to write a query that looks at each horse's last 3 races (for example), takes the best of last 3 for each, and determines how the top rated horse does (let along create my own ratings based on all the data).

The easiest way to do something like this, is either writing a Stored procedure or accessing your data from an external program.

Note that you are mentioning picking the best of the last 3, the question is what kind of an algorithm you are going to use to determine it? Going further this path, you will find yourself changing this logic over and over... This is a good example, why you might need to consider a fully OO language instead o relying on mere sql queries (even stored procedures will come short for this task)....

[classhandicapper]

Note that you are mentioning picking the best of the last 3, the question is what kind of an algorithm you are going to use to determine it?

My example from above was just an attempt to demonstrate how limited my understanding is of how to perform the database logic of something that simple, let alone what I have in mind long term.

I'm not the least bit worried about any of the handicapping aspects of this. I've been tinkering with formulas for close to 40 years. I'll just try to get as close as I can to some of what I already do subjectively. Then I will test it alone, vs. other things, in combination with other things etc... I don't think I'll ever use any formulas or ratings in live action. But I think I can answer certain questions I've had for a long time and learn some stuff that will become part of my thinking.

I clearly have a lot to learn about how to manipulate the data AFTER I am on a solid path to building the database I want. Your suggestion is probably a good one.

[DeltaLover]

My example from above was just an attempt to demonstrate how limited my understanding is of how to perform the database logic of something that simple, let alone what I have in mind long term.

Actually your example is very tangible to the problem and you its solution can serve as the template to a whole family of similar cases. You are the right direction.. You should start getting used in terms of tier separation and clearly understand the separation of data storage (Access, mysql, flat files or whatever else you are using), the middle tier which is where you are actually using these data to perform your calculations (this can be VB, python, java as an example) and the front end (excel, browser of fat client)... Try to achieve transparency between these tiers and experiment with having more than one platforms for each one... There are a lot of interesting problems that you will face as you learn more, like injection of control, parallel processing, talking to third parties, building code on the fly etc...

[MJC922]

I don't want to speak for sjk but as far as I can tell the solutions provided so far in this thread cover every one of the requirements I've heard thus far.

[classhandicapper]

I'm sure I'll have more questions, but I want to thank everyone that offered insights. You have all saved me a lot of time. I appreciate it.

[classhandicapper]

Here's an interesting issue.

In my Horse database, the names of foreign horses do not have the country of breeding as part of the name (GB), (FRA) etc.....

In the Results database it is included.

So the horses will not match on name as a key field when I do my queries.

[raybo]

The horse name field shouldn't include the country of birth, unless you created that field yourself, the name and country of birth should be in separate fields in the raw data files. You will probably have to create a new field to include the horse's name and date of birth, etc., in order to avoid the problem of more than one horse having the same name. But, all this I based on the fields in Brisnet and JCapper/HDW data and results files. Don't know what fields you have in your data and results files.

[vegasone]

You will need to strip the country out before you save the name but....you will need to be able to differentiate between two horses with the same name but different countries...happens rarely but it happens. Depends on what you are going to do with the data for research. May not be a problem.

[classhandicapper]

The data is in my tables the same way it was provided to me (inconsistently).

Is there a way to strip it out automatically now that it's already in the database that way or will I have to strip it out manually and rebuild the table?

[vegasone]

It is pretty easy to do it manually if you don't want to complicate things. A search and replace in access will do it as long as you are careful and always make sure the country is in () (JP) etc. Or write a simple query.

[Exotic1]

The data is in my tables the same way it was provided to me (inconsistently).

Is there a way to strip it out automatically now that it's already in the database that way or will I have to strip it out manually and rebuild the table?

You can trim it "electronically". Look at the Instr, Mid, Right and Left - functions.

You would want to run an Update query, trimming the country code and parentheses from the Horses name.

Here's an example:

Expr1: Left([H_Name],InStr([H_Name],"(")-1)

http://support.microsoft.com/kb/286238


Google Search: "Remove part of data string in Access"

[classhandicapper]

You guys rule!

Seriously, if I ever meet any of you guys at the track, coffer, beer, whatever your choice is on me.

[sjk]

You can trim it "electronically". Look at the Instr, Mid, Right and Left - functions.

You would want to run an Update query, trimming the country code and parentheses from the Horses name.

Here's an example:

Expr1: Left([H_Name],InStr([H_Name],"(")-1)

http://support.microsoft.com/kb/286238


Google Search: "Remove part of data string in Access"

That is the way to do it. Update queries are irreversible so if you are new to it you might copy your data table just in case.

I keep a list of duplicate horses so as to not confuse them when they run. There are 394 names in the table but in many cases neither of the two has run in years.

[Exotic1]

That is the way to do it. Update queries are irreversible so if you are new to it you might copy your data table just in case.

I keep a list of duplicate horses so as to not confuse them when they run. There are 394 names in the table but in many cases neither of the two has run in years.

Good point.

Thanks.

[Exotic1]

That is the way to do it. Update queries are irreversible so if you are new to it you might copy your data table just in case.

I keep a list of duplicate horses so as to not confuse them when they run. There are 394 names in the table but in many cases neither of the two has run in years.

Along those lines Class, you can add a data field to the existing table which keeps a picture of the original data string. Once you are comfortable with the test results you can choose to eliminate the original or keep it - so you would have a data field called H_Name and a backup called H_Name_Original, as an example.

[whodoyoulike]

Here's an interesting issue.

In my Horse database, the names of foreign horses do not have the country of breeding as part of the name (GB), (FRA) etc.....

In the Results database it is included.

So the horses will not match on name as a key field when I do my queries.

I'm curious. How are the foreign horses in the Horse database cross-referenced in the Results database? Otherwise, how does DRF create the foreign horse's PP?

[classhandicapper]

I'm curious. How are the foreign horses in the Horse database cross-referenced in the Results database? Otherwise, how does DRF create the foreign horse's PP?

Internally, I do not know what DRF does. I only know what I exported from Formulator and DRF Charts service.

[whodoyoulike]

Internally, I do not know what DRF does. I only know what I exported from Formulator and DRF Charts service.


I realize that's what you're doing. But, the cross-referencing algorithm should be the same whether the horse was domestic or foreign between the two databases. But, you're going to be creating a separate procedure which may not be applicable in all situations. Unless, you're positive it will maybe, there's a foreign horse code?

[classhandicapper]

You can trim it "electronically". Look at the Instr, Mid, Right and Left - functions.

You would want to run an Update query, trimming the country code and parentheses from the Horses name.

Here's an example:

Expr1: Left([H_Name],InStr([H_Name],"(")-1)

http://support.microsoft.com/kb/286238


Google Search: "Remove part of data string in Access"


I did a manual find and replace to get it done because that was the easiest thing for me to do given my current knowledge.

I understand what you are suggesting above, but to be honest I have no idea how to accomplish it. I don't know how to create an Update Query.

I created a new "Garbage" table for testing with some names I wanted to change. I went into Query Wizard to create a Query, went into design view, added my Garbage table, and entered the expression below in one of the columns in the blank new query.

NewName: Left([Horse],InStr([Horse],"(")-1)

It did what it was supposed to do in that column and removed all the "(GB)" etc...

However, I have no idea how to apply that to my real table so the old names are overlaid with the new names and I still have all the other data intact. This just created a query result with that single field updated.

[classhandicapper]

I realize that's what you're doing. But, the cross-referencing algorithm should be the same whether the horse was domestic or foreign between the two databases. But, you're going to be creating a separate procedure which may not be applicable in all situations. Unless, you're positive it will maybe, there's a foreign horse code?

My guess is that internally there may be multiple sources of information and some manual work done when it comes for foreign PP lines. That's why the names are different in the 2 extract files.

There is a field in both files that tells me where the horse was bred. So if 2 horses have the same name, I will be able to tell them apart that way.

[classhandicapper]

I am importing Past Performance Data into the following tables:

1. Race Info
2. Horse Info
3. Trainer Info
4. Jockey Info
5. Running Line Info
6. Race Results
7. Pace Info

Each of 1-5 is fairly straightforward, but it does take time to download the Formulator extract, unzip it, and then import each file 1 at a time into the appropriate table.

#6 takes longer because I have to download it, import it into an Excel spreadsheet, add the Track and Date columns I need that are missing from the "S records, add a header record because it won't append the data without one, and then import it.

#7 is private data I am entering into spreadsheet and then importing.

All told it takes me close to 15-20 minutes to import a single card for one day. There are probably some ways to automate some of this, but right now that's a lot of time to spend just getting the data in place for one track for one day.

What do most people do in order to cut down time if you want to load data for multiple tracks every day?

[raybo]

I am importing Past Performance Data into the following tables:

1. Race Info
2. Horse Info
3. Trainer Info
4. Jockey Info
5. Running Line Info
6. Race Results
7. Pace Info

Each of 1-5 is fairly straightforward, but it does take time to download the Formulator extract, unzip it, and then import each file 1 at a time into the appropriate table.

#6 takes longer because I have to download it, import it into an Excel spreadsheet, add the Track and Date columns I need that are missing from the "S records, add a header record because it won't append the data without one, and then import it.

#7 is private data I am entering into spreadsheet and then importing.

All told it takes me close to 15-20 minutes to import a single card for one day. There are probably some ways to automate some of this, but right now that's a lot of time to spend just getting the data in place for one track for one day.

What do most people do in order to cut down time if you want to load data for multiple tracks every day?

Does Formulator not have a batch processing routine? If not, then you will have to do that some other way. If it were me doing this, I would use an automated (with VBA) batch processing routine in Excel, which would save all the data from each race and each card (and results, as I would include the importing of the results files in the batch processing routine, so you import both the card data and the results data in the same step), to another worksheet(s), convert that sheet's data into a csv file, and then import it all into Access. Don't know if that is possible the way you are splitting data into separate files, but that is the way I would try to approach it anyway.

[classhandicapper]

I did a manual find and replace to get it done because that was the easiest thing for me to do given my current knowledge.

I understand what you are suggesting above, but to be honest I have no idea how to accomplish it. I don't know how to create an Update Query.



I got an Update Query to work.

I knew "Access 2013 for Dummies" would come in handy. :lol:

[classhandicapper]

Does Formulator not have a batch processing routine? If not, then you will have to do that some other way. If it were me doing this, I would use an automated (with VBA) batch processing routine in Excel, which would save all the data from each race and each card (and results, as I would include the importing of the results files in the batch processing routine, so you import both the card data and the results data in the same step), to another worksheet(s), convert that sheet's data into a csv file, and then import it all into Access. Don't know if that is possible the way you are splitting data into separate files, but that is the way I would try to approach it anyway.

There are 11 possible files to download that contain everything in the DRF PPs. They are broken up into logical units: Jockey, Trainer, PP Lines, Race Info, Workouts, Horse Info, Beyer and Tomlinson etc...

You can download them separately or in one big file.

If you download them at once, you have a huge file with varying record types, different fields, different record lengths etc... So you'd need some way to break it all out logically for importing purposes anyway.

If you download them the way I'm doing it, they are broken up in a fairly logical way for importing purposes into separate tables, but you have to do them 1 at a time.

You can also download all the cards for a specific track at one time (a bunch of days), but that puts you in the same position because they are separate files, 11 for each date.

[classhandicapper]

You don't even need a script to merge the exports, just open a command window and do it in a few seconds. I don't remember the extension of the exports, but something like this:

copy *.drf allexports.txt

Could you elaborate on this a little?

I'm sure you know what I am talking about in my previous post to Raybo because you've worked with the DRF files.

I currently have a separate table for each of the Formulator files. I don't see how I can have them in one file and get them all into the appropriate separate tables. I also I don't see how in a single command in Access can import each of the files into the appropriate table with all the parameters needed (comma delimited, first row = headers etc..).

[classhandicapper]

One program I find very helpful is Simple file Joiner - freeware. I use it to join text files - maybe a 100 at a time - all the daily chart files - to make one file to use for importing.

This sounds like a possible solution for dealing with multiple days or multiple tracks.

I'd still have to import each file type into its appropriate Access table, but I could do multiple days or multiple tracks at one time. The one problem I could see running into is that the Append function for importing is requiring me to have a Header record as the first record. So if I combined files I'd have multiple header records that would cause problems or I'd be back to manually deleting the ones I don't need.

[cj]

Could you elaborate on this a little?

I'm sure you know what I am talking about in my previous post to Raybo because you've worked with the DRF files.

I currently have a separate table for each of the Formulator files. I don't see how I can have them in one file and get them all into the appropriate separate tables. I also I don't see how in a single command in Access can import each of the files into the appropriate table with all the parameters needed (comma delimited, first row = headers etc..).

My suggestion was for the single file option. You chose not to use it, so you'd need to do the above for each of the different file extensions.

There is nothing difficult about the single file option like you seem to believe.

[classhandicapper]

My suggestion was for the single file option. You chose not to use it, so you'd need to do the above for each of the different file extensions.

There is nothing difficult about the single file option like you seem to believe.

The single file option didn't buy me much because then I had one giant file with different kinds of data and record descriptions that ultimately had to be broken up logically anyway.

Let's say I downloaded all the cards for today (6 tracks).

I'd have 6 files of PPs data, 6 files of Trainer data, 6 files of Race Info and so on.

Then I'd basically concatenate the 6 of each type into 1 file each and could import them in 1 shot. So it might still be a several imports, but I could do multiple days or tracks at 1 time.

Correct?

[classhandicapper]

Let's say I downloaded all the cards for today (6 tracks).

I'd have 6 files of PPs data, 6 files of Trainer data, 6 files of Race Info and so on.

Then I'd basically concatenate the 6 of each type into 1 file each and could import them in 1 shot. So it might still be a several imports, but I could do multiple days or tracks at 1 time.

Correct?

OK thanks. I see how this will work much better. :ThmbUp:

Now I have one last import issue.

When I created my tables I used the the header records to establish the field names. Now when I try to append new records to a table it insists that I have header record. So I've been downloading the files from Formulator with a header every time and then importing them that way.

If I am going to concatenate all these files, I'll have lots of headers - which is no good. So I'm going to have download without headers but then somehow have a file with just a header that will concatenated with the data as the first record.

That makes sense correct?

[Exotic1]

I did a manual find and replace to get it done because that was the easiest thing for me to do given my current knowledge.

I understand what you are suggesting above, but to be honest I have no idea how to accomplish it. I don't know how to create an Update Query.

I created a new "Garbage" table for testing with some names I wanted to change. I went into Query Wizard to create a Query, went into design view, added my Garbage table, and entered the expression below in one of the columns in the blank new query.

NewName: Left([Horse],InStr([Horse],"(")-1)

It did what it was supposed to do in that column and removed all the "(GB)" etc...

However, I have no idea how to apply that to my real table so the old names are overlaid with the new names and I still have all the other data intact. This just created a query result with that single field updated.

Later today, I'll try and post some sql code here that hopefully will help.You'll be able to copy and paste into your db to test.

[classhandicapper]

Later today, I'll try and post some sql code here that hopefully will help.You'll be able to copy and paste into your db to test.

I was able to solve that one. I appreciate the help. I'm getting there. lol

[BIG49010]

My suggestion was for the single file option. You chose not to use it, so you'd need to do the above for each of the different file extensions.

There is nothing difficult about the single file option like you seem to believe. I know Excel will let you take that many fields, but their Database does too?

[raybo]

Whew! Is Access really that restrictive, regarding the use of automated routines to grab fields from text/csv files, and send them to different tables? I know, in Excel, you can use VBA to split huge csv files into whatever you want, even the older versions that had only 256 columns. You just import the file and split the fields onto a few rows rather than a single row. Then, by referencing/mathematical formulas you create as many "views" as you want, essentially creating multiple lists/tables from that large single file. Once that is done, it's done forever. You just select the cards and results files you want to import from a list of files in a folder, and click a button.

Surely there is some way to combine Excel and Access, using Excel as a front end to get the data in the format you need for Access.

[cj]

The single file option didn't buy me much because then I had one giant file with different kinds of data and record descriptions that ultimately had to be broken up logically anyway.

Let's say I downloaded all the cards for today (6 tracks).

I'd have 6 files of PPs data, 6 files of Trainer data, 6 files of Race Info and so on.

Then I'd basically concatenate the 6 of each type into 1 file each and could import them in 1 shot. So it might still be a several imports, but I could do multiple days or tracks at 1 time.

Correct?

Not really...I don't see any reason the data can't be in one table. In fact, you are now duplicating data in all the different tables (Track, Date, Race#, etc). Maybe I'm confused about what you are trying to do, but since you are just starting out I doubt is near the scope of some of the things I do and I do it all from the single file.

[cj]

OK thanks. I see how this will work much better. :ThmbUp:

Now I have one last import issue.

When I created my tables I used the the header records to establish the field names. Now when I try to append new records to a table it insists that I have header record. So I've been downloading the files from Formulator with a header every time and then importing them that way.

If I am going to concatenate all these files, I'll have lots of headers - which is no good. So I'm going to have download without headers but then somehow have a file with just a header that will concatenated with the data as the first record.

That makes sense correct?

Sorry, didn't see this one first. I just import without headers. I designed the table first to match the file description which is available via Formulator, including specific field data types and sizes.

[cj]

Whew! Is Access really that restrictive, regarding the use of automated routines to grab fields from text/csv files, and send them to different tables? I know, in Excel, you can use VBA to split huge csv files into whatever you want, even the older versions that had only 256 columns. You just import the file and split the fields onto a few rows rather than a single row. Then, by referencing/mathematical formulas you create as many "views" as you want, essentially creating multiple lists/tables from that large single file. Once that is done, it's done forever. You just select the cards and results files you want to import from a list of files in a folder, and click a button.

Surely there is some way to combine Excel and Access, using Excel as a front end to get the data in the format you need for Access.

Yes, you can import Excel files just as easily as you can import text files, several other types too.

[cj]

I know Excel will let you take that many fields, but their Database does too?

Yes, no problem.

[classhandicapper]

Not really...I don't see any reason the data can't be in one table. In fact, you are now duplicating data in all the different tables (Track, Date, Race#, etc). Maybe I'm confused about what you are trying to do, but since you are just starting out I doubt is near the scope of some of the things I do and I do it all from the single file.

I realize I am duplicating some information in multiple tables, but I don't know how I could import a single file with 7 or more record types (trainer, jockey, PP lines, race info, horse info, etc.. ) into 1 table and have all the information go into the appropriate fields.

Right now, each table is joined by Track, Date, Race# info or Horse Name or whatever else is appropriate. One table for each record type.

I would rather have it all in one table if I knew how to import each of the _H, _HR, _JF, _R, _TF, _CF, _S etc.. files that make up one race card into 1 table and have it actually work.

I am just learning this product, but after you do a download from Formulator as 1 file, it seems to me what you have is 1 file that's a giant mess of mixed data types. You would have to let Access know which fields to put where unless you broke it up logically on your own via a personal program outside of Access so there was one record for each Date, Race, Race # etc.. Otherwise you'd import a mess.

[cj]

I realize I am duplicating some information in multiple tables, but I don't know how I could import a single file with 7 or more record types (trainer, jockey, PP lines, race info, horse info, etc.. ) into 1 table and have all the information go into the appropriate fields.

Right now, each table is joined by Track, Date, Race# info or Horse Name or whatever else is appropriate. One table for each record type.

I would rather have it all in one table if I knew how to import each of the _H, _HR, _JF, _R, _TF, _CF, _S etc.. files that make up one race card into 1 table and have it actually work.

I am just learning this product, but after you do a download from Formulator as 1 file, it seems to me what you have is 1 file that's a giant mess of mixed data types. You would have to let Access know which fields to put where unless you broke it up logically on your own via a personal program outside of Access so there was one record for each Date, Race, Race # etc.. Otherwise you'd import a mess.

I'm sure I am misunderstanding something, because this just wasn't that complicated. I found the file definition, I created a blank table to match, and I started importing daily. None of this took more than an hour total. Once I did a file or two, I just did the bulk download of all tracks every day, merged them into one using the method I describe via a command prompt, and that was it.

I do know this, you are going to have to create relationships between a lot of tables doing it your way with multiple files. That will cause queries to take longer and longer the more complicated and deeper you want to go.

Unless I can figure out where the disconnect between us is, going to have to bail out of this one. I tried to find that database but I don't think I put it on my new computer since I obviously don't use Formulator any longer.

[raybo]

I'm sure I am misunderstanding something, because this just wasn't that complicated. I found the file definition, I created a blank table to match, and I started importing daily. None of this took more than an hour total. Once I did a file or two, I just did the bulk download of all tracks every day, merged them into one using the method I describe via a command prompt, and that was it.

I do know this, you are going to have to create relationships between a lot of tables doing it your way with multiple files. That will cause queries to take longer and longer the more complicated and deeper you want to go.

Unless I can figure out where the disconnect between us is, going to have to bail out of this one. I tried to find that database but I don't think I put it on my new computer since I obviously don't use Formulator any longer.

Is he talking about the export file from Formulator, and you are talking about the import file into Formulator? You have to have a file to shoot into Formulator, right? That file is probably a csv file, and unless it's encripted, could be imported into Access, Excel, or any other app that works with csv/delimited files.

[cj]

Is he talking about the export file from Formulator, and you are talking about the import file into Formulator? You have to have a file to shoot into Formulator, right? That file is probably a csv file, and unless it's encripted, could be imported into Access, Excel, or any other app that works with csv/delimited files.


No, it is an export file that you get from Formulator. They just have a few different options on the format.

[bcgreg]

Yes, no problem.

CJ

I am pretty sure that Access tables are limited to 256 fields.

bcgreg

[classhandicapper]

I'm sure I am misunderstanding something, because this just wasn't that complicated.

Unless I can figure out where the disconnect between us is, going to have to bail out of this one. .


Let's do the simplest case first (1 race card from 1 racetrack).

We agree that you can get a Formulator extract for a single day and single track as 1 file or multiple files.

The reason the multiple file option exists is that each of those files contains unique information, unique fields, and is in a unique format (trainer stats, jockey stats, horse data, tomlinson data, earning box data, Beyer data, workouts...). If they are merged into 1 file, you have a lot of different record layouts inside the same file.

It's quite possible that when you were using Formulator, you were only using the main PP file and discarding the other specialized Trainer, Jockey, Workout, Beyer and Tomlinson data that are in the other records that are part of DRF PPs.

I am using some of that other data. So I am building the main PP file into 1 table and the specialized data I want into others. Then I am joining them.

That may explain the disconnect.

As far as merging multiple days or cards goes, you solved my problem. I was able to do a copy and merge several days of data and turn it into one import for a table. So one major issue has been solved well enough. Thanks!!!! When you visit NY, lunch is on me.

[cj]

CJ

I am pretty sure that Access tables are limited to 256 fields.

bcgreg

Yes, true, but there aren't that many fields in the file I was discussing if I remember right. It has been a few years though.

[cj]

Let's do the simplest case first (1 race card from 1 racetrack).

We agree that you can get a Formulator extract for a single day and single track as 1 file or multiple files.

The reason the multiple file option exists is that each of those files contains unique information, unique fields, and is in a unique format (trainer stats, jockey stats, horse data, tomlinson data, earning box data, Beyer data, workouts...). If they are merged into 1 file, you have a lot of different record layouts inside the same file.

It's quite possible that when you were using Formulator, you were only using the main PP file and discarding the other specialized Trainer, Jockey, Workout, Beyer and Tomlinson data that are in the other records that are part of DRF PPs.

I am using some of that other data. So I am building the main PP file into 1 table and the specialized data I want into others. Then I am joining them.

That may explain the disconnect.

As far as merging multiple days or cards goes, you solved my problem. I was able to do a copy and merge several days of data and turn it into one import for a table. So one major issue has been solved well enough. Thanks!!!! When you visit NY, lunch is on me.

Told you there was a disconnect, I get it now. I didn't realize all the data wasn't included in the single file. It must have been stuff I wasn't using.

[raybo]

Told you there was a disconnect, I get it now. I didn't realize all the data wasn't included in the single file. It must have been stuff I wasn't using.

I bet all that data is also in the single file, but those fields are mixed in all over the place in the single file. I know that Brisnet's multifile, and their single file contain the same data, it's just that in the multifile the data has been separated by type into separate files, for ease of use and ease of import into apps that only have 256 fields/columns.

[cj]

I bet all that data is also in the single file, but those fields are mixed in all over the place in the single file. I know that Brisnet's multifile, and their single file contain the same data, it's just that in the multifile the data has been separated by type into separate files, for ease of use and ease of import into apps that only have 256 fields/columns.

I don't think all the data is there, but again, it has been a few years. I definitely don't remember Tomlinson Ratings for example.

You are definitely correct about BRIS, same data regardless of format.

[classhandicapper]

I bet all that data is also in the single file, but those fields are mixed in all over the place in the single file. I know that Brisnet's multifile, and their single file contain the same data, it's just that in the multifile the data has been separated by type into separate files, for ease of use and ease of import into apps that only have 256 fields/columns.

If the DRF data is easy to separate inside the single file, it's not in the documentation. Visually it looks like a nightmare to me. That's why I immediately went the multi file direction.

Besides, I have no interest in the Tomlinson numbers, top Beyers on each surface, or even the workouts at this point. But I am interested in some of the trainer data, jockey data, and the consistency box. So I created tables for those and discarded the others.

At this stage though, you guys have already helped me get to the point where I am fast closing in on having the database I want and a process for loading it that won't be too time consuming. :ThmbUp:

[classhandicapper]

Again I come back to advising the use of a staging table. Once you have it in there you can do something with it. Try not to think about filtering on the way in, get it in first, even though it's all chaotic and messy in the staging table you can work with it. For example if there's an S to identify that entire record as race data then a select query on the staging table in the QBE grid with an S added as criteria for that field brings up only those records. Switch that from a select query to an append query to push those records out to a specific table.

Can we revisit this?

I had no difficult creating a query that will prompt me for the Track and Date (the 2 fields I need added to me staging table). It will then generate results that include both the Track and Date I entered and the rest of the fields I need for just "S" records.

It's getting from there to Append that's giving me trouble.

Do I have to recreate the entire query as an Append query to accomplish the task of doing an actual append?

That the way it seems. Somehow I accidentally wiped out my query but just switching to append.

Also, one minor quirk. When you click on the "*" to add all the fields of a table to the query (there are 92 fields in this case), is there any way to say "all except 1 or 2 fields" you might want to leave off instead of listing all 90?

[MJC922]

Can we revisit this?

I had no difficult creating a query that will prompt me for the Track and Date (the 2 fields I need added to me staging table). It will then generate results that include both the Track and Date I entered and the rest of the fields I need for just "S" records.

It's getting from there to Append that's giving me trouble.

Do I have to recreate the entire query as an Append query to accomplish the task of doing an actual append?

That the way it seems. Somehow I accidentally wiped out my query but just switching to append.

Also, one minor quirk. When you click on the "*" to add all the fields of a table to the query (there are 92 fields in this case), is there any way to say "all except 1 or 2 fields" you might want to leave off instead of listing all 90?

I would recommend saving the select query. Then on the left side of the interface you should have a list of All Access Objects, expand Queries, the query you saved should be listed, right-click it and choose copy, right click somewhere in the same area and choose paste. Now you can right click on the copy choose design, go up top to the ribbon and click on Append, it will prompt you for a table to append to, Choose the appropriate table from the drop down menu, click ok, save the query then you can run it. Rename the append query later to something more appropriate than copy of etc.

Queries can call other queries so descriptive naming will help in the long run. For example you aren't limited to creating a new query and adding a table to work with, you can set up a totals query to group by certain fields and average on others, this might use another query as its source. Nested queries can be slower to finish executing but it's a cool option to play around with and can be changed into a make table query for example.

For the minor quirk you mentioned, in the query design area you should be able to shift click all of the fields in the table and then ctrl click on the ones you don't want, right click on what's selected and drag it onto the grid. You can also just shift click to select them all, drag them down and just uncheck the fields you don't want shown in the result. I prefer the latter method, that way those fields can still be used for criteria even if you don't want them to show. If you don't include them at all then you can't set criteria there. Your call though, just depends on the situation.

[classhandicapper]

MJC922,

I got the Append working properly. Thanks.

Basically, I still have a bit of a tedious process for the Chart Results, but it's WAY better now.

1. Download the Track/Date Charts I want from DRF.
2. Upload the specific one I want first into a Staging Table.
3. Run the "S" record Append to it's appropriate table.
4. Run the "R" record Append to it's appropriate table.
5. Run a Delete Query to initialize the Staging Table
6. Go to step 2.

I don't see much of a way to streamline it from here. It takes a couple of minutes per Track/Date. If I was doing this for 20 tracks, it would be tedious. But I'm only doing a few.

The Appends work nicely. I manually enter the Track and Date I want inserted into the table, it selects the records I want, and adds them with my manual fields. :ThmbUp:

[MJC922]

Sounds good Class. Adding one last step to this -- every so often go to the File tab and click the 'Compact and repair' button. You're doing the right thing to clear the staging table(s), keep in mind however the bloat from deleted records in Access isn't cleared out until you compact the db. If not compacted regularly Access can slow down, eventually to a crawl.

A nice option in some cases is to configure 'Compact on close' under File tab>Options> Current Database.

[classhandicapper]

Thanks

I ran across a problem with the "S" Append that I wasn't expecting after the "R" Append worked so smoothly.

When I originally imported the Chart File into the Staging table, it defined everything as Short Text even though there are numeric fields here or there depending which record type you are looking at.

In my final tables (both R and S) some of the fields were defined as numeric when I created those table using an Excel file as input. The R's are appending fine, but I'm getting the error message on the "S" append. The problem is it won't even tell me which field(s) are a problem so I can see what's going on.

After I got the problem, I decided to see if I could import the same exact data as an Excel file instead of as an Append and it worked fine. :bang:

Is there any way I can trace the problem field(s) or get a clue as to what the issue is?

Error

"MSFT Access set 113 fields to to null due to a type conversion failure and it
didn't add 0 records due to key error, 0 due to lock, 0 due to rules violations. To ignore and run query click Yes."

I click Yes, it seems to run, nothing gets added. No messages.

[MJC922]

Sometimes Access will create an extra table in these cases named errors or something like that so you can open it up and at least track down the field name. I don't know how many fields are in the file but worst case you may have to just pick through it using the table designer to see what doesn't look right. Take a look at the saddlecloth number field, it should be text but can get tagged as numeric.

[classhandicapper]

Sometimes Access will create an extra table in these cases named errors or something like that so you can open it up and at least track down the field name. I don't know how many fields are in the file but worst case you may have to just pick through it using the table designer to see what doesn't look right. Take a look at the saddlecloth number field, it should be text but can get tagged as numeric.

I get that extra table from time to time with the errors, but unfortunately not the one time I needed it.

I'll have some time to take a better look at it field by field tonight......all 92 of them. :-)

[sjk]

Use the function val([xyz]) if you want to convert a string of numeric characters to the number it looks like.

[MJC922]

I get that extra table from time to time with the errors, but unfortunately not the one time I needed it.

I'll have some time to take a better look at it field by field tonight......all 92 of them. :-)

I tell you folks, it's harder than it looks. It's a long way to the top if you wanna rock n roll. :cool:

[classhandicapper]

No luck whatsoever.

I checked every field identified as numeric in the target table to make sure the source field actually had a number in it. Every one did.

Now granted, even though they contain numbers, some of those fields in the staging table are identified as text. So that could be the issue. The system may think it's trying to move text to numeric. However, that was true of the "R" record append also and it worked.

I could go into the Staging table and define all the numeric fields on the S records as numeric, but that may cause other problems. Then some of the text fields in the R record will be defined as numeric when they really contain text. :lol:

If I identified some of the numeric fields as text in the target table I assume I wouldn't be able to use them for numeric calculations anymore. Correct?

I could also easily do it the long way and manually edit my Excel files and then import them, but the whole point of using the Append was avoid all the manual stuff.

If both Appends weren't working, this would be less frustrating. As it is it just makes me hate the product for giving me an error but absolutely no clue as to which fields are a problem and why.

[classhandicapper]

I think I found it !

I made a few typographically errors in the source/target field moves.

Too tired to deal with tonight. I'll try tomorrow. Wish me luck!

[classhandicapper]

If you have 2 tables joined on Track and Date and one of them has the date in yyyy/mm/dd format and the other has them in mm/dd/yyyy, will that join work?

If not, is there an easy way to do a global change of all the dates in specific table?

Second question.

One of my tables has the Trainer Name inside one field in "Last First MI" format and another has it in 3 separate fields Last, First, and MI. Is there a way to join those 2 tables on trainer name?

(same with Jockey)

[cj]

If you have 2 tables joined on Track and Date and one of them has the date in yyyy/mm/dd format and the other has them in mm/dd/yyyy, will that join work?

If not, is there an easy way to do a global change of all the dates in specific table?

Second question.

One of my tables has the Trainer Name inside one field in "Last First MI" format and another has it in 3 separate fields Last, First, and MI. Is there a way to join those 2 tables on trainer name?

(same with Jockey)

No, it won't work. Joins only work when exact matches. There are ways around it using string functions, but it isn't easy.

[sjk]

For each table you make a query that contains all of the original fields and also three more where you use functions to split the date or trainer data into three parts. Then make a query which uses the two queries above and joins them along the split up fields.

[classhandicapper]

OK.

The date issue I can handle easily with a one time query or a "find replace" to the existing table with the wrong date format. That was my doing. I created that table with "my data" using the wrong date format and can do it right going forward once I fix what's in already place.

The Trainer/Jockey name issue I assumed would involve some sort of string/unstring. I was just wondering how to accomplish and organize it to minimize the pain.

If a table already exists (Trainer table for example), can't I just create 3 new fields in it and then run an update query of some kind that will update those fields with the unstrung trainer name? Then I can join it properly.

The only downside there would be can I continue importing the real live data into that table even though it doesn't have those 3 fields in it. If I can, I prefer that solution. I'll just run the unstring update query every once in awhile.

I'd prefer to have the trainer name inside the same table twice (once in 1 field and once in 3 fields) as opposed to having a more complex set of queries with joins etc..

[MJC922]

OK.

The date issue I can handle easily with a one time query or a "find replace" to the existing table with the wrong date format. That was my doing. I created that table with "my data" using the wrong date format and can do it right going forward once I fix what's in already place.

The Trainer/Jockey name issue I assumed would involve some sort of string/unstring. I was just wondering how to accomplish and organize it to minimize the pain.

If a table already exists (Trainer table for example), can't I just create 3 new fields in it and then run an update query of some kind that will update those fields with the unstrung trainer name? Then I can join it properly.

The only downside there would be can I continue importing the real live data into that table even though it doesn't have those 3 fields in it. If I can, I prefer that solution. I'll just run the unstring update query every once in awhile.

I'd prefer to have the trainer name inside the same table twice (once in 1 field and once in 3 fields) as opposed to having a more complex set of queries with joins etc..


In the table with three separate fields for Trainer Name I would probably go into the table design mode and create another field called TrainerFullName. Then you need to create an update query to populate it. When creating the update query the field to update is going to be TrainerFullName. In the 'Update To' section you will want to specify something that looks like this:

Trim([MyTable]![LastName]) & " " & Trim([MyTable]![FirstName]) & " " & Trim([MyTable]![MiddleInitial])

Change MyTable to whatever your table name is and the field names will also have to be changed to match your own.

[classhandicapper]

In the table with three separate fields for Trainer Name I would probably go into the table design mode and create another field called TrainerFullName. Then you need to create an update query to populate it. When creating the update query the field to update is going to be TrainerFullName. In the 'Update To' section you will want to specify something that looks like this:

Trim([MyTable]![LastName]) & " " & Trim([MyTable]![FirstName]) & " " & Trim([MyTable]![MiddleInitial])

Change MyTable to whatever your table name is and the field names will also have to be changed to match your own.

This is exactly what I'd like to do. Thanks.

Once I have my table in that format, can I continue importing the original format data into that table even though it won't have that extra field in it?

Assuming "yes", then I can just run this new update query whenever I want to string together the Trainer Name for all that new data I just imported.

Correct?

I feel like I'm getting there even though I haven't even tried to tough stuff yet . :ThmbUp:

[MJC922]

Yes I can't think of a reason why having the extra field will do anything to affect the import process. Should be fine.

[classhandicapper]

Yes I can't think of a reason why having the extra field will do anything to affect the import process. Should be fine.

The date change was extremely easy. All I had to do was go into Design Mode, click on the date field, and change the format. It took me 10 seconds.

The Trainer issue is going to be more complex.

Most of the names are going to be fine, but there are some that are not the same in the 2 files.

1. The one that has it broken up into 3 fields always has a period after the initial. The other has a space. So I'd have to strip out all the periods with an update query before stringing it together.

2. One has some names like Graham H. Motion and the other H Graham Motion where they switched the first and middle names (a few like that)

3. One has the middle initial for some trainers and the other has the full middle name.

Those will never match.

So I'm either going to have to write update queries for all trainers that are mismatched to get them in the same format or find a different way to join various tables (my brain is smoking).

[MJC922]

I hate to say it but from my experience some (not all) of these naming issues may turn out to be impossible for you to work around. I've had to resort to many custom functions coded to get as close to matching the naming conventions in use between the various files. Even at that, to this day I still have some names that show up in the PPs different from the way they're named in the charts. It's astonishing to me these providers don't see the importance of having consistent naming between the various files and the PPs.

[whodoyoulike]

I remember this name matching problem also occurs with jockey names.

[classhandicapper]

Fortunately, it's not a catastrophic issue. I'll do what I can to work around it.

[classhandicapper]

Is there a way to display edited fields as part of an Inquiry?

For example, suppose Odds are stored as 100 for 1.00 - 1, 1750 for 17.50 - 1 etc... and I want to display 1.00 and 17.50 etc...

[raybo]

Is there a way to display edited fields as part of an Inquiry?

For example, suppose Odds are stored as 100 for 1.00 - 1, 1750 for 17.50 - 1 etc... and I want to display 1.00 and 17.50 etc...

Dang, is that a real world example, or just an example of a field you might want displayed differently?

I'm thinking seriously about trying a database again (have failed many times in the past) but I'm going to use Excel as the front end, import all the data into a batch processing Excel routine (both cards and results files at the same time), separate the data by category and send those to different "table" worksheets and format the data their so I don't have to do any of that in the DB (all the former will be automated via macros and formulas), then import those tables from Excel into Access, or what ever DB program I decide to use (having the same table names in the DB as the ones in Excel. Trying to do everything in the DB seems way too complex and time consuming.

[sjk]

You make any expression a field in a query. In this case [odds]/100. Right click on it to get a drop down for format and choose fixed with 2 decimal places.

[Dark Horse]

Is there a way to display edited fields as part of an Inquiry?

For example, suppose Odds are stored as 100 for 1.00 - 1, 1750 for 17.50 - 1 etc... and I want to display 1.00 and 17.50 etc...

I found that the answer to almost any excel related question can be found by googling it. You'd be amazed at how much people are willing to share. Often a ready made formula can be copied and pasted.

[classhandicapper]

You make any expression a field in a query. In this case [odds]/100. Right click on it to get a drop down for format and choose fixed with 2 decimal places.

Yikes, I'm embarrassed I didn't think of that. It's training. I used to be a Cobol programmer (in other words I'm a dinosaur). TY

I'm getting close to starting to write more serious queries now that can actually be used as a learning tool and for information retrieval. :ThmbUp:

[classhandicapper]

Dang, is that a real world example, or just an example of a field you might want displayed differently?

I'm thinking seriously about trying a database again (have failed many times in the past) but I'm going to use Excel as the front end, import all the data into a batch processing Excel routine (both cards and results files at the same time), separate the data by category and send those to different "table" worksheets and format the data their so I don't have to do any of that in the DB (all the former will be automated via macros and formulas), then import those tables from Excel into Access, or what ever DB program I decide to use (having the same table names in the DB as the ones in Excel. Trying to do everything in the DB seems way too complex and time consuming.

Setting up the database and writing basic queries was not that tough. I'm not spending a lot of time on this because I'm busy with work and other things, but I've made a lot of progress already with the help I've been getting here. Writing the tougher queries is likely to be more of a challenge for me, but you have to crawl before you can run. ;)

[classhandicapper]

I want to create a query I can use to evaluate shippers.

Part 1.

1. I have a table with today's Track, Date, Race #, Horse, and previous Track, Date, Race # ......other miscellaneous fields (basically the PP lines for each horse).

2. I am specifically interested in the previous Track in each runner’s most recent race.

3. I only want to select a record if the previous Track is not equal to today's Track in the runner’s most recent race (it will be the first one listed under his name).

4. I want to continue doing that for the entire table.

I tried doing this using Group and First, but it wasn’t just returning a runner's most recent race. It was returning the first one in the group it found that met that criteria of him being a shipper (which could be his 2nd, 3rd, 4th race back). I hope this makes sense. I can probably play with until I get it to work, but if it's a quickie answer I'm all ears.

[sjk]

Create a query to determine the most recent race date for each horse.

Create a query using the above where you join the most recent race date with the previous date in the main data table. Select as criterion that the previous track is not equal to today's track.

In order to get off the ground you are going to have to break things into pieces and use queries to solve for the pieces.

[classhandicapper]

Create a query to determine the most recent race date for each horse.

Create a query using the above where you join the most recent race date with the previous date in the main data table. Select as criterion that the previous track is not equal to today's track.

In order to get off the ground you are going to have to break things into pieces and use queries to solve for the pieces.

Thanks

If I create a query to solve the first part do I use then that query as input to the next query or do I create a table out of the first query results and then join it that way? :bang:

The good news is that loading all my tables for PPs, Results, and personal stuff is becoming a breeze. A few of the more basic queries I put together are already useful. So things are going well.

Here's one other thing.

I created a query to only select off data for a specific track after getting prompted for the Track ID. It works fine. But now suppose I wanted all of them. Is there a way to code to get all of them or whatever one I enter or do I need a separate virtually identical query to get all of them. (next I am going to code for a date range)

It's not that this stuff is difficult, but when you don't know the techniques and the online help available is either too advanced or not clear you can waste a lot of time experimenting trying to get it to work. lol

[MJC922]

You can enter wildcards in a parameter query. So if you're prompted for the track abbrev and you type a * you should get all tracks.

[sjk]

Thanks

If I create a query to solve the first part do I use then that query as input to the next query or do I create a table out of the first query results and then join it that way? :bang:


Either one. I would use the query as input. If you next too many queries inside others it slows things down and you make a table to improve performance. You will need a lot more complexity before that becomes an issue.

[classhandicapper]

Thanks guys.

I don't where you guys live, but if you are in NY and want to get together some time at AQU or BEL, let me know. Lunch/beers on me.

[classhandicapper]

You can enter wildcards in a parameter query. So if you're prompted for the track abbrev and you type a * you should get all tracks.

Worked like a charm. :ThmbUp:

[classhandicapper]

I'm at the stage where I accomplished a mildly more difficult query, but I'm pretty sure I didn't do it in an inefficient way.

1. Query 1 - I looked through some Horse data and calculated a Rating for each horse in each race.

2. Query 2 - I grouped the data from Query 1, by Track, Date, and Race and returned the horse with the highest Rating from each race

3. Query 3 - I went to the Results file displayed the finish position and the payoff (if it won) of the highest rated horse.


Things I want that it can't do without some manual intervention.

1. Total # of races
2. Total number of winners that had the highest rating
3. Sum of win payoffs for the horses that had the highest rating and won

I can get #1 by displaying the results of Query 3 and using one of the Total features

I can get #2 and #3, by only including the winners in Query 3 and using the Total features.

Does any of this make sense?

[sjk]

If I understand you Query 3 returns one row for each race with either a payoff or 0 depending on whether the top rated horse won.

Create query 4 using query 3 as input. Output fields should be expr1:1, expr2:iif([payoff]>0,1,0) and expr3:iif([payoff]>0,[payoff],0).

Use the sigma and set each of the expressions to be the sum. Of course if you cared to see the results by track you could also add a group by field on track.

[sjk]

Number of Races, Number of Wins and Payoff Total are probably better names than Expr1, Expr2 and Expr3.

[classhandicapper]

If I understand you Query 3 returns one row for each race with either a payoff or 0 depending on whether the top rated horse won.



Correct.

I'll try what you suggested. I never saw the "iif" function. Are some of these functions available through Query Wizard or do you just have to learn them and code them yourself?

Was the general approach I took of breaking it into 3 queries OK or did I make an easy query more complicated than it had to be?

I'm a little reluctant to go too far down this path only to learn a few months from now that I did everything all ass backwards. :bang:

[sjk]

Seems like you are doing fine to me

You might want to look at the avg of iif([payoff]>0,[payoff],-1) as return.

[sjk]

I messed up that last formula. How about the average of iif([payoff]>0,([payoff]-2)/2,-1). (converting the payoff back into odds)

[classhandicapper]

I'm having the strangest problem imaginable.

I have a Running Line table. I'm trying to pick up information from each horse's most recent race.

The first query is just going through all the records and eliminating all races with an invalid Beyer figure (foreign shippers and a few other miscellaneous oddball figures I saw that were clearly errors). That seems to be working correctly.

In the second query I'm grouping all the records by Track, Date, Race, and Horse and picking some fields in the First record (Track, Date, Race, Beyer Figure etc.... ). So theoretically I should have each horse's last Beyer.

This second query seems to be working for almost every race card, race, and horse I have in the table except for 2 cards at SA. Instead of picking up the horse's most recent race, it's picking up it's oldest race. ?????

Those were the very first race cards I loaded, but I don't see anything different in the data.

[cj]

Delete those two cards and reload them, that will tell you if it something in the data or the query.

[classhandicapper]

Delete those two cards and reload them, that will tell you if it something in the data or the query.

I just tried combining the 2 queries into 1 just to see if that might change something and I got and even stranger result. I only checked 1 race, but every horse from that race picked up the correct last race except 1 of them and that one picked up a race in the middle. :bang:

I'll try what you suggested.

(It's very hard to check every single horse, but it IS possible I had a few others with a problem)

[classhandicapper]

Delete those two cards and reload them, that will tell you if it something in the data or the query.

Thanks.

That seems to have corrected the problem, but it makes me wonder what other data is screwed up and why. I'm going to have to check the output of any queries I do a little closer.

[cj]

Thanks.

That seems to have corrected the problem, but it makes me wonder what other data is screwed up and why. I'm going to have to check the output of any queries I do a little closer.

There is no way for me to help with that without seeing it, but glad it worked.

[sjk]

I would calculate the most recent race date as the max of the dates associated with that horse and use that to pick out the most recent running line.

[classhandicapper]

I would calculate the most recent race date as the max of the dates associated with that horse and use that to pick out the most recent running line.

I think I used First because I wanted all (or at least most) of the fields in that PP line to pass to other queries. I took every field and used "First" with all of them. If you use Max you'll get the correct date, but when I tried variations like that it wouldn't allow me select off any other fields. I could only get the "group" fields and the date of the last race.

I grouped on track, date, race, horse for today's race.

Do you do a grouping first and then select MAX on that last race date?

Maybe there's something I can do that I'm simple not aware of.

[sjk]

Yes group on those 4 items and find the max of the last race date. Save that query create another query using that one and the original table where you join each field with the corresponding one.

[classhandicapper]

Yes group on those 4 items and find the max of the last race date. Save that query create another query using that one and the original table where you join each field with the corresponding one.

Thanks. After I posted my last message I realized what I had to do. It worked fine.

The stumbling block I have right now is that I keep trying to do too much inside a single query instead of breaking things up into logical units. My mind must be trained to think that way from prior data processing experience. :bang:

[raybo]

Thanks. After I posted my last message I realized what I had to do. It worked fine.

The stumbling block I have right now is that I keep trying to do too much inside a single query instead of breaking things up into logical units. My mind must be trained to think that way from prior data processing experience. :bang:

I'm not a programmer, but from messing with macros in Excel, I know that doing them in preliminary steps and saving them, and then combining the ones you need in another macro is much easier, and much less complex.

[classhandicapper]

Does anyone work with the Formulator files?

In the "Beyer" field in the running line file, if it's a foreign race they will typically put the Racing Post figure in that field instead.

I would like to eliminate those figures from some of my queries.

There's a "Foreign Race" field, but they don't seem to fill that one in to identify all the foreign races. There's a "Graded Race Description" field that seems to only get filled in with foreign race names and there's a course type that may be helpful "L for left", "R for right" and "S for straight" that could be useful in most situations, but there could easily be exceptions.

If anyone knows a way to handle that, I'd appreciate the insight so I don't have to spend hours investigating.

[cj]

Does anyone work with the Formulator files?

In the "Beyer" field in the running line file, if it's a foreign race they will typically put the Racing Post figure in that field instead.

I would like to eliminate those figures from some of my queries.

There's a "Foreign Race" field, but they don't seem to fill that one in to identify all the foreign races. There's a "Graded Race Description" field that seems to only get filled in with foreign race names and there's a course type that may be helpful "L for left", "R for right" and "S for straight" that could be useful in most situations, but there could easily be exceptions.

If anyone knows a way to handle that, I'd appreciate the insight so I don't have to spend hours investigating.

Let me check some old code...there is a way to know.

[cj]

The thing I seem to remember is that the "comment" field would have the track name in parentheses for foreign races, so I'd check for a "(" and if present, I knew it was foreign. That is probably the easiest way.

[sjk]

You could create a table of North American tracks and include only those races at a listed track.

[cj]

You could create a table of North American tracks and include only those races at a listed track.

I had that as well, but there are a few out there with same codes as US tracks.

[classhandicapper]

Thanks guys.

Hopefully between what I learned last night and your help I should have a grip on it. In the mean time, someone is getting a note on filling in the "foreign race" field. ;)

[classhandicapper]

Is there a way to select information from each of multiple records in a table and create 1 record out of it in a query?

For example: Suppose I wanted to look at each of a horse's last 5 races and pick up his speed figure and the class of the race and wind up a result that looks like this:

Horse Name, Fig1, Class1, Fig2, Class2, Fig3, class3, Fig4, Class4, Fig5, Class5

[cj]

Is there a way to select information from each of multiple records in a table and create 1 record out of it in a query?

For example: Suppose I wanted to look at each of a horse's last 5 races and pick up his speed figure and the class of the race and wind up a result that looks like this:

Horse Name, Fig1, Class1, Fig2, Class2, Fig3, class3, Fig4, Class4, Fig5, Class5

Yes, of course, if I'm understanding you right. Think the tables need to be linked.

[classhandicapper]

Yes, of course, if I'm understanding you right. Think the tables need to be linked.

It's one table with every horses' running lines coming into a race. So if a horse has 10 races, it will have 10 records (1 for each race). If he has 40 races, it will have 40 records for him. Each record will have both the figure he earned and the class of the race just like a running line in the past performances.

Instead of dealing with 5, 10, or whatever records, I was wondering if I could get something like the last 5 figures all on 1 record.

Are you suggesting I join a table to itself multiple times and manipulate it that way?

[sjk]

I don't have a really easy way but here is what I would do:

Use an append query to put the data into a table where you know the lines are sorted by current date, horse, and previous race date and which has a counter field so that the highest value of the counter for each horse relates to the most recent race.

Define a query grouping by current date and horse and finding the max of the counter. If you were dealing with a really large data set (millions of records) you would want to make this into a table.

Define a new query using the table with the counter, the query which calculates the max and join the two on current race date and horse (but no join on the ctr).

You can get at the values you want with expressions like lastsr:sum(iif([ctr]=[maxofctr],[sr],0)). The same with [maxofctr]-1 gets you the previous race, etc

[Jeff P]

...I was wondering if I could get something like the last 5 figures all on 1 record.
Assuming you have a field in the RunningLines table named RaceDate that contains Date Of Race, an easy way to get the 5 most recent running lines into a recordset - sorted in RaceDate order - most recent one first - would be to use the TOP and ORDER BY commands in your sql expression like this:

SELECT TOP 5 * FROM RunningLines ORDER BY RaceDate Desc



-jp

.

[Jeff P]

Amending my earlier post to make the SELECT statement specific to a single horse...

Assuming the RunningLines table contains a data field named HorseName that contains name of horse, an easy way to get the 5 most recent running lines into a recordset for a single horse (I'll use Secretariat for example purposes) sorted by RaceDate - most recent running line first/oldset running line last - might be to use the TOP, WHERE, and ORDER BY commands in a sql expression like this:

SELECT TOP 5 * FROM RunningLines WHERE HorseName = 'SECRETARIAT' ORDER BY RaceDate Desc

At this point, you have all data fields (*) from the RunningLines table for the desired number of records (5) in a recordset - and it becomes a simple matter of looping through the recordset one record at a time - and from there - displaying desired output.

Alternately, while looping through the recordset one record at a time, you could write selected data points that make up your desired output to a second table used to hold records for purposes of generating reports.

Hope I managed to type most of that out in a way that makes sense.


-jp

.

[classhandicapper]

Thanks guys.

I think it's going to be necessary for me to think through exactly what I want to do. Either way, these techniques will be useful to me going forward. Right now I have more ideas than time or knowledge, but I can see where I am heading long term. I think I'm going to be able to answer a lot of questions with hard data that typically I've had to learn by trial and error over very long periods of time.

One application I was wondering how to handle was doing calculations on figures that come from multiple races. That's why I wanted them on one record. If a horses's last few figures were on the same record it would be easy to average them or do whatever calculations I wanted with them and create another field. Then I could use that field as input somewhere else.

I've seen functions like average, total, count etc... on groups, but I haven't seen how to create personal calculations using multiple records and storing the result in one field that can be passed on.

[FocusWiz]

You are on the right track, there.

The most efficient method for capturing and storing data is not always the beat for later reporting of that data. In the area I work, we create reporting tables nightly to create the necessary data structures for reporting.

You may find that you don't need to store all five values if you get the average calculation working as desired (you may need a rule for handling horses with fewer than five outings). You may only need to store the average.

Good luck!

[MJC922]

I agree with FocusWiz. Please don't hesitate to create research databases for deep dives into specific areas. IMO you want to avoid the sense that this is my live database and I keep it current every day, as such this is where I will do all of my research too.

I look at the live db as more of a container and when I want to do research then I either push a ton of records out to another DB right from the get go using a make table out to an empty db or push the same out to Excel and then bring them back as a table in an empty db.

As for getting things on one line we discussed it earlier in the thread. There are several ways I've used in the past, you can do this with SQL or just punt it to Excel and do it that way, frankly the latter is probably going to be more straight-forward for new folks to get their head around. For example if you're sorted by horse then by date in Excel (aircode formula) IF HorseName in A1 matches HorseName in B1 then in the first open column of row1 show me the SpeedFig from Row2 otherwise give me "", continue on to the right with the same idea, fill it down to the bottom, and so on.

Be advised when you fill down a million rows of IFs in five columns like this is when you'll be glad you too missed the deal on Goren's $150 doorbuster laptop.

[raybo]

I agree with FocusWiz. Please don't hesitate to create research databases for deep dives into specific areas. IMO you want to avoid the sense that this is my live database and I keep it current every day, as such this is where I will do all of my research too.

I look at the live db as more of a container and when I want to do research then I either push a ton of records out to another DB right from the get go using a make table out to an empty db or push the same out to Excel and then bring them back as a table in an empty db.

As for getting things on one line we discussed it earlier in the thread. There are several ways I've used in the past, you can do this with SQL or just punt it to Excel and do it that way, frankly the latter is probably going to be more straight-forward for new folks to get their head around. For example if you're sorted by horse then by date in Excel (aircode formula) IF HorseName in A1 matches HorseName in B1 then in the first open column of row1 show me the SpeedFig from Row2 otherwise give me "", continue on to the right with the same idea, fill it down to the bottom, and so on.

Be advised when you fill down a million rows of IFs in five columns like this is when you'll be glad you too missed the deal on Goren's $150 doorbuster laptop.

Agree, this would be very simple to do in Excel, and then shoot it back into the db. With VBA it becomes even simpler, without all the nested "IF" statements - LOL.

[MJC922]

Agree, this would be very simple to do in Excel, and then shoot it back into the db. With VBA it becomes even simpler, without all the nested "IF" statements - LOL.

Yes, trying to keep it simple first and then build upon the concepts -- he will inevitably face the performance challenges we've all run up against. VBA of course is the far better option because with a subroutine there's no overhead from having so many cells left populated with formula, so consequently by using VBA to do this type of thing the spreadsheet is not left sluggish, bogged down and hogging memory. These things don't matter much for people who aren't working with large datasets but for a whole years worth of races we're looking at probably 450,000 rows. With the million row limit many people may choose to use Excel itself as a database and certainly it can be done but anyone who does it will have to become well acquainted with using VBA and subroutines to maintain performance. It would be nice if we could just be sloppy and fill millions of Excel's cells with formula and not take a hit. PCs are a lot faster than they were but it's surprising how quickly these types of operations cause lengthy bottlenecks.

[raybo]

Yes, trying to keep it simple first and then build upon the concepts -- he will inevitably face the performance challenges we've all run up against. VBA of course is the far better option because with a subroutine there's no overhead from having so many cells left populated with formula, so consequently by using VBA to do this type of thing the spreadsheet is not left sluggish, bogged down and hogging memory. These things don't matter much for people who aren't working with large datasets but for a whole years worth of races we're looking at probably 450,000 rows. With the million row limit many people may choose to use Excel itself as a database and certainly it can be done but anyone who does it will have to become well acquainted with using VBA and subroutines to maintain performance. It would be nice if we could just be sloppy and fill millions of Excel's cells with formula and not take a hit. PCs are a lot faster than they were but it's surprising how quickly these types of operations cause lengthy bottlenecks.

Yes, formulas that remain in workbooks can build up to unwieldy overhead. In a macro you can code all those "IF"s and other decision making scenarios, one time, and just loop it, so you end up with lots of data but no formulas. Anything that keeps Excel from having to make calculations saves gobs of memory and time lags, because Excel has to read all those formulas.

[classhandicapper]

Using Excel for some of these things and then importing the updated spreadsheet into a new table sounds like the best approach for "one time" or "occasional" projects and studies. I wouldn't want to make it part of my daily or even weekly process. I'm trying to avoid spending too much time on the imports and exports otherwise I won't have time for other work and the actual handicapping.

I'm familiar with Excel, but not expert.

[raybo]

Using Excel for some of these things and then importing the updated spreadsheet into a new table sounds like the best approach for "one time" or "occasional" projects and studies. I wouldn't want to make it part of my daily or even weekly process. I'm trying to avoid spending too much time on the imports and exports otherwise I won't have time for other work and the actual handicapping.

I'm familiar with Excel, but not expert.

Of course, we're not talking about using Excel as the front end all the time, but rather for those instances like you are currently working on, where it is much easier, and faster to automate file import and processing, to result in an importable table for your database. So that, rather than having to join multiple tables in order to obtain a really simple set of data (the last 5 speed figures, displayed on a single row, for example). You create that table as a stand alone, in Excel, and simply import it into your db. From there you just create your report from that single table.

[MJC922]

Using Excel for some of these things and then importing the updated spreadsheet into a new table sounds like the best approach for "one time" or "occasional" projects and studies. I wouldn't want to make it part of my daily or even weekly process. I'm trying to avoid spending too much time on the imports and exports otherwise I won't have time for other work and the actual handicapping.

I'm familiar with Excel, but not expert.


I hear you, I think what I'm trying to hammer home (and I'm probably doing a poor job of communicating it) is that certain types of research such as what win% the top speed fig hits globally let's say or in different types of races, or different ages, or a study about spacing between races and how that impacts figures etc, these are studies that really should be done by pulling data out of your db and preparing it especially for that task. And sometimes the preparation alone as you can probably tell can be fairly involved.

It shouldn't be thought of as we have this one source db and that one db should be fully capable of any research we can dream up as a standalone entity. At some point let's say when you do get the previous five races on one line, the thought IMO shouldn't be to eventually get all of that back into the source db and then periodically do a similar type of operation to keep it 'current' weekly. There are certain things in racing that require serious amounts of de-normalization to study and it makes no sense to uselessly clog up the source db with records arranged in that manner.

[MJC922]

Of course, we're not talking about using Excel as the front end all the time, but rather for those instances like you are currently working on, where it is much easier, and faster to automate file import and processing, to result in an importable table for your database. So that, rather than having to join multiple tables in order to obtain a really simple set of data (the last 5 speed figures, displayed on a single row, for example). You create that table as a stand alone, in Excel, and simply import it into your db. From there you just create your report from that single table.


Well stated.

[classhandicapper]

I'm making good progress writing queries I can use for information retrieval (some really neat stuff I always wished I had). I also started a few for testing various handicapping ideas. The latter is a bit more difficult because it sometimes involves creating personal tables with data, doing calculations, setting up rules etc... but I'm inching forward. One problem I run into from time to time is when I change a query. If it is used by other queries it sometimes corrupts the others (not sure why). That forced me to redo it.

Just loading the PPs and Charts I am interested in has become a breeze.

Thanks again.

[Exotic1]

I'm making good progress writing queries I can use for information retrieval (some really neat stuff I always wished I had). I also started a few for testing various handicapping ideas. The latter is a bit more difficult because it sometimes involves creating personal tables with data, doing calculations, setting up rules etc... but I'm inching forward. One problem I run into from time to time is when I change a query. If it is used by other queries it sometimes corrupts the others (not sure why). That forced me to redo it.

Just loading the PPs and Charts I am interested in has become a breeze.

Thanks again.

If Query_2 references dynamic field names of Query_1 any change to Query_1 will impact Query_2. If you want to add field names to Query_1 instead of changing existing field names, that shouldn't have an impact on the other Queries referencing Query_1. Not sure if this is what you meant.

If you want to test dependencies, go to "Database Tools" and click on "Object Dependencies". I don't use that feature, so I have no insight on that.

[sjk]

Copy the query and change the copy. Leave the original as is

[Exotic1]

Copy the query and change the copy. Leave the original as is

That's a creative technique. :ThmbUp:

However, if the newer version will be the active Query going forward where updates and improvements are made only to the newer version, then at some point a reconciliation needs to be done. I think.

[raybo]

That's a creative technique. :ThmbUp:

However, if the newer version will be the active Query going forward where updates and improvements are made only to the newer version, then at some point a reconciliation needs to be done. I think.

These queries appear to be for testing purposes only, so just copy the base query, instead of writing a new query from scratch, and make the changes, for the new testing method, in the new query, leaving the base query as it is, so you can copy it and make changes for other testing methods or ideas in the future.

My thinking is, once you have a useful query created, and are pretty sure you will be using it again in the future, keep it as it is, never alter it, just copy it and create newer versions from it. That way you always have the original query for the future.

[Exotic1]

These queries appear to be for testing purposes only, so just copy the base query, instead of writing a new query from scratch, and make the changes, for the new testing method, in the new query, leaving the base query as it is, so you can copy it and make changes for other testing methods or ideas in the future.

My thinking is, once you have a useful query created, and are pretty sure you will be using it again in the future, keep it as it is, never alter it, just copy it and create newer versions from it. That way you always have the original query for the future.

Good points.

I thought Class wanted to modify and go "live" with a Query_1 version that changed the original field names. However, if the updated query is only for testing, then keep the original and rename the test version to something else.

I changed my post b/c I was confused reading it which is never a good sign.

[cj]

Copy the query and change the copy. Leave the original as is

No doubt, that should be taught in computer programming 101 too!

[classhandicapper]

Good points.

I thought Class wanted to modify and go "live" with a Query_1 version that changed the original field names. However, if the updated query is only for testing, then keep the original and rename the test version to something else.

I changed my post b/c I was confused reading it which is never a good sign.


I do want to go live with the new ones. That's the problem.

For example, I created a query that did something for "all records" in a table and it worked fine. Several other queries use that query output and it all works great.

My knowledge increased. I realized I wanted to add a few more fields and have the ability to select only specific race types by prompt (only a certain class, distance, or whatever...). I made the changes and the new query worked fine, but the subsequent queries that use it (that worked fine previously) started crapping out. I haven't been able to determine why. I redid them and they worked fine????

I have another chronic issue where I create a query that selects specific types of races by prompt. Then a series of queries use that information.

It works fine, but then for some reason the next day the same prompts start coming up twice instead of once. So I have to enter everything twice to get what I want. Then sometimes the problem magically disappears only to return again. That happened to me tonight. When I left off last night a series of queries I just put together were working fine. Today I wanted to remind myself where I left off so I ran the series and I got a double prompt. I said "screw it" and kept making updates to finish my work. Then I tested it and it was still doing the same thing even though the results looked good. I logged off for awhile, came back, and suddenly the double prompt went away. :rolleyes:

Note: Doing copies of everything in the stream would be burdensome. It's the not the ones I am changing that crap out. It's the ones using the new version that suddenly won't work even though nothing has changed to them.

[MJC922]

It sounds like you have a parameter query which is nested (i.e. a sub-query) and maybe over time this results in some strange behavior. Do you have compact on close enabled? Maybe after that operation runs it clears up. I don't recall having a need to nest parameter queries like this before so I can't really say from experience that I've seen what you've described.

The other thing you might consider is creating a form to type in your criteria and have the queries reference text fields on the form as opposed to prompting you for the criteria during execution. It seems like the direction you're taking with the db might benefit from a form-type of UI. I know that gets a bit more involved but might be worth investigating at some point.

[classhandicapper]

The other thing you might consider is creating a form to type in your criteria and have the queries reference text fields on the form as opposed to prompting you for the criteria during execution. It seems like the direction you're taking with the db might benefit from a form-type of UI. I know that gets a bit more involved but might be worth investigating at some point.

I didn't get to that chapter yet ;) , but I can see the point you are making.

I'm getting more enthusiastic about this project. Right now my list of ideas is growing faster than my ability to actually create the queries and new tables I need to get the answers. If I can keep this momentum up, I'm finally going to have the tool I always dreamed about having. I'm not sure what took me so long to pursue this because I've been talking about it for years. I hate to admit it, but it may be because I'm so cheap. :lol: I get all the data for free now. Previously, I had a tough time justifying both the money and effort for an unknown outcome.

[MJC922]

I'm delighted to hear about your progress and hope it continues into the new year. Best to you. :ThmbUp:

[classhandicapper]

Here's an interesting design issue.

Let's say I create several "class" tables with personal data. The "key" to each table is dependent on whether it's a Stakes race, Allowance race, Claiming race etc.... Sometimes I want to use Grade and Purse to get the data I want. Sometimes I want to use Track and Purse. Sometimes I want to use Claiming Price and Track. Sometimes I might be interested Purse, Track, and a Statebred indicator etc...

I'll know which table I need to get the information from based on what's in the PP line for a horse, but how can I control which table to look up.

The only easy thing I can think of is running separate queries for each race type and then somehow joining them all into one.

Here's another unrelated couple.

Let's say "Track" is a key to some table.

Is there any way to code "If track is on the table use the value in the table otherwise use "X".

[sjk]

Double click on the join line between the tables and you can select a one sided join which returns all values in the table on the left and null where there is no entry on the right. Use a formula such as iif([x] is null,[y],[x]).

[classhandicapper]

Double click on the join line between the tables and you can select a one sided join which returns all values in the table on the left and null where there is no entry on the right. Use a formula such as iif([x] is null,[y],[x]).

Thanks.

I've been experimenting with the join type already. I got it to work on a plain vanilla situation, but it didn't seem work when I added a wrinkle.

I set up a table with a purse range (min and max). I joined the tables and used this.

WHERE ((([Lookup B].Purse) Between [Class Info].[Purse Min] And [Class Info].[Purse Max]));

When I did that, the one sided join stopped working properly. It works fine as long as there's a value in the table that meets the criteria, otherwise it will skip the record. I could easily have screwed something else up, but I set it aside for the time being. Maybe I can revisit it.

[classhandicapper]

I have several columns of output from a query. I want to average each.

I used the Total feature with "average". It gives me the result, but it takes the answer out to a ridiculous number of decimal positions. All I need is for the average to be rounded to 1 or 2 decimal positions. As is, it displays XXXXXX unless I make the column wide enough to actually support this overkill of an answer (which in turn makes it impossible to view all the columns at once).

I've used "round" on various fields with calculations, but I don't see any way to use Total with round to display the simpler values I want from those columns of data.

Anything?

Better technique?

[headhawg]

Can't you just format the cell to Number and set the decimal places to the desired amount?

[sjk]

You can right click on the field and define format properties. You want fixed format.

[classhandicapper]

You can right click on the field and define format properties. You want fixed format.

That changed the width of the field, but it doesn't change the fact that the value in the total field (the average for that column) is being displayed as "######" because it's so many decimal positions. To see the actual value I have to expand the width of the field. Then I see a result like
1.84838709677419 when all I want is 1.85 which fits beautifully into a narrow column.

[classhandicapper]

Can't you just format the cell to Number and set the decimal places to the desired amount?

I have yet to figure out what "decimal places" in the field definition does. When I've changed the value, I saw no difference in what was displayed when I used those fields in a query.

In fact, that's where this whole issue is coming from.

Certain fields in my table are stored as (for example) 100 instead of 1.00 or 150 instead 1.50 etc.... So when I display them, instead of displaying the actual field value I display 100 / 100 = 1; 150 / 100 = 1.50 etc...

Then at the end of the column I ask for the average using Total and it gives me the correct value but in a bad format. I don't seem to have any control over the Total row in terms of editing the format of the results (or at least I haven't found it yet).

[raybo]

I have yet to figure out what "decimal places" in the field definition does. When I've changed the value, I saw no difference in what was displayed when I used those fields in a query.

In fact, that's where this whole issue is coming from.

Certain fields in my table are stored as (for example) 100 instead of 1.00 or 150 instead 1.50 etc.... So when I display them, instead of displaying the actual field value I display 100 / 100 = 1; 150 / 100 = 1.50 etc...

Then at the end of the column I ask for the average using Total and it gives me the correct value but in a bad format. I don't seem to have any control over the Total row in terms of editing the format of the results (or at least I haven't found it yet).

Why would you use Total and Average? Why not just average? Shows how much I know about database functions. You should, as sjk suggested, be able to right click the field/cell and select the "number" format as "fixed" and specify how many decimal points you want to round to.

Or:

Set the number of decimal places to display in a field or control (MDB)

NOTE The information in this topic applies only to a Microsoft Access database (.mdb).

The DecimalPlaces property provides a default setting, Auto, and the option to specify from 0 to 15 decimal places. When set to Auto, fields with Format property settings of Currency, Fixed, Standard, Percent and Scientific display two decimal places. The DecimalPlaces property has no effect unless the field or control has a Format property setting.


Set the number of decimal places to display for a field in table Design view

Open a table in Design view.
In the upper portion of the window, click the field you want to define decimal places for.
In the lower portion of the window, click the arrow next to the DecimalPlaces property box, and then click the desired number of decimal places.


Set the number of decimal places to display for a field in query Design view

Open a query in Design view.
In the query design grid, place the insertion point in the column for the field you want to change. You can place the insertion point in any row for that field.
Click Properties Button image on the Query Design toolbar to open the property sheet for that field.
On the General tab, click the arrow next to the DecimalPlaces property box, and then click the desired number of decimal places.

[classhandicapper]

You can right click on the field and define format properties. You want fixed format.

Disregard my previous note.

I just found what you were talking about. Thanks.

:jump:

[classhandicapper]

Why would you use Total and Average? Why not just average? Shows how much I know about database functions. You should, as sjk suggested, be able to right click the field/cell and select the "number" format as "fixed" and specify how many decimal points you want to round to.

Or:

I'm sure part of it is still lack of experience on my part, but some aspects of the product are kind of clumsy to work with relative to other programming languages I've worked with or manipulating data with Excel.

The Total option gives you the ability to get the Sum, Count, Average, Maximum, Minimum, Standard Deviation, and Variation of a column of data.