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:   

   1. Run a Data Window StarterHistory Table Export to get data into your StarterHistory Table. Use the Clear 
   First option with the first export. Use the Append option with subsequent exports.

   2. Run a Compact and Repair Database on the JCapper2.mdb file after every export (very important.)

   Repeat steps 1 and 2 as needed until your StarterHistory Table contains the data that you want.



  ---------------------------------------------------------


   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.



  ---------------------------------------------------------


   -jp
   .