Joining the ALL_MASC and ALL_IMPC Tables

[Victor]

I'm not doing this correctly. I'm using the ALL_MASC template but do I also need a corresponding template for Impact? If I use the import table wizard to produce the Impact Table, does this cause table incompatability? I've read the Impact Manual and the board posts related to joining tables, but I'm still stuck. I've also tried adding primary keys to the Impact table produced by the table wizard. I've let wizard choose the primary key. I've told the wizard not to use any primary key. What should I try next?

[hurrikane]

Welcome Victor....
Import table...either All_MSA or Impact...if you don't have a table you want to use already such as All_MSA or all_Impc then the wiz will create a table for you the first time. After that you want to import into the table you created the first time....select table, first column is field names...select the table.

Primary Keys...VERY IMPORTANT...to avoid duplicates. Open table design mode..hold down the control key..select track, date, race and pp in ALL_MSA..click on the primary key button on the tool bar. Select trk,dat,race,tpgm in ALL_IMPC and click the primary key button on the tool bar.

Joining the table. Here is where the problem is. In All_IMPC the tpgm is a character, this is because of the coupled 1a,2b,ect entries.
In All_MSA..nPPO is an integer...the actual prm number is included in the nHorse field. So you have to create a field that will strip the pgm no.
from the nHorse field in All_MSA and create a new field that is a string that has the pgm no.

I do it like this...others have thier own way..hopefully they will post an you can choose.
I create a query I call PIVOT. include the table All_MSA. Click on the * in the table box to include all fields in the table. in the next field in the design are at the bottom in the field area type tpgm: (Left([all_msa].[thorse],2)),

you will not have a field in the Pivot table and can join the two tables by track, date,race,tpgm.

Let me know if you have a problem

[Carl]

Hi Victor.

Sounds like "life as usual" with Access.

If you're of a mind too, grab a cold one then:
1) Yes you need a separate template for IMPACT. I would imagine there is one on site. If not, try this.
2) The first time you bring data in from your "ALL_IMPAC" file it should come in with column headings. If it is, you'll be able to see that when you start your download. So, then check "first row headings" or whatever the message is, and put in into a new db.

Post back, let me know how you are doing. Once you get IMPAC going (which you can query the same way you query HTR, BTW), we'll worry about joining the two (probably best with a make table).

later,
Carl

[Victor]

To Carl and Hurrikane:
Thank you very much! Both of you are fantastic. The tables are created and linked and now here comes the money! O.K., I know, but it's an important step in the right direction.