|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | StarterHistory Table Year End for Prob Expressions | |
| jeff 12/6/2016 8:42:57 AM | Rules/Best Practices for StarterHistory Tables when using Prob Expressions in JCapper: Rule #1. Never ever allow a JCapper2.mdb file to break/breach Microsoft's 2.0 gigabyte file size barrier. When you do allow this to happen: Your JCapper2.mdb file is about 99% likely to become corrupted and/or unusable. Rule #2. Always Maintain Regular Backups of your JCapper2.mdb File. You should be doing this religiously throughout the year. That said - immediately after you run the final build database routine for a calendar year - right-click the c:\JCapper\ExeJCapper2.mdb file and select COPY. From there, right click inside the folder where you want the backup to go - and select PASTE. This will cause Windows to paste a copy of the JCapper2.mdb file onto that folder - and you now have a good backup of the file. Hint: Don't limit your .mdb file backups to other folders on your main hard drive. Make sure you have backups of your .mdb files on media other than your hard drive - such as thumb drives, CDs, and external hard drives. I also recommend off site backups using a cloud based service too. Hint: Don't limit your .mdb file backups to the c:\JCapper\ExeJCapper2.mdb file. You should also be making regular backups of the following files: c:\2004\JCapper.mdb c:\JCapper\ExeJCapperSDK.mdb Rule #3. Make sure the StarterHistory Table in your current JCapper2.mdb file contains the data needed to execute your Prob Expressions properly. Getting this right requires that you understand some relevant background info. Every time you click the Calc Races and/or Build Database buttons - your StarterHistory table must contain the requisite data so that all of the Prob Expressions you are using can execute fully. If your StarterHistory Table does not contain the needed data - the query results returned by your Prob Expressions are going to be incorrect. (Or at the very least - they going to be incomplete or otherwise inaccurate.) Consider: a. If you have a factor rank-track-intsurface-dist specific Prob Expression designed to score the most recent n starters for a specific situation - your StarterHistory table must contain the needed data so that the interface can execute that Prob Expression each time you click the Calc Races and/or Build Database buttons. b. If you have a rider-track-intsurface specific Prob Expression for the last n mounts - your StarterHistory table must contain the needed data so that the interface can execute that type of prob Expression as well. c. Same thing applies for all other Prob Expression types. Your StarterHistory table must contain the needed data so that your Prob Expressions can execute. In the past (before I was using Prob Expressions) I could just make a year end backup of the JCapper2.mdb file - and from there start fresh by running the very first build on the Q1 folder for the new year using Mode 3. But now that I'm using Prob Expressions - Do I really want to do that? No. If I do, I'll be breaking Rule #3. (There won't be any data in my StarterHistory table at the start of the new year and my Prob Expressions are going to - initially at least - return zero values.) But if I start the new year off with a fully loaded StarterHistory table - and totally ignore the ever growing file size issue - I'm pretty much guaranteeing that my JCapper2.mdb file is going to break Rule #1 at some point. So -- With those 3 rules in mind: Q. What is the correct way to handle a StarterHistory Table inside of a JCapper2.mdb file when it comes to a calendar year end if I am using Prob Expressions? A. Here is how I handle a StarterHistory calendar year end: 1. Immediately after running the final build database routine for the just ended calendar year: Make a good backup copy of the c:\JCapper\Exe\JCapper2.mdb file. 2. Double check that I do in fact have a good backup copy of the c:\JCapper\Exe\JCapper2.mdb file for the year just ended. 3. Verify step 2 (above) and then (immediately) bring up the Data Window and delete data from the StarterHistory table that is not needed to execute my Prob Expressions. Real Life Example: Right now as I type this (on Dec 5th, 2016) the StarterHistory table in my c:\JCapper\Exe\JCapper2.mdb file contains data from Jan 01, 2015 current through yesterday Dec 04, 2016. The current size of that file is 1.30 gigabytes. Based on past experience I fully anticipate being able to use the StarterHistory table in that same file for all remaining calendar year data. Sometime on or about Jan 3rd or 4th 2017 -- I expect to have acquired all of the December 2016 chart files that are or will be available -- and I will have run the final Mode 5 Build Database routine for calendar year 2016. Immediately after that I will complete step 1 in this section (above) by making a good backup of my current c:\JCapper\Exe\JCapper2.mdb file. And when I do this, that backup will contain data spanning two full years: Jan 01, 2015 through Dec 31, 2016. At that point step 1 (above) is complete -- and from there I will immediately perform step 2 and verify that I do in fact have (several) good backup copies of the c:\JCapper\Exe\JCapper2.mdb file - on thumb drives, CDs, external hard drives as well as off site on the cloud. Immediately after that I will perform step 3 in this section (above) by: a. Launching the Data Window. b. Executing the sql expression in the following linked to text file to delete 2015 data from my current 2017 StarterHistory table: http://www.JCapper.com/messageboard/reports/YearEndSQL2016.txt Immediately after that I will use the Data Window Menu to run a compact and repair. At that point -- sometime on or about Jan 3rd or 4th 2017 -- my current c:\JCapper\Exe\JCapper2.mdb file will be approximately half its current size -- and it will contain data for calendar year 2016 only. This should be more than more than ample data to allow all of the Prob Expressions I am using to execute properly going forward in time for calendar year 2017. This will also enable me to operate without breaking any of the three rules that I mentioned in this post. -jp . ~Edited by: jeff on: 12/6/2016 at: 8:42:57 AM~
| |
| parshooter 1/2/2017 1:04:14 PM | DELETE * FROM StarterHistory WHERE [date] >= #01-01-2015 and [date] <= #12-01-2015# I am finding this link not working in data window. Am using SQL mode. Please advise if you can identify problem. Thank you Wayne
| |
| jeff 1/2/2017 4:35:55 PM | Try the link again: http://www.jcapper.com/messageboard/reports/YearEndSQL2016.txt The original was missing the # character after the first date. I just now corrected it - and uploaded a new text file to the server. -jp .
| |
| parshooter 1/4/2017 2:07:08 AM | Thank you Jeff. Noticed the difference and got SQL to run for JCapper2.mdb file.
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |