HTR2 Access Manual
Contents
1. Next is the table name that the macro is to use for the data The file name is the complete path and name where the macro can find the text file In our case c htr all_hx4 txt Finally we want to indicate that the import file has field names The macros work fine as long as your text files are in the htr directory What if you have data in a different directory and you have built an export file in that directory The easiest way to get your data into access is to move the text file into the HTR directory and then run your macro Importing data with the Import Wizard What if you want to bring data into your dB for one of the exports like hx1 hx2 or hx6 The htr_templates mdb does not have pre built tables for these exports The following is a step by step process to accomplish this We will use all_hx2 txt as an example In HTR2 create an all_HX2 txt export file Open your database and click on Get External Data then Import data The first thing the wizard needs to know is what files do you wish to import In this example we will import the all_hx2 txt file that you created in the HTR folder Where the box says Look In change this to the C HTR directory You must also change the Files of De to Text Files so the uae lists all your text files in this directory Choose the file listed in the z a EE El E H Mil he2 tet Al _hx4 txt All he tet Advanced Look in Find Files that match these search cri2. February 2004 It was done to update the material to reflect the HTR2 windows based HTR export functions and to add additional Access instructional material Slight revision in Aug 2010 to clear up database name used The database name is htr_templates mdb Also fixed some links From Don s original manual This booklet is dedicated to Ken Massa the author of the HTR software I would have dedicated this to my wife Sara and 2 sons Jake and Sam but they never gave me a 12 horse However they do receive honorable mention for having to put up with a horseplaying father and husband A compulsion No A labor of love Yes Read apply and enjoy May all your exactas find the big horse on top Don Nadermann Chapter 1 Explaining Access An Access database is a collection of information related to a particular subject or purpose If your database isn t stored on a computer or only parts of it are you may be tracking information from a variety of sources that you must coordinate and organize yourself With Access you can manage all your information from a single database file Within the file your data is divided into separate storage containers called tables You can view add and update table data Retrieve just the data you want using queries and analyze or print data in a specific layout using reports or export query outputs to Excel or Word To store your data you will create one table for each type of information you
3. coming in 4m then this horse also does not meet the double criteria and will not show in the results dynaset Multiple criteria requires the record to meet all the conditions of criteria before it will be displayed You can think of this as an AND statement The horse must have raced in a 6F race AND finished in first place Microsoft Access Query Select Quer M B SRY sans _ xFIN nDIST tTRK nRACE tPGM B 11 You can define multiple criteria but on separate horizontal lines When you do this you are creating an OR statement When applying an OR statement to a query you are allowing the resulting dynaset records to contain either one piece of criteria OR the other In the example above lets query for winners at 6 or less furlongs and place horses for gt 6 furlongs 4 N Field nDIST nRACE IPGM Table ALL H4 ALL H4 ALL Had ALL Hesg ALL Hesg Se eee Sort Show Criteria or Be careful when using an OR statement as you will get a broad range of results as each record needs to satisfy only one piece of the statement in order to be included in the results In the AND statement each record must satisfy ALL pieces of the criteria statement so you get very definite records in your results 12 Using Access allows you to query down to ranges of data as well within the different fields The criteria box can contain statements that help accomplish just that Suppose you want to quickly filter
4. down the horses in every 6 furlong race to just the top 4 FR1 horses from each race In the corresponding criteria boxes you would enter a 6 under the Dist field while under the rF1 field you would enter lt 5 The resulting dynaset would include just the top four horses from each of the 6 furlong races To keep them in order you will notice there is a Sort box on the query design grid The sort function sorts your selections from left to right as the query comes across each sort order As you construct your queries for ease of output it s best to sort first by track ascending then by date descending then by race again ascending This will give you the races in the order they are to be run from each track in alphabetical order 23 n p E ee Field FR Table ALL H4 Sot Ascending Ee O ooo o Show Criteria G aA Or 13 The previous examples are known as Select Queries because all you are doing is selecting records from the designated table One step most HTR users would like to learn in Access is compounding numbers or creating your own figures based on the numbers found in HTR 14 When compounding numbers follow these simple steps a In the first empty criteria box first type in the name you want to appear as the field name followed by a colon b Next in parentheses use the field names you wish to add subtract multiply or divide Always use hard brackets around the field names Example Personal Rat
5. help you design a query Several of the options will invoke query wizards programs that walk you step by step through the process of building a query There are numerous types of queries within Access that allow you to do many interesting things with the data contained in your tables You can use queries to create whole new tables from existing tables or to search for duplicate data or to delete records from a table that contain criteria you decide We will be focusing in on designing a simple selection query We will design it from scratch so you can see how a query is built Follow these steps 1 If you have not already done so click on the Queries Tab at the top of the screen 2 At this screen click on the New button on the right side 3 The dialog box that opens is asking what type of query you wish to open and if you want to use a wizard For this example make sure the Design View is highlighted and click on the OK button 4 The Query Design Grid screen opens and the Show Table dialog box opens as well This is where you choose the tables from which you want to query your data We will be using one table at a time to query from but as you learn more of the program Access allows you to query from multiple tables at a time This is the strength of a relational database You may create relationships between separate tables which in turn then act as one larger table In the Show Tables box you should notice a list of any and all tables co
6. that now resides in the comma delimited text file you just created using HTR2 The computer and Access will do all the grunt work all you have to do is make the proper selections at the proper time For review when the program creates the all_hx4 txt file it is stored in the HTR folder on your hard drive or the folder where the racefiles you exported are located When we are ready to import this data we must look in the HTR folder for the files we wish to import By importing what we are attempting to do is have the program draw all the information out of these files and put them nice neat and orderly into tables Once the data is contained in a table it may then be queried filtered and sorted Calculations may be run on the fields within the table and information may be called up at will by the user Importing data using table templates Rather then building a data base from scratch the easiest way to build a data base is to use the htr_templates mdb database template that is available on the HTR web site To do this go to the HTR web site http www homebased2 com km htr htm and download the htr_templates mdb to the directory on your pc where you want your dB to reside Once this is done open this dB This can be done by running the Access program and selecting the existing database htr_templates You can also use explorer or the my computer function to find the htr_templates mdb file and then double clicking it At some point you may
7. track To bring the data from multiple tables together in a query form or report you define relationships between the tables To find and retrieve just the data that meets conditions you specify including data from multiple tables you create a query A query can also update or delete multiple records at the same time and perform built in or custom calculations on your data To analyze your data or present it a certain way in print you create a report or export the formatted data to Excel or Word HTR2 allows the handicapper to export the data found in HTR into text files These text files are then imported into Access by the Access file import function With just a few easy steps the information is placed into Access tables The tables and the data they hold are the backbone of your Access database Everything you do within Access is dependant on the integrity of the data within your tables If the data in your tables is not accurate or is skewed then the queries analysis and output reports you generate will not be very accurate It is most certainly a case of garbage in garbage out The final product will only be as good as the information being put into the tables Using the HTR2 program you are assured that the data being imported is accurate every time And the best part is that the tables will be self generated after you answer a few simple questions Once you begin to see what can be done with the data in the tables what you do w
8. HTR and Microsoft Access User Manual Index Forward page 2 Chapter 1 Explaining Access page 4 Chapter 2 Using HTR2 Export Functions page 5 Chapter 3 Importing Data Into Access page 7 Chapter 4 Queries page 20 Forward The manual is designed to coincide with the combined use of HTR Handicapping Software and Microsoft Access It centers on using the file exports in the HTR2 program in conjunction with the Microsoft Access Data Base program While not all inclusive the ideas and steps contained in this booklet will help the novice Access users shorten the learning curve associated with using HTR and Access Some of the screen shots of Access objects in this manual where done with Access 97 If you are running Access 2000 or later your version of Access may have a different look but the information in this manual is valid for both 97 and up Many people going to the track these days use some sort of computer program or software to help point a direction towards winners By using HTR2 exports you will be able to glean data from the HTR numbers in ways you may not thought possible A major contribution to this manual was also made by webmaster Rick Rick created the access template data base that can be used by you to start you own dB All of the objects contained in the template dB are covered in this manual Don Nadermann was the creator of this manual and he first made it available in June 2000 This update was prepared by Mike DeRienzo in
9. RACE and tPGM fields These 4 fields when combined and marked as key fields make a unique key for each horse in each and every race in your dB Because they are key fields no duplicates will be permitted This provides an important level of security in your dB since you will be unable to add any duplicate data to a table that has key fields set like this You will also notice in the design that every field of data is assigned a data type The table templates provided by Webmaster Rick have been optimized so that each data type takes up the minimum amount of disk space Now lets take a look under the hood of your input macro Click on the macro button and highlight the Import ALL_HX4 text file and open it in the design mode by clicking on design Your macro design mode should look like this npm PE Transfer Type Import Delimited Import ALL_HA4 text file Macro Specification Name ALL H4 Import Specification Table Name ALL _Hx4 ransferText File Mame CAhtrall Hed ext Has Field Mames Yes HTML Table Name Code Page The first thing you will notice is that this is a transfer text macro This is one of the many macro functions in Access The lower left hand corner of the design screen is the most important part of the macro design The first line gives the type of transfer In our dB we are importing a delimited text file We use an import file spec that is designed for this import We will discuss import file specifications in more detail later
10. ally HTR2 will only export races where you have added the HDW result files The without results option is provided for those that do their handicapping with Access queries in lieu of or in addition to the HTR2 screens You will also need to indicate if you want chart data included in your export There are fields in the HX4 file that are dependent on information found in the HDW charts If you do not include the chart data these fields will be blank If you decide to include chart data you will also need to add the HDW chart files as well as results to your HTR directory All files versus single track files Next you need to indicate if you want to export to a single all file only It is strongly recommended that you select this option Otherwise you will get a separate file for each track The last step 1s to indicate if you want all of the track files in the track window included in the export If you do not select this option then you can manually select the tracks one at a time to add to the export Exporting the data The final step is to click on the Export Now button When you execute the export function of the program the data is put into a file that is then named after the export type and the txt extension is added onto the end of the file For example a HX4 export for all files in the window will be named ALL_HX4 txt An all file for HX1 would be named ALL_HX1 txt etc The export files are placed in the same folder dir
11. ate as it 1s found in the table such as mm dd yyyy This type of query is asking you for specific criteria each time you run the query The parameter query can be used in combination with other query criteria This will help you also when you create reports based on queries Each time you go to run the report the query will first ask you for the date then it will access the table for the records containing that specific date If you add each day s racefiles to the table you can quickly pull the handicapping reports you need each day based on the criteria you established and it s off to the races You can look in the Access help section under the index enter criteria entering and you will find many many examples of query criteria Creating queries from linked tables There are so many different fields in HTR that one access table with its 256 filed limit cannot hold all of them But what if you want to use information in one of the exports with data in one of the other exports Lets say your primary table is the HX4 table but you want to use the AP EP SP fps numbers that are in the HX3 table The way we do this is to join or link the 2 tables in a select query Once these tables are joined you can query on fields from both tables In the design mode of a new query put both the hx4 table and the hx3 table a Query Select Query 25 With your mouse left click on date in hx4 and drag the mouse with the button depressed to date i
12. e HX2 file spec J Field Name ee Number General Lookup Text Field Size Long Integer Text Format Number Decimal Places Auto Number Input Mask Murniber Caption Mumber Default value Murmb validation Rule Validation Text emer Required Mo idi Indexed Mo In the design mode the arrow points the field type and the lower section provides the details for how the field is set 12 The following are fields that the wizard will typically set incorrectly Pay particular attention to them Field wizard change to tDATE tText date time nDIST integer single nMLO integer single xWIN integer currency xPLA integer currency nABLI integer single nABL2 integer single nABLF integer single nAODD integer single Be sure to set the date field to a date and not to text This will then allow you to query and sort in date order The following table will help you set the correct field type and size for your data items Using the correct type will keep your data base size to a minimum and will speed up your searches You can save about 40 of the hard drive space used for a correctly sized data Items Setting Description Text Default Text or combinations of text and numbers Date Time Date and time values for the years 100 through 9999 Currency Currency values and numeric data one to four decimal places Number Byte Stores numbers from 0 to 255 no fractions Integer Stores numbers from 32 768 to 32 767 no fractions Lon
13. e the data for importing To import a file into Access it must be in a format that Access is able to read One of the most common forms is called comma delimited Quite simply this means there is a comma separating each piece of data Access is able to read this data and the program understands that when it comes across a comma the piece of data that follows the comma should be placed into a new column These are neatly plugged into a table and no real manual data entry is needed While this is a very simplistic explanation of a comma delimited file the main problem for us as HTR users is that Ken s numbers and rankings in their original form are not comma delimited and cannot be read by Access Several users had asked Ken if he could produce a converter that would take all the rankings and ratings in HTR and make them available in a comma delimited format Ken complied by creating the original HTR4MSA Program While accomplishing what was originally requested the program did not convert enough of the information to satisfy the people Ken affectionately calls data freaks These are the people who can t get enough of analyzing numbers and ratings We need as much data as can be fed to us Don t spoon feed us shove it down our throats We still won t be able to get enough So Ken went back to the drawing board a couple times and with each update increased the amount of data that would be converted into these comma delimited files In 2000 Ke
14. ectory where the htr files that were used are If you are working in the C HTR directory then your export files will be in this directory If you do a track specific export the track 3 letter code is used in the file name Aqueduct exported becomes aqu_HX4 txt Note there are no dates associated in the file name unlike many of the other files found in the HTR folder This is because this file will continue to grow as you add more and more race files into it It acts as a container for your exported data once that file is created in the HTR folder In other words the next time you export an htr file to HX4 the program searches for the file called all_hx4 txt and adds the new data to it Be very careful that you do not export the same race data multiple times It will not overwrite the data in a file it will just add it to that file The program will duplicate the race dates and your output and results will be inaccurate to say the least It is strongly recommended that once you have created a HTR2 export file and have imported this data into your Access data base that you Delete the export file that you used This will avoid the problem of duplicating data in your access tables Review the above steps and practice them Delete the export files out of the HTR folder and do it again Practice Practice Practice Chapter 3 Importing Data Into Access Now comes one of the easiest and yet maybe a somewhat confusing part of importing the data
15. ed zhtr has been created with sub folders for 1999 through 2003 The user can select one of the sub folders and work with the HTR files that the user put in these folders instead of the files in the HTR folder 3 The HTR2 export screen has quite a few options that need to be set before you export any data ia HTR 2001 PX a Select Track to Export Export Options C HXI PHA SUN Hx2 TAM l Export Races w o Results TUP IHS HZ4 l Exportto an All File Only C HS pp s M Use Chart Data if available C HZ6 23 Data Fields Paceline Mode 5 C PL 0 HX4 Only Export Now C PLA C PL3 J Export All Tracks on List C PL 4 PL 5 f Show FileSpec y Only exports races with user selected lines Available Export Files As of this update to this manual there are 6 export files that are available HX1 through HX6 You need to review the HTR export specifications to determine the ones that are most beneficial for you They are text or pdf files i e hx4 txt The export file specifications can be found at the HTR web site http www homebased2 com km library htm HX4 is the export that is most commonly used and this manual will deal primarily with this file Pace Line Next you need to determine the pace line that you want to use for your export PL5 is the HTR recommended and default pace line Results and Charts Next you need to indicate if you want HTR2 to export races without results Norm
16. esign lou Click and drag the from the table icon to the field box Next click on lolak taz eSa the small down arrow and click on delete query E Select Query i Query2 Select Query indow Help When you run this query no output is shown but the entire contents of your HX4 table will be removed E Make Table Query 1 Update Query Append Query rt Delete Query Criteria or 17 Finally you need to decide how far back you want to go and you must re run the export function of HTR2 to create a new export text file that contains data for the new fields Keep in mind that when you run the export function in HTR2 you must only use only data files from the same year But since access handles multiple years you can add data over multiple years to the same HX4 text file 18 Chapter Four Queries What is a query A query is a method by which you ask Access to show you specific pieces of a table while allowing you to specify exactly which pieces of that table you wish to view Some of the more advanced queries such as create new table append or delete assist the user in changing data within a table automatically Creating Select Queries from single tables To access the query screen you just need to click on the tab at the top of the screen named Queries while in the database view If this is your first time working with queries and you have used the template to build your dB you will notice there are alread
17. g Integer Stores numbers from 2 147 483 648 to 2 147 483 647 Single numbers with decimals Double very large numbers with decimals or numbers in a key field Not needed for HTR When finished be sure to save your table Storage size Up to 255 characters 8 bytes 8 bytes 1 byte 2 bytes 4 bytes 4 bytes 8 bytes 13 Now that the field types are set next we need to set the key fields Microsoft Access ALL H 2 Table File Edit View Insert Tools Window Help Saw se TEGOLE Field ame Data Type nIDY Number ETRE Text tDATE Text nRAC E Mumber nDIST Mumber nSURF Mumber E Mumber I nc Mumber M nsEX Number ne Number nSTB Number nPURS Number FnM Number nPaR Number nFL Number a EPGM Text KHOR Text nPPO Number Holding down the ctrl key select the track date race and program number field as shown highlight all 4 fields at the same time Now click on the key Icon This will create a unique key for each horse in each race and will prevent you from adding duplicate data into your table The final step is to make a macro so that the next time you want to add data to your table you can do it by running the macro instead of going through the multiple steps on the import wizard Click on the macro tab highlight one of the existing macros and then click on file and then click on save as Next click on within the current database and change the name to Import All_HX2
18. he records in the given table and returns to you just those that contain a 6 in their Dist field at AQU If any number other than 6 is contained in a record s Dist field the record will not be displayed in the dynaset This is a means of filtering down to just the data you wish to view A Microsoft Access Query Select Query M H 4RYl s4RaS _ tTRK nRACE tPGM rAP nDIST gt enn 10 As you become comfortable working with queries you can query down to very specific data within your records If you enter multiple criteria under the different field names the resulting dynaset will display only records that contain all of the criteria you requested If there is one piece of criteria that does not match in a record then that record will not be shown in your dynaset A good example would be if you want to see all the winners of 6F races In the Dist criteria box you would put the number 6 because all you want to view are the 6 furlong races in your table In the criteria box under the Fin field name you enter the number 1 because you want to see the winners of the 6 furlong races Field T able Sort Show Criteria or If the record of a horse shows an 8 in the Dist field and a 1 in the Fin field the horse will not show in our results because he does not satisfy our need for BOTH criteria Likewise if a horse s record contains a 6 under 22 the Dist field and a 4 under the Fin field for
19. ing rAP rFR1 nKrat Note the field names as you type them in the brackets must match IDENTICALLY the names as they are spelled on the grid E i SS a aT Field Table ALL H4 ALLA oyyy y Sort Ascending scending O Jooo O Show JTLT a Or If they do not you will receive an error message Don t forget the parentheses Note when using more complicated formulas you must use the proper number of parentheses Here is one a little more difficult Personal Rating nAP 5 nCLA 33 nPER 125 This formula adds 1 2 of the Average Pace No to 1 3 of the class No to 12 5 of the Speed Figure PER When putting formulas together bear in mind the multiplies the divides and well I hope you know what the and the signs do CO 66 One last query to try is called a Parameter Query If you put the following line in the Date criteria box Enter date ENE H l E Field DATE nRACE kooo ooo S o i O Table ALL Hd ALL HYG ALL HX ALL HX re e w MMO mM M M U UOU U Criteria Eter Dte m 24 Every time you run this query it will first ask you for the date before it executes And you will only get data for the date you enter WATE nRACE Enter Parameter Yalue x 4 kai Enter Date b T am Field Table Sort Show Criteria Enter Date or v gt Be sure you use the same format for the d
20. into the last empty field name and begin typing the new field name Your table design should look like the following In this case fields nLiveS through nLay90 were entered manually Be sure to set the data type to the correct type In this example they are all numbers To set the data type use the revised HX4 txt file spec from the HTR web site A Microsoft Access HX4 Tab l General Lookup File Edit View Insert Tools Window eor TET Format Decimal Places Auto Input Mask Capti mE Werkt Bes e eee eee Validation Rule Field Name Validation Text nivel Nurnber derbi No Ld rvel Number a ram Wumber Design view F6 Switch panes F1 Help _ nFPSx Mumber HA Start ie Microsoft Acc Pd nWkSC Number Pd ric Number M I nLives Number M nliveP Number 16 Once your table is changed if you are using a file spec you need to modify your import file spec to recognize the new fields The only way to change an import file spec is to bring in a file and then edit and save the file spec during the import process Using the get external data wizard bring in an export file from htr that has the new data fields Click on the advanced button and then click on the spec button Select the HX4 input specification Scroll down to the end and just like the table changes type in the new fields It should look like the following AL of the fields shown are the new fields that are being added You mu
21. ion in the dynaset the result of your query you change the information in the table the query was run on So be careful not to change the data in the dynaset unless of course that is your intent 8 To return to the Design View click on the little blue triangle in the upper left corner of your screen File Edit view Insert Format Records Tools Window Help x Hi SA smer e S43 _ tTRK nRACE tPGM rAP 21 This gives you the Design View with the design grid again You will notice the triangle disappears when you are in the Design View and is replaced by a button that returns you to the Datasheet view You will probably toggle back and forth between these two views as you change fields you wish to view and start to add criteria for each of these fields 9 One nice thing about queries is that you can choose multiple fields to view while requesting select criteria within each record When you choose criteria as we will demonstrate in just a second you are requesting just to see the records within your table that contain that criteria An example of this would be if you wish to view just 6 furlong races you would put a 6 in the criteria box under the Dist field in the Design Grid E Field nRACE Table ALL H4 ALL He ALL H4 ALL H4 ALL Had er re o Sort a Show T Criteria agu or When you click on the exclamation point to run the query the query searches through all of t
22. ith it becomes almost limitless As you will see once the data is imported into a database it is then easily manipulated into what you want to see One of the greatest strengths of databases is the ability to query the information down to only what you wish to see A query is the act of filtering out data to leave only the information you wish to view or print It helps to make quick comparisons of large groups of data If you wish to view the early pace number for all winners of maiden special sprint races you can quickly view this information by using the proper language in the query object All of this will be covered in detail later in this manual In the next chapter we are going to examine the proper use of HTR2 to prepare the data for importing into an Access database Once it is ready for importing we will do exactly that and then learn how to query to find the information that will hopefully find us more winners at the track Be patient and practice the procedures explained in the different chapters until you become familiar with the steps Before you know it you will be doing all the data processing without a second thought Take your time when working through this material while none of it is real rocket science a new user of Access may find some of the concepts difficult to understand Let s get started with the main course Chapter 2 Using HTR2 Export Functions The first step to fully using the HTR data within Access is to prepar
23. n hx3 a black line will connect the two items they are now joined Repeat this process with track race number and tPGM program number You should now have 4 black lines connecting the two tables When you join data this way only those races with an exact match of date track race and prg will be selected in this query Now that the tables are joined you can use fields from each table in your design l a OT OT a Field tDATE _ tTAK___ nRACE Table ALL Hx ALL Hea ALL Hx4 ALL HS4 ALL Hea Ooo Soo S S o o Gort Show
24. n introduced his widows based program HTR2 This is his latest program and the exports it contains are far superior to his first creation HTR4MSA The following as a step by step guide to preparing the files to import into Access Read through carefully first and then practice In short order it will be very clear It is assumed you have already downloaded and installed the HTR2 program on your hard drive The program can reside in your HTR folder where your racefiles are kept You can put the HTR2 program and the related HTR2 program files in a separate folder if you prefer to keep your program files separate from your data files so that you don t delete a program file by accident 1 Start the program by double clicking on the HTR2 icon on your screen or double clicking on the file name HTR2 exe while in the Windows Explorer 2 When the program opens up click on the button labeled Enter HTR 2001 or Enter HTR2 This brings you to the main HTR2 screen Wed Jan 14 2004 X a 2 sag 2 Using Multiple Folders The default HTR folder or directory is the C HTR directory Whenever you run HTR it looks in this folder for the files to be processed You can keep HTR data in other folders If you have done this you can direct the HTR2 program to use the data in other folders by clicking on the folder icon and picking the folder with your HTR files i Select Folder Working Directory E c Y In the above example a folder nam
25. ntained within this database We need to choose a table from which we want to query data Highlight the table you want to query and chose Add Then click on the Close Button az Query Select Query E Tables E Queries EE Both H ALL_HX3 ALL_HX5 Criteria or 5 The screen is divided into 2 sections The top area shows you the tables from which you will select the fields you want to use or view Remember in a query you can choose just the information on each record that you wish to see You do not need to look at all the fields for each record If you just wish to see the date the horse the class number and the pace number then you may query down to see just those fields The bottom half of this screen is called the design grid This is where you set the certain criteria and parameters of the records you want to view You may resize the two panes by placing your cursor between the upper and lower pane and when the cursor turns into a double arrow click and hold your left mouse button as you drag down the pane divider After resizing the panes you may then resize the table box in the upper pane to see more of the fields contained in that table Place your cursor on the lower right hand corner of the table box and again when it turns into a double arrow click and drag the box into a larger size 6 At this point you need to decide which fields you would like to query down to In other words which select
26. parts of each record do you wish to view To choose these fields double click on each field you wish to see When you double click you will notice that particular field now shows up on your design grid in the lower pane When you double click on the next field you want that second field name is placed in the next available Field Box along the grid Another way to do this same thing is to click and hold on the field that you wish to bring down then drag it over the next open Field Box Release the left mouse button when your cursor is over the Field Box This simple query is going to show the program number of the number 1 rank AP horse at aqu 20 Thee a Field nRACE E O P Table ALL Heid ALL Hrsg ALL Had ALL Hesg n QO Show Criteria aqu 1 or 7 To actually run the query click on the button on the toolbar that looks like an exclamation mark R Microsoft Access Query Select Query File Edit View Insert Query Tools Window Help ERCE Siete zl e xma KK A This runs the query and the result you will see are only those fields you choose in the design grid w Microsoft Access Query Selec M A SQY Be _ tTRK nRACE tPGM rAP gt Rete ee ee The grid that is displayed on your screen is called a dynaset This new table is a temporary table but is dynamically linked back to the table you are running the query on What this means is that if you change the informat
27. st include all of the fields in the export file You will notice that there are 2 fields nWetch and nEEn that are in the Eis conser Voeeresd Field Delimiter gt export but not in the above table C FuedWith Tetude foo canes Tn this example I decided not to add these to my dB but to do this File Origini Windows ANS E e I must skip them in the import file spec as shown Dates Times and Numbers Date Order MDY T Four Digit Years Secs Date Delimiter 7 I Leading Zeros in Dates Also notice that nEEn is a single not an integer Single is used for Time Delimiter Field Information FieldName DataType Indexed Skip I No Decimal Symbot numbers with decimals and replacing your existing file spec neen mngie 0 _ nEE Singl N long Integer nLiveS Long Integer L nLive P LonglInteger No O vet eo ine 5 Be sure to save your edited file spec by using the save as button __ rwatCh Long Integer No O WwW Your table and import file spec are now ready for new data Next you must decide if you are going to put the data for the new fields in your table for your old data If you decide that you want to update your existing data then you must delete the data from your existing table The easiest way to do this is with a delete query Go to the design grid of a query Put your HX4 table in the d
28. t Qualifier should be a single set of quotation marks this is the default and should not need to be changed Click on the Next gt button This next box wants to know if you want the information placed into a new table or into an established table within this database If this is the very first time you brought data into your dB then you will select in a new table Click on the Next gt button As you import the data you can change the data types during the import A good example would be the changing the date column from text to a date You can change the field name and you are also able to skip whatever fields you wish by highlighting the field you wish not to import and click on the Do Not Import Field When you are doing this the first time for a new table leave all the fields alone and just click the next button fou can specify information about each of the fields you are importing Select fields in the area below You can then modify field information in the Field Options area Field Options Field Hame Data Data Ippe Text Indexed No T Do not import field Skip G 5 G J 6 a g Advanced Leaned h On this screen select no primary key We will set the primary key later E Import Text Wizard x Microsoft Access recommends that you define a primary key for your new table A primary key ts used to uniquely identity each record in your table It allows you to retrieve data more quickly C Le
29. tAccess add Primary Key C Choose my own n Key 11 The final thing we need to do is name our new table Access will pick the name of the export file in this case ALL_HX2 If you want a different name type it in here or click on Finish and the program will drop the data into the new table and give you a confirmation message telling you it is finished importing your text file into your new table We now have a new table in our dB E Import Text Wizard x That s all the information the wizard needs to import your data Import to Table T would like a wizard to analyze my table after importing the data T Display Help after the wizard is finished Advanced Cancel Setting the fields to the proper type When you are importing to a new table the import wizard looks at the first line of your data and decides what type of data it is 1 e text integer single An integer is a number without decimal points and a single or double is used for a number with a decimal So the next thing we need to do is to set the correct data types for this table like they are set in the template tables we got from webmaster Rick To do this go to the htr web site and get the latest copy of the HTR file spec for your export and print it out Then go to the design view of your new table and compare the field type from the HTR HX2 file spec with the data type that is assigned by Access Change the field type to agree with th
30. teria File name Text or property Find Mow Files of type Text Files tet cows babs asc Last modified any time New Search display box you wish to import and choose the Import button The wizard determines what type of data you are attempting to import It should choose delimited as the files we are importing are delimited or separated by commas If Delimited is chosen click the Next gt button The next box asks you to choose what the delimiter is that separates your columns Our choice needs to be Comma a What delimiter separates your fields Select the appropriate delimiter and see how your text iz affected in the preview below Choose the delimiter that separates youn fielde AA pt Tab C Semicolon Comma C Space b Other F Fuck How Contains Eid Names ee ee ee Text Qualifier E SID CEI OO CCIE IS ESI CRI CIC OCIS CRIS CIS SOCIO ICIS CISION CSO CE ISIS OCICS ICIS eI CUS E T CHD E DISSES T 37999 BEU 01 13 2004 l 10 If Comma is chosen you will see your data in neat columns as shown in the display box It is very important on this screen to make sure that you place a check mark inside the box next to First Row Contains Field Names If this box 1s not checked the columns will be named Field 1 Field 2 etc instead of HTR field names from the HTR program You will note when you click on that box the field names then become the column names over each column The Tex
31. the import 15 You can also use your file spec with your input macros Transfer Type Import Delimited Specification Mame ALL Het Import Specification Table Mame File Mame ALL_Hs3 Import Specification Has Field Mames ALL_Hs4 Import Specification HTML Table Name ALL_H 5 Import Specification MLL AW Import Specification SLL AW Import Specification Go to the design view of your import macro click on the right side of the Specification box and a menu will open up that shows all of the import specifications Highlight the spec for this file and click on it The macro will now use this file spec Now you have completed adding the HX2 export file to a new table and you have a macro for easy data importing Adding fields to an existing table From time to time Ken adds new fields to one of the exports For example in Oct of 2003 Ken added fields 129 to 135 to the HX4 export For a dB person added fields are a good news bad news situation The good news of course is that you have new fields to test and model The bad news is that you cannot test your existing dB since you will not have this field in any of your existing data There are 2 major steps you must perform to get the new fields into your table 1 Add the new fields to your existing HX4 table 2 If you are using a import specification it must be modified to recognize the new fields To add new fields to your table go to the design mode of your existing table put your cursor
32. txt And click OK ge ALL HX4 MD 97 Database Miel Ei E Tables E Queries Forms JE Reports Z Macros Modules Ti Inport ALL HS text file Run fe Import 4LL_H 4 text file Ti Import AL ey ee E x Cancel Design Save Macro Import ALL Hed kext File To an External File or Database Save as Visual Basic Module f Within the Current Database as New Mane Import ALL HZ text File 14 Now open the macro and change the macro design as follows From Transfer Type Import Delimited Specification Name ALL H Import Specification Table Name ALL H4 File Mame Cheryll Hed txt Has Field Mames Yes HTML Table Name Code Page To Transfer Type Import Delimited Specification Name Table Name ALL He File Mame Cher ALL HRS ext Has Field Names Yes HTML Table Mame Notice the table name is changed and the file name is changed Now save your macro Notice that we do not have an import specification for our HX2 import Working with Access import file specifications What if you decide that you don t want to import all the fields in an export file or that you want to have different names in your table then the standard ones contained in the export files Then you will need an Access import specification An Access import specification stores the information Access needs to import a fixed width or delimited text file An import specification is generated by running the Text Import Wi
33. want to put a shortcut on your desktop to easily get to your dB The htr_templates data base comes to you complete with tables ready for data import macros that automate the importing process some sample queries to get you started and ROI reports to test query results and a Control Panel with buttons to run the import and clear tables macros Importing is a very simple process If your all_hx4 txt file is in the C htr directory then go to the macro button under objects and click on it or use the import buttons on the Control Panel There you will find import macros Simply highlight the HX4 macro and press the run button The macro will take the data in your HTR2 export file and will put it into the All_HX4 table Keep in mind that the macro will add the data to any existing data in your All_HX4 table That is all there is to it Let s review the All_HX4 table in your dB Go to the table tab of your dB highlight the All_HX4 table and open it in the design view Your table should look like this FieldName DataType Text Text Date Time Number Number Number Number Mumber Mumber Number Number Number Number Number Number Text Text Number Number Number Number Number Number Number Mumber Number Number General Lookup Field Size 50 Format Input Mask Caption Default Value Validation Rule Validation Text Required ho Allow Zero Length Mo Indexed ho Notice the Key symbol on the tTRK tDATE n
34. y some queries in your query tab As we build queries we will save them and their names will appear under this tab for future use On the right side of the screen you have 3 option buttons Open Design and New The buttons are used for the following functions Tables Queries Forms B Reports 7 Macros s Modules example of a OR query Open example of a select query Design New Open this allows you to open a query that you have saved in this database This is one way of running a query or asking it to execute to find the results you desire By clicking once on the name of the query it becomes highlighted By then clicking on the open button the query then runs You will note that if there are no queries saved yet or there are no queries highlighted then the only button that is active or usable is the New button Double clicking on the name also runs the query Design once you have saved a query you will highlight it by clicking on it once then clicking on Design to go directly to the Design Grid The Design Grid is the screen where you will determine what table you wish to pull information from and what information you wish to retrieve from that table New the New Button is where you will click to make your first query if there are none in your database It is also where you will start to create a brand new query When you click on New the program will give you several options to
35. zard and is stored in the database In order to create a import spec Run the import wizard in the normal manner On the following screen click on advanced A Import Text Wizard x What delimiter separates your fields Select the appropriate delimiter and see how your text is affected in the preview below Choose the delimiter that separates your fields C Tab C Semicolon Comma C Space C Other i First Row Contains Field Names Text Qualifier I M tDATE nRACE nDIST nSURF nDS nCLA nSEX 38002 AOU 1 8 32 2 g ng r a a r a r a r 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 B ALL_HX4 Import Specification File Format Delimited Field Delimiter C Fixed Width Text Qualifier I z t i ance File Origin Windows ANSI v ear ave As Dates Times and Numbers Date Order MDY 5 T Four Digit Years Secs Date Delimiter I Leading Zeros in Dates Time Delimiter j Decimal Symbol j Field Information Text No Text No Long Integer No Double No Long Integer No Long Integer No Long Integer No JO000O0O0OOCO When you click on advanced the import file spec box will open and you can change the field name and or change the data type for each field When finished save the spec Once it is saved when you bring data in again you can click on the Specs button and select the import file spec you want to use with
Download Pdf Manuals
Related Search