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
    
    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:
    
    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 TRACK = 'GPX'
    AND TRACKLAST <> 'GPX'
    AND STARTSLIFETIME > 0
    
    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')








   -jp
   .