Access Question - Multiple PL Imports & Queries

[Sheets Guy]

Help!

What I need to do is import multiple PL modes from a HX4 export into Access. Basically what I'm trying to study are contender and longshot flags in PL-1, PL-4 and PL-5. How do I do that? If I create one monster ALL_HX4 from a PL-5, Pl-4 and PL-4 export file I'll have the 3 times the records when what I really want is a PL-5 file that has some additional field for PL-1 and PL-4 fields.

Thanks in advance.

Sheets Guy
An Access Rookie :confused:

[Rick]

You basically have two options.

The first you already mentioned was importing all races in all three pacelines into one table. Easy to do when you add the PL to the Primary Key.

But you end up with a massive sized table.

The other choice is to create three tables. Since you only want limited info for the other pacelines, I would only put the fields needed to form a relationship with the tables and the additional fields you are interested in.

The fields needed to form the relationship are date, track, race and program number.

You still have to export all the races 3 times and keep track of what you are doing so you can import into the right table. You need to only include the fields you need in your 2nd and third table and edit the Import Specification file for each (actually you should be able to do one and make a copy) to only import the fields you are interested in.

Then you do your query with all three tables and create the relationship between the three table (drag track to track etc).

Will take much less space but will be more work trying to keep everything straight.

Have fun. :D

[Sheets Guy]

Hopefully it will be easier to do than the explanation sounded. I will try tomorrow and let you know how I make out.

Regards,
Ian
:)

[Rick]

It is easy.

You just have to get organized so you can do an export in PL5, rename it or move it out to another directory or just import it and delete it.

Then for each of the other pacelines. You always have to start off with a new ALL_HX4.txt file.

You might want to create a check list to follow.

It's easy and easy to mess it up.

[Sheets Guy]

OK Master Rick. I created a table called PL1_HX4. It has six fields:

1) ID
2) nIDX
3) tTRK
4) tDATE
5) nRACE
6) tPGM
7) PL1_LONG (the nLONG field exported with a PL1 export)

I want to add this 7th field, PL1_LONG to my ALL_HX4 table. If I can do this, I will have the
PL5 longshot field (form the original ALL_HX4 table) and the appended PL1_LONG field which is just waht I need. Can you please talk me through this. I think after I do this one time I won't need to bug you again.

Thanks.

Ian

[Rick]

1) ID
2) nIDX
3) tTRK
4) tDATE
5) nRACE
6) tPGM
7) PL1_LONG (the nLONG field exported with a PL1 export)


What is ID for?
You found a use for nIDX?

Why add to table?

Basically you will have to create a new table from your query. Why?

Just use your queries to look at and query. Everything doesn't need to be in one table. You are just asking for more work and more places where something can go wrong.

You can use a make table query to put all your fields in one table.

You can then change the query to an append query to add future records to the table.

I think it is much easier to just leave it at three tables and use queries.

But of course it is your db and your tables to do as you please.

At least that is the way I look at it on my end, if I can get it to work. :D

[Sheets Guy]

Rick:

I don't understand Access well enough to simply execute what you suggest. If I follow you, your suggesting I run a query ACROSS tables.

1) How does Access tie the records together. I take it there is someway for it to match on track, race and program #.

2) When I tried to create a query with all fields from ALL_HX4, except the PL1_LONG field that is in PL1_HX4 it wouldn't let me use the PL1 table. My table choices were limited to ALL_HX$ when I chose from the list and when I manually typed in PL1_HX4 as the table it said it wasn't a valid choice.

Is there a way you could quickly list the steps? I'm pretty sure I could follow it.

Thanks!!

Ian

[Rick]

Go to the HTR Library (http://www.homebased2.com/km/library.htm)

Download and read the three pdf files done by Donnie and MikeDee.

That should help a lot.

Then get back with any questions.

[Sheets Guy]

Rick:

I got it, sort of. I was able to do the link up and the combined table query. Unfortunately, the two tables I linked from each had 324,727 record and now the output query (if I use all records) has 329,514. I can't for the life of me figure out why. I'm POSITIVE the PL1 and PL5 table were identical. I've got some duplication somewhere but I can't find it.

Regards,
Ian

[Rick]

Sounds like you are not using date, track, race and program number as the primary key.

If you have those fields set as the relationship for the two (three) tables in your query only the records that are in all tables will be used in the query.

Easy way to see what you primary key is to open each table in design view. The fields that have the small key icon next to them are the primary key. By using the correct primary key you will not have any duplicate records in your tables.

Easy way to get rid of duplicate records is to make a copy of your table without records. Set the primary key for this new table. Then copy/append the records from the original table to the new table. You should get an error message about records that could not be copied or something like that. If it gives you the option to accept click on Yes/Ok.