Database Handicapping Software- JCapper

JCapper Message Board

          JCapper 101
                      -- Trip Notes Report

Home Register
Log In
By Trip Notes Report
NYMike
5/14/2021
7:53:30 PM		Is it possible to run a report that would show all of the horses running today that I have entered trip notes for and their note?

Thanks,

Mike

Reply
jeff
5/15/2021
11:47:41 AM		I'm thinking the sql expression posted below will get you pretty close what you have in mind.

Instructions:

  1. Load today's .JCP Files and run a SQL Calc Races to populate the StartersToday table with data for today's starters.


  2. Bring up the JXC File Exports Module, check the Use Custom SQL Expression box, and paste in the following sql expression:

    SELECT StartersToday.[DATE], 
           StartersToday.TRACK, 
           StartersToday.RACE,
           StartersToday.SADDLECLOTH,
           StartersToday.HORSENAME, 

           TripNotes.[DATE], 
           TripNotes.TRACKCODE, 
           TripNotes.RACENUMBER, 
           TripNotes.HORSENAME, 
           TripNotes.SHORTDESCRIPTION, 
           TripNotes.BEATENLENGTHSADJUST,
           TripNotes.NOTES FROM TripNotes

           INNER JOIN STARTERSTODAY ON TripNotes.HORSENAME = STARTERSTODAY.HORSENAME

           WHERE STARTERSTODAY.[DATE] = #05-15-2021#
           AND INSTR('CATG-SATG', TripNotes.SHORTDESCRIPTION) > 0

           ORDER BY STARTERSTODAY.TRACK ASC, STARTERSTODAY.RACE ASC, STARTERSTODAY.RAILPOSITION ASC, TRIPNOTES.[DATE] DESC


  3. Modify the above sql expression as needed.

    For example: Replace the part of the sql expression that references today's date "WHERE STARTERSTODAY.[DATE] = #05-15-2021#" (without the quotes) with the race date matching (today's) data files that you loaded into JCapper with the DFM Card Loader.

    Also, replace my ShortDescription codes in this part of the sql expression "AND INSTR('CATG-SATG', TripNotes.SHORTDESCRIPTION) > 0" (without the quotes) with your own ShortDescription codes.


  4. Key an output filename into the Output File Name box. For example: "c:\jcapper\exe\_tripnotes-05152021.csv" (without the quotes.)


  5. Click the Create Export button to run the Export.


That's it!

At this point, if you open your .csv export file from step 4 (above) in Excel or even a text editor like Notepad:

You should be able to see it contains all TripNotes for the ShortDescription codes referenced in the sql expression -- with the data sorted by this part of the sql expression "ORDER BY STARTERSTODAY.TRACK ASC, STARTERSTODAY.RACE ASC, STARTERSTODAY.RAILPOSITION ASC, TRIPNOTES.[DATE] DESC" (without the quotes) -- for all starters in your StartersToday table where you took the time to create TripNotes.

Link to screenshot showing the top of a .csv export file run at my end for today's races open in Excel: here.

Link to screenshot showing JCapper PPs with my TripNotes for #5 FLASHNDYNAMITE in today's BEL 05-15-2021 R1: here.

Note that the .csv export file contains the same info that you see in the PPs -- but ONLY FOR THOSE HORSES in the StartersToday table that you took the time to create TripNotes for.


-jp

.



~Edited by: jeff  on:  5/15/2021  at:  11:47:41 AM~

Reply
NYMike
5/15/2021
9:05:00 PM		Jeff,
This is perfect. Thanks!

Mike

Reply
Reply
Copyright © 2018 JCapper Software              back to the JCapper Message Board              www.JCapper.com