Remedial Access!

[Maxspa]

Mike,
I've printed the Access Manual you wrote and have carefully perused the chapters 1+2.
I have a couple of questions that probably been asked a hundred times. My approach using Access will be track specific. When I exported and then imported Churchill Downs, everything seemed to go well . However, when I did the same with Yavapi I found both of them in the same chart Churchill First and Yavapi following. What am I doing wrong?
Thank you in advance for your time on my behalf!
Maxspa
P.S. My experience with databases can be inplanted in the eye of a needle!

[Rick]

You need to delete the ALL_HX4.txt file before doing a new export in the same directory.

When HTR2 does an export it will create the file if it is not there, but if it is there it will just append the new export to what was already exported. It doesn't care it they are the same races.

Just get into the habit of deleting the ALL_HX4.txt file.

Of course if you put all the tracks in one table, all you would have to do is include the track code for the track you wanted to run a query on in the criteria under tTRK.

I find that is easier than keeping a separate table for each track.

And that way, if your Primary Key is set correctly, you will not be importing duplicate races even if you forget to delete your ALL_HX4.txt file. Just click YES when you get the error message during import and Access only imports the races that are not already in your table and only imports the duplicate races in your ALL_HX4.txt file once.

It takes a little getting use to. :cool:

Of course it's What Works For You

[MikeDee]

Max
Glad to hear that you are doing some downloads before coming to the seminar. It won't be long and you will be able to fill gallon buckets with your Access knowledge.

Even though you are only doing a few tracks you should export all of them one at a time by clicking on the names of the tracks on the HTR2 export screen. As you do this HTR2 will give you a messge that it is exporting the track to the text file. You should do this all at the same time, puting the data you want in a single all_hx4.txt file.

No need to do 1 track, import it into access, and then go back and do a second track, just do all the tracks you want in a single all_hx4.txt file. Putting them in your table at the same time.

You want both tracks in the same table, I assume you mean table when you say chart. But like Rick mentioned yoiu don't want any duplicates in your data.

Best way to avoid duplicates is to use the template dB or to set the key fields like Rick mentioned.

This is all covered in the manual keep reading, experimenting with you dB and asking questions.

[zimal2]

When the database is large, isn't there a query to check if there are any duplicates in there?
If so, is it too complicated to explain here? If so, could we touch on it at the seminar?

Zim

[Rick]

That is what the Primary Key (date, track, race, & program #) is for.

The Primary Key will keep duplicate races out of your table.

Access has a built in wizard to create a query to find any duplicate records in a table. From my experience (long ago) it is easy to find the duplicate records but not easy to get rid of them.

My advice is to make sure you have your Primary Key properly set.

It is set in the ALL_HX4.mdb template.

[zimal2]

I ask the question because I have the uneasy feeling that I answered "no" instead of "yes" to the error message one time in creating the database, and I'm not certain of the consequence of that because I don't recall the content of the error message I do have the primary keys in.

Zim

[MikeDee]

Zim

We can cover this in the seminar, be sure to remind me when we are review the user manual The finding duplicates wizard is difficult for us because this is set to look for only 1 field with duplicates. We have to look at 4 fields together to find duplicates.

Take a look at page 9 of the user manual. Then look at the design of your table. If you have the key fields set this way, then you cannot have any duplicates in your table.

Take a look at page 13 of the user manual half way down the page. There is a sequence that shows you how to set the key fields for your table if they are not set.

If your key fields are not set, then set the 4 key fields date, track race no and prg no. and save your table. If you have duplicates, access will not let you set these as key fields, so you will have to remove the keys.

Let me know if you have duplicates and I can post an easy way to remove them.

The error messge you described sounds like the one you get when you have the keys set and you are tring to append data with duplicates to your table.

If this is the case you should have said yes becasue only one line of date per horse would be appened not the dups. Saying no resulted in none of the data being appended.

[Maxspa]

Rick and Mike,
Thank you for taking the time from your busy schedules to answer my elementary question. Elementary for you and remedial for me! Due to a phone problem, I have been unable to access AOL until today- Monday. How are you guys with phone hookups? Just kidding!
I have one more question and that is I already have duplicate files for CD because I was trying to get the Access steps correct. How do I get rid of the additional CD files?
Again you guys have been so unselfish with your time and help!
Maxspa

[Rick]

For my way of thinking, the easiest way to get rid of duplicates, is to make a copy of your table, without records, make sure the Primary Key is set in the copy of the table you just made.

Then you can use the same copy function a second time to append the records from your original table to your copy.

Then delete your original table and rename your copy to the original name.

You do this by opening your database, hightlighting the table name, right click, copy.

Then just to the right of the file name right click paste. You get an option box with three choices. The first time you want structure only. The 2nd time you want to append records.

[Rick]

Two more things.

To set the Primary Key, open your table in the design mode. Hold down the Ctrl key and click on the box to the left of the field names for date, track, race & program #. My experience has been that I have to start at the top and work down.

When all four are highlighted, click on the key icon in the tool bar. Key icons should appear to the left of the field names.

When doing the append you will probably get some sort of error message. The gist of the error message is that there are duplicate records. Click Yes/OK - that will append all the records except for the ones that are duplicates.

[Maxspa]

Ricks,
Appreciate the file deletion help! No more questions until after the seminar and hopefully they will be at a minimum.
Maxspa