CHEAT SHEET - JCapper in SQL Mode --------------------------------------------------------- Operating Instructions: Save the download package to your c:\JCapperBuild folder and run the Extractor/Installer. The download comes with a new JCapper2.mdb file. The Factors Table in that file is already populated with 35 default factors. Check the box on the Installer the very FIRST TIME you install a SQL Enabled copy of JCapper (available 9/20/2009 or later) to make the Installer give you a new JCapper2.mdb file. On subsequent program updates - you have the option to leave the box on the Installer unchecked... HOWEVER, from time to time expect me to add new tables and data fields to the JCapper2.mdb file. Whenever I do that, expect me to post about it in the JCapper Platinum Program Downloads thread on the JCapper Message Board. Check the box on the Installer each time you want the Installer to give you a new JCapper2.mdb file. From there use the JCapper2 Import Module to import data from your old JCapper2.mdb file into your new file as shown in the video. You can find all of the SQL Mode videos on the JCapper 101 Audio and Video page. There's an index near the top. Click the index item labeled Operating JCapper in SQL Mode. You'll find the launch button for the JCapper2 Import Module on the User Sys Defs Screen. About the Interface: * The download comes with 35 default SQL-F Factors pre-loaded into 35 slot numbers. I recommend you use these 35 default factors and slot numbers until you become familiar with the way that the interface works. * After you've worked with the interface for a while THEN use the Factor Set Up Tool to customize your 35 SQL-F factors and the customizable SQL Mode 48 factor HTML Report. * Whenever you rearrange the 35 SQL-F factors (or just swap out one factor for another) you need to run a StarterHistory Table Data Window Export (Clear First) to repopulate your StarterHistory Table. Otherwise, the SQL-F Factor data sitting in your table will be in the original (old) slot numbers and not your new slot numbers - VERY IMPORTANT. * Whenever you rearrange the 48 factor slots for your Customizable HTML Report you also need to (at a minimum) edit the Column Headings for your Customizeable HTML Report. Otherwise, your column headers will describe the original (old) factors sitting in each slot number and not your revisions. --------------------------------------------------------- Getting Data Into the StarterHistory Table: There are two ways to populate the starterhistory table: 1. SQL Mode Build Database routines. 2. Data Window Export. Use one method or the other. Do not try and use both on a regular basis. The second method was intended as a backup way to populate the starterhistory table in the event something goes wrong with a build database routine. SQL Mode Build Database routines: specifically: Build Database routine run in mode 3, 4, or 5. Note: This is probably the easiest way to operate. Best practices: 1. Run the first build database routine on any folder in mode 3. This clears the starterhistory table and populates it with data from the folder you are building. 2. Any time you start a new folder, run the first build on that folder in mode 4. This does not clear the starterhistory table - instead, data from the folder you are building is appended to the starterhistory table. 3. Any time you add new data and results files to a folder with a previous build on it, run the next database build on that folder using mode 5. This does not clear the starterhistory table - instead, data from the newly added data and results files on that folder is appended to the starterhistory table. Data Window Export 1. Put the Data Window in playlist file mode and point it at a target folder. 2. Click Menu, select Exports, and then select either: a. StarterHistory Table Export Clear First (This option clears the starterhistory table.) -or- b. StarterHistory Table Export Append (This option does NOT clear the starterhistory table.) Both options toggle an export "on." About toggling an export "on." -- The most common mistake is thinking that just because an export is toggled "on" that the table is being populated. Wrong. Toggling an export "on" means that the next Data Window query that you run is going to populate the starterhistory table using data from the starters returned by the query being run. That 's right, after toggling an export "on" you have to run a Data Window query to populate the table. 3. Click the ALL or UDM button and run a Data Window query. --------------------------------------------------------- BASIC SQL EXPRESSIONS Examples of SQL Expressions that you can use in UDMs: An "ALL Button" SQL Expression: SELECT * FROM StarterHistory (The above SQL Expression is identical to an ALL Button Data Window query and takes a while to execute.) A simple "UDM-like" SQL Expression: SELECT * FROM StarterHistory WHERE JPR >= 80 AND rankODDS = 1 (The above SQL Expression returns post time favorites that win in excess of 40 percent of the time.) Adding further conditionals: Track code: 1. AND TRACK = 'KEE' 2. AND INSTR('KEE-TPX-APX-HOL-DMR-SAX-OSA-WOX-GGX-PID', TRACK) <> 0 Surface: 1. AND surface = 'D' 2. AND INSTR('D-d', surface) <> 0 3. AND (surface = 'T' OR surface = 't') Synthetic Dirt Surfaces: AND INSTR('KEE-TPX-APX-HOL-DMR-SAX-OSA-WOX-GGX-PID', TRACK) <> 0 AND SURFACE = 'D' Natural Dirt Surfaces: AND INSTR('KEE-TPX-APX-HOL-DMR-SAX-OSA-WOX-GGX-PID', TRACK) = 0 AND SURFACE = 'D' Track Condition: 1. AND tkcond = 'SY' 2. AND INSTR('SY-MY', tkcond) <> 0 3. AND tkcond = 'SY' OR tkcond = 'MY') Distance: Sprints: AND dist < 1760 Routes: AND dist >= 1760 Five furlongs: AND dist = 1100 Class Descriptor: 1. AND classdescriptor = 'G' 2. AND INSTR('M-S', classdescriptor) <> 0 Rider: 1. AND rider = 'MENA MIGUEL' 2. AND INSTR('MENA MIGUEL-MARTINEZ SETH', rider) <> 0 Trainer: 1. AND trainer = 'PLETCHER TODD' 2. AND INSTR('PLETCHER TODD-MIYADI STEVEN', trainer) <> 0 OR Conditional: AND (rankJPR = 1 OR rankF14 = 1) Recency: AND racedays <= 45 Morning Line: AND MLine >= 4.5 Sire: 1. AND sire = 'A.P. INDY' 2. AND INSTR('A.P. INDY-SMART STRIKE', sire) <> 0 Numeric Factors - Factor Rank: 1. AND rankF22 = 1 2. AND rankF33 <= 3 Factor Numeric Value: 1. AND valF22 >= 70 2. AND valF33 < 95 Factor Gap: 1. AND gapF22 >= -12 2. AND gapF33 < 10 DATES: There are three date fields in the table: field data name format ------ ------- YEAR integer MONTH integer DATE short date (mo/dy/year) Suppose you want to make the Data Window return results for the months of June 2009, July 2009, and August 2009... To do that, add something like the following to your SQL Expression: AND [YEAR] = 2009 AND [MONTH] >= 6 AND [MONTH] <= 8 Suppose you want to make the Data Window return results for a single race card like Keeneland 10/11/2009... To do that, add something like the following to your SQL Expression: AND TRACK ='KEE' AND [YEAR] = 2009 AND [MONTH] = 10 AND DAY([DATE]) = 11 A little explanation about the DAY([DATE]) = 11 part... DATE is a field name in the table schema. It contains the race date in short date format like this: mo/dy/year In the above sql expression DAY is a function. The way that I used it tells the Data Window to first evaluate records in the DATE field... and pull only those records from the table where the DAY part (dy) is equal to 11. --------------------------------------------------------- SQL EXRESSIONS (A LITTLE MORE ADVANCED) DATES Wrapping the Date field using square brackets -- DATE is the name of the date field in the StarterHistory Table. DATE is also a valid command in the sql programming language. For that reason, whenever you use the DATE field, you should "wrap it" inside of square bracket characters to tell the database driver that you are referring to the DATE field in the table and not the DATE command in the sql programming language. Example: AND [DATE] >= #06-01-2012# AND [DATE] <= #06-30-2012# The above two lines correctly show how to "wrap" the DATE field using square brackets. Wrapping date text using # characters -- Note that in the above two lines, the date text is also "wrapped" using # characters. The # character is used in both VB and the sql programming language to indicate to the database driver that the text found between the # characters is a date. For best practice in your sql expressions, always "wrap" your date text inside of # characters instead of single or double quote characters like those used to indicate string text. Example: AND [DATE] >= #06-01-2012# AND [DATE] <= #06-30-2012# The above two lines correctly show how to "wrap" DATE text using # characters and cause the expression to find table records for the month of June, 2012. Using Long Numbers Instead of date text -- Some of you will find this next trick useful (but wrapping date text inside of # characters is really all you need to know because it will get the job done.) A little known sql trick is to refer to dates using numbers that refer to specific dates on a calendar. The SQL programming language assigns a number to each date on the calendar. The very first day on the calendar is 1. The day after that is 2... the day after: 3, etc. Under this numbering schema, Fri Nov 4, 2011 is day number 40851 on the calendar. Sat Nov 5, 2011 is day number 40852 on the calendar. To create a sql expression that resturns starters for a range of dates, for example Fri Nov 4, 2011 through Sat Nov 5, 2011: AND [DATE] BETWEEN 40851 AND 40852 The above line refers to numbered dates using the between command. The following line accomplishes the same thing: AND [DATE] >= 40851 AND [DATE] <= 40852 The above two lines are a cleaner way than something like this which returns the same dates: AND [YEAR] = 2011 AND [MONTH] = 11 AND DAY([MONTH]) >= 4 AND DAY([MONTH]) <= 5 Hint: Not sure of the number for an individual date? Display individual plays in the Data Window starting with the number for a known date - and count from there. 40848 is November 01, 2011. The following sql expression displays everything in the table starting with that date: AND DATE >= 40848 Table of Relevant Date Numbers 40544 Jan 01 2011 40575 Feb 01 2011 40603 Mar 01 2011 40634 Apr 01 2011 40664 May 01 2011 40695 Jun 01 2011 40725 Jul 01 2011 40756 Aug 01 2011 40787 Sep 01 2011 40817 Oct 01 2011 40848 Nov 01 2011 40878 Dec 01 2011 SQUARE BRACKETS The following starterhistory table field names are also part of the sql programming language: Date, Month, Year Wrap those field names in square brackets whenever you use them in sql expressions. For example: AND [YEAR] = 2011 AND [MONTH] = 11 AND DAY([MONTH]) >= 4 AND DAY([MONTH]) <= 5 Note: When you Wrap field names in square brackets, you are telling the (Jet) Database Driver that the characters inside of the brackets refer to a field name in the table and not a sql programming command. TRACK Codes Single track code: The following sql expression flags starters at Gulfstream: AND TRACK = 'GPX' The following sql expression flags starters whose prior race was at Gulfstream: AND TRACKLAST = 'GPX' Multiple Track Codes: The following sql expression flags starters at either Gulfstream, Belmont, or Saratoga: AND INSTR('GPX-BEL-SAR', TRACK) > 0 The following sql expression flags starters whose prior race was at either Gulfstream, Belmont, or Saratoga: AND INSTR('GPX-BEL-SAR', TRACKLAST) > 0 Avoiding Specific Track Codes: The following sql expression flags starters at tracks other than Gulfstream, Belmont, or Saratoga: AND INSTR('GPX-BEL-SAR', TRACK) = 0 The following sql expression flags starters whose prior race was at tracks other than Gulfstream, Belmont, or Saratoga: AND INSTR('GPX-BEL-SAR', TRACKLAST) = 0 Meet Specific Sql Expressions: To write a meet specific sql expression, use the 1st and last day of the meet with the track code. The following sql expression flags starters at the 2010-2011 GPX meet: AND TRACK = 'GPX' AND [DATE] >= 40548 and [DATE] <= 40658 Note: Opening day was 1/5/2011 (day number 40548 on the calendar) and closing day was 4/24/2011 (day 40658 on the calendar.) Note that the following (alternate) sql expression accomplishes the same thing: AND TRACK = 'GPX' AND [YEAR] = 2011 AND [MONTH] <= 4 FIRST TIME STARTERS The following sql expression flags horses making their first lifetime start: AND STARTSLIFETIME = 0 The following sql expression avoids horses making their first lifetime start: AND STARTSLIFETIME > 0 Note that adding a constraint for lifetime starts is useful in many situations because field values for first time starters are normally populated as empty. For example, the following sql expression flags the top ranked JPR horse in a race: AND RANKJPR = 1 However, if the entire field is composed of first time starters, that might a meaningless thing to attempt because the speed and pace figs for every horse will be zero. Adding a factor constraint to weed out first time starters changes the effectiveness of said sql expression: AND RANKJPR = 1 AND STARTSLIFETIME > 0 Adding factor handling for first time starters in other situations is required (as will be demonstrated below in the section about meet specific sql expressions.) As a best practive: When in doubt, add separate factor handling for first time starters. --------------------------------------------------------- SHIPPERS: Non Track Specific Shipper Example: The following sql expression flags starters shipping from one track to another without naming specific track codes. AND STARTSLIFETIME > 0 AND NOT TRACK = TRACKLAST Note: A min number has been specified for lifetime starts. I did this because the values in the track and tracklast fields for first time starters will be empty and can prevent the second line in the expression from evaluating correctly (depending on your machine's chipset.) Track Specific Shipper Examples: The following sql expression flags starters racing at GPX not shipping in from another track. AND TRACK = 'GPX' AND TRACKLAST = 'GPX' However, the above sql expression is not meet specific because dates are not being handled. The following sql expression flags starters racing at GPX not shipping in from another track whose last start was run during the current GPX meet: AND TRACK = 'GPX' AND TRACKLAST = 'GPX' AND RACEDAYS >= (40658 - 40548) Note: 40658 is closing day on the calendar and 40548 is opening day. The following sql expression flags starters racing at GPX shipping in from another track. AND STARTSLIFETIME > 0 AND TRACK = 'GPX' AND TRACKLAST <> 'GPX' Note: A min number has been specified for lifetime starts because the value in the tracklast field to avoid first time starters. Just so you know, the value in the tracklast field for first time starters is "" (which would be flagged by TRACKLAST <> 'GPX') --------------------------------------------------------- NATIVE DATA FIELDS vs. USER DEFINED DATA FIELDS in the STARTERHISTORY TABLE Link to the Table Schema Doc: http://www/JCapper.com/HelpDocs/StarterHistoryTableSchema.html The Table Schema Doc, linked to above, provides a list of data field names and data types for the STARTERHISTORY table found in a JCapper2.mdb file. Field names that are named for specific JCapper factors are fields that are "native" to the STARTERHISTORY TABLE. Field names that are named in a generic manner, so that the field name itself referes to an "F" slot number, enable you to write sql expressions to get at the user defined factors in the STARTERHISTORY TABLE. Example: AND TRACK = 'GPX' AND INTSURFACE = 1 AND RANKJPR <= 4 AND RANKF18 = 1 In the above example sql expression, the field names TRACK, INTSURFACE, and RANKJPR are all "native" to the table. "Native" data field names refer to factors/data items that are hard coded into or "native" to the JCapper program. However, the field named RANKF18 is the place in the STARTERHISTORY table where the JCapper program will write "rank" for the factor assigned by the user in the SQL Factor Setup Wizard to slot #F18. The factor assigned to slot #F18 in the default SQL Factor Setup is EarlyConsensus. However, all 35 of the F Slot numbers are user defined and can be edited by the user in the SQL Factor Setup Wizard. (RANK18 can mean different things to different users depending on what factor is assigned to that slot number in the SQL Factor Setup Wizard.) Link to a web tutorial for Using the SQL Factor Setup Wizard to create and persist entries that define the sql factor setup and customizable sql mode html report layout: http://www.jcapper.com/Article_AddJRating.html Note: Clicking the Display Factors button on the Data Window's SQL Expression Tool will cause the Data Window to display a list of the 35 F Slot Numbers and the user defined factors assigned to each for the JCapper2.mdb file that the Data Window is currently connected to. (Clicking the button a second time will cause the Data Window to revert back to displaying query results.) --------------------------------------------------------- DELETING TABLE RECORDS USING THE DATA WINDOW So far, all of the sql expressions presented show examples of how to make the Data Window read and display data from the StarterHistory table. You can also use SQL Expressions to make the Data Window DELETE records from the table, Example: DELETE * FROM STARTERHISTORY WHERE [DATE] >= #06-01-2012# AND [DATE] <= #06-30-2012# The above sql expression will cause the Data Window to delete StarterHistory table records for the month of June, 2012 (provided that you answer Yes at the "Delete records/Are You Sure?" prompt after hitting the Execute button.) Deleting records can be useful when you need to remove duplicates accidentally introduced into the table, Note that after deleting a significant number of records from the table, it is always a good idea to run a Compact & Repair on the Jcapper2.mdb file. Also note that after deleting records from the table, you can repopulate the table with records for the same time period through Data Window Export and/or Build Database Routines. -jp .