access

[ronnie h]

i still can't join masc and impact try doing it like Donnie has in his manuel. can anybody suggest a book that will help me catch up

[Carl]

Reddy,

Here is something you can try.
1) Create a new query with all the fields in it that your "ALL_MSAC" table has.
2) Insert a new blank column just to the right of "tHORSE."
3) In the field row of your new blank column paste the command:

pgno: Left([tHORSE],2)

4) Run the query as a "make table" query.

If you get this far, the next step is easy, 'cause you now have four keys in each db to make the link unique.

Post back if this works or you have further questions.

[Rick]

reddy,
You will learn more asking questions here than trying to find a book that relates to what we are doing with Access.

Joining MSAC and Impact is pretty easy. I would and did do it using the same ROI query so I wouldn't have to redo the report.

Open ALL_MSA Misc Query in design mode. In the top window right click on the open grey area and click on Show Table. Highlight ALL_IMPC and click on Add. Click Close.


Left click drag tTRACK in MSA to tTRK in IMPC. Left click drag tDATE to tDAT. Left click drag nRACE to nRAC. Left click drag nPPO to nPPO.

You now have the two tables joined for those races that are in both tables. Now you can bring fields down from your IMPC table and add them to your query.

Try it and ask questions if you have problems.

PS: It doesn't hurt to get an Access book. I have Access97 Bible and don't recommend it. It was written around a pet hospital. Hard to relate.


Revised: Follow Carl's directions for adding pgno to MSA then Left Click Drag pgno to tPRG. In IMPC the nPPO field is for actual post position if the results are in.

[George]

Rick is right that you will probably get it done quicker with all the help you can get here.
If you just want a decent book to learn overall ACCESS suggest you go to www.ddcpub.com
and order Learning Microsoft Access 2000. Think it is around $35. Access For Dummies is not a bad book to start with either.

[MikeDee]

one point of clarification the current impact program does not have nPPO but has the pgno or program number instead.

Htr has PPO but does not have program number. Carl's suggestion is the easist way to do it. The left function will parse the program number from the thorse field and you can use this to create the a unique join.

We will be making sure that everyone can join 2 tables at the seminar since we will need to do this until the new htr extract program becomes available later on.

[Carl]

Glen,

Did you get your two tables (HTR and IMPACT) linked yet after Mike and Hurrikane's posts? Or or did you manage to make a new table incorportating the two if that's what you wanted to do? Post back and let me know, I'll do a follow up of the way I do it if need be. ( As you know, I basically have to do access the way of dummies.....that's the only way I get through it.) I had an easy time of it once I managed to get a pgno made. That one (making a pgno) took me a few hours and a wife to do though.

(And am still afraid of VB).

later,
Carl

[Glen]

Thanks everybody. Think it works now...

[Glen]

x

[hurrikane]

Reddy if you still have problems let me know and I"ll shoot you the pivot query that will be used to join the table.

[Cliff]

All,

I am finally getting around to linking the "ALL" table and the "IMPACT" table. I currently have a "Pivot" table which is simply the ALL table with the program number parsed out. Both tables have the four keys of track, date, race, and prog #.

Do I want the prog # key to be in number format or text format? Somehow I have them in two different formats and I think this is keeping the linkup from working. Do I need to keep this consistent in the export function as well?

Also, why would I have 2000 less records in my Impact table than in my All table if I have exported exactly the same races with results? How will the linking up handle this discrepancy?

Thanks for the advice in advance,
Cliff

[MikeDee]

The program number needs to be text because program numbers have letters in them (ie 1A, 2B, etc.) if you make it a number it will chop off the letter. Make sure the prg no is text in both tables. If you have imported your table with the program number as a number then you have already chopped off the letter on a entry. In access, every race with a entry, in this table now has a duplicate record because you have two horses with the same program number. This could be the source of you records problem. As access will not show these records with the same program number in them.

You do not need the pivot table at all. It will work, but it is unnessary and something that I was doing because of a misunderstanding I had about the Post Position Order number, PPO.

In your join query design delete the pivot table and put both of your tables in the desgin field now join with date, track race and ppo. You sohuld have all of your records show up.

[Cliff]

Thanks Mike,

I think that might solve some of my problems. Hopefully will get around to working on it later today.

Cliff

[Rick]

MikeDee,
I think you need to revisit your last comment about PPO.

In Impact the PPO is suppose to be the actual post position the horse ran from if results are in.

Most would match up but I would think in a 6 horse field that if the 5 was scratched the 6 would actually be running form the 5 post. I have not tested that yet.

But I thought that was the whole reason for creating the PGM # field in ALL_MSA.

Cliff,
You don't actually have to create the table with the PGM# field, you just have to create a query that creates the field and include all the other fields in the query. Then in your append query you use the make program # query and your Impact Table in the query, joined on the four fields, date, track, race, and program #.

It works either way, just one less step.

[MikeDee]

Rick-

As long as you are you using data that is apples to apples it is OK. For example if your htrmsa does not have results and the impact does not have results then the ppo's are a 100% match.

if both files have results then you have a 100% match.

If you had htrmsa with results and impact without resutls then you could have a mismatch if actual PPO was different then the PPO in the pre-results file. This can occur with scratches.

I think most of us work on way or the other with results or without results but not 1 file with and 1 without. If anyone is working this way then they need to join on prog no and use the pviot query.

When impact first came out I thought the impact would only have a ppo after the results where included and that prior to adding results the ppo would be zero. This was a misunderstanding my part as the PPO is always there is just has a potential to change due to scratches.

There is nothing wrong with the pivot query method, it works fine, but it was never really necessary.

[Rick]

Thanks Mike,
I had never checked that out and believed there was a difference.

[Cliff]

Guys,

A couple more questions as I get further into this:

1) When I create the query to add the PGM# field, it does not have any key fields. Thusly, wouldn't I need to run the query as a "make table" query so I can set the key fields accordingly?

If I do need to do this, is there any way to set the key fields and the type (text vs. number) before the fact instead of after?


2) Will an append query leave me with a table that I has all the fields of both ALL and IMPACT that I can ultimately query within the new table? Do I have to run the append query every time I import new data or is there a way to avoid this step?

Ok, actually 4 questions.

Thanks,
Cliff

[Rick]

I have named my Master Table ALL_MSA. That way I didn't have to change queries and reports.

I import into ALL_MSAold which is set with Primary Key of date, track, race, & PPO. I have a Make PGM# Query made off this table.

I import Impact into ALL_Impc. I import HX2 into ALL_HX2.

I have an Append Query (for first time use it could be a Make Table Query). In the top of the query I have the Make PGM# Query, the ALL_Impc Table, and the ALL_HX2 table. I have them all joined on date, race, track, and PGM # fields.

I would suggest the first time you run your Make Table Query you use a small sample. After the table is created go in and set your Primary Key. Check and make sure everything is importing the way you expect. Check for error messages. After everything is set the way you want it, delete all the records.

If you are having trouble with your PGM# field make sure it is defined correctly before going on.

Change your Make Table Query to an Append Table Query. You should be set to go.

You should not want all the fields of both (all three) tables in your master table cause many of them are duplicates. You should decide which fields you want from the tables you are importing from. Like, I decided to use the nFIN (xFIN) from Impact (HX2) instead of from ALL_MSA cause it is tied to the payoffs.

Every time you want to add new data to your master table you have to import it into your Impact and ALL_MSAold tables and then run your Append Query. It also helps to delete the records in the Impact and ALL_MSAold tables before you import new records.

Have fun.

[MikeDee]

1) When I create the query to add the PGM# field, it does not have any key fields.
Thusly, wouldn't I need to run the query as a "make table" query so I can set the key fields
accordingly?

Not sure that I understand your question. Queries don't have key fields, tables have key
fields. You set the fields that you want to be key in the design mode of a table. In the same
manner the "type" of field (ie numeric, or text) is set in the table design not in a query. The
query just reflects what is set in the tables. When you run a make table query it uses the
field definition and data types in the tables that the query is run on to set the field definition
and data types it the new table it is about to make.



If I do need to do this, is there any way to set the key fields and the type (text vs.
number) before the fact instead of after?

Yes, as I mentioned assign the key fields and the field types in the tables and save that
design. It will flow through in your make table query.



2) Will an append query leave me with a table that I has all the fields of both ALL and IMPACT that I can ultimately query within the new table?

Yes, First you do a make table query to create the new table in the first place and then save
the query as a append query to use to add data to you combined table. I would recommend
that you look at the make table/append query and delete all of the duplicate fields, the ones
that are in both tables.



Do I have to run the append query every time I import new data or is there a way to
avoid this step?
no way to avoid this the only way to get new data into your combined table is to run the
append query. I assume that you only have new data in the tables you are joining and not
running the append query on your tables with all of your odd data.

[Cliff]

Guys,

I'm still stuck. I have a table with "tpgrm" in it (also as a key) but it is number instead of text.
When trying to change it, I get the old "Not enough disk space or memory". Tried Carl's work around.....creating empty table with fields only in it, changing the tpgrm field to text and then appending the data......now getting "invalid argument" error message. Can't seem to find a way to make this work. Any suggestions?

Sorry for the redundancy and stupid questions......need "Access for Super Dummies" book apparently!

Thanks,
Cliff

[MikeDee]

Is your table very big?

Have you ever compacted your db? If not go to tools data base utilities compact data base

Try this go to the design of the table and remove the key from all of the key fields. To do this highlight each key field by depressing crtl and clicking on each field with the key. When you complete thie the Key icon on the toolbar will be depressed go to it and click this will turn off the key fileds now save the table.

Now try to change the field from numeric to text.

If it doesn't work try Carl's work around again. but before you do it make sure that there are no key fields in either table. Once the field is converted you can re-establish the keys.

If it doesn't work please post the exact error message you are getting

[Rick]

Cliff,
Is it your Impact table that has the program # defined as a number?

After changing the field type in the table definition, you will also need to make the change in your Import Specification file for this table. You should be able to do that fairly easily by doing a manual import after you get the field redefined.

And remember, as MikeDee mentioned earlier, with the field defined as a number you lost all the records for those races with entries. When that happens you should have been getting a message telling you there was a problem with some records. When you get those messages you need to figure out what the problem is, if not its going to bite you later on.

I've run into most of the problems you can have, it might be having a field defined as integer when it needs to be long integer. Can happen when a field is defined as byte but ends up having data like -1.

[Cliff]

Rick and MikeDee,

1) I attempted to compact the DB. Access worked on it for a few seconds and then came back with "Invalid Argument" error.

2) Went into Design, removed all key fields and attempted to change the TPGM field from number to text. Got "Microsoft Access can't change the data type. There isn't enough disk space or memory" error message.

3) Attempted Carl's work around method. Cannot seem to get that to work either, although it did work on a previous Date/Time issue. Not allowing me to set up blank table or import present table as definition only. Even tried making a copy of the existing table and deleting all the records. Wouldn't let me do that either. Keep getting "Invalid Argument" error message.

I have approx. 517,000 records in my ALL table, approx. 515,000 in my ALL_IMPC table, and even less in my ALL_X table. I understand that I probably lost some records because of the Program # issue, but never got any error messages in exporting or importing or any facet of analyzing the data.

Rick.....my ALL_IMPC table is correct with text definition and the proper 4 fields. I am having trouble with what I am calling the Pivot table which is simply my ALL_MSAC table with the field of TPGM added and defined. That one is set up as number.

Hope you guys can give me some more guidance on this. As always, much thanks.

Cliff

[Rick]

Cliff,
I would suggest you start over if you still have your original ALL_IMPC and ALL_MSA tables.

Both your master table and your pivot table are tainted.

Go back and re-read this thread and decide how you want to do it.

I also suggest you set up your master table with a small sample instead of all your records. It just makes it easier to work with to try and get it set up the way you want it.

[MikeDee]

Cliff- ditto to what rick said if you cannot compact your db there is someting wrong. you might try fixing and then compacting again, but doubt that it will work.

I would start over to.

Important, don't start over by using a db that was sent to you. Create a brand new db and if you want to use a template then use the import wizard to bring the template into your db. This way access knows where everythining is and you don't get any surprises later on.

As Rick suggested just bring in a small amount of data for each of the tables. then check it in the table make sure the data looks

[Cliff]

Guys,

How does one's database become tainted?

Does anyone have the best field format (ie. text, number, date/time) for each field in every export item so as to avoid these potential pitfalls in the future?

Thanks again,
Cliff

[Rick]

Cliff,
What I mean by 'tainted' us that your table is ending up with incomplete races. Those you have are almost impossible to correct in your present tables. It would take too long trying to figure out what was missing.

Mine got a little tainted but it only turned out to be 5 races over a years period. I just left it be.

When I am creating a new table, I take Ken's list of fields that show the expected data in the fields. I then compare this to the FieldSize property sheet from Access to determine what to use in each field. I then do an import and create the Import Specification file to match the FieldSize of each field.

Some times I will create the table by importing the data and sometimes I will define the table before I import any data into it. During import I check for any message indicating there is a problem with any of my fields. After importing a small sample of records I check that the data is displaying the way I expect. If any fields in any records are not imported, somewhere there was a message telling me that something wasn't imported. It may not tell me what was not done correctly but it lets me know there was a problem.

Normally I can run the problem down by finding the records that have missing data. If there is missing data I will normally change the FieldSize for those fields just to see what data is being imported that is causing the problem. Ken is normally good at letting us know what kind of data to expect but on rare occasions some of the data doesn't match what I expect.

I try to use the smallest FieldSize possible. If you are not worried about how much disk space your database takes up you can always go with the defaults that Access comes up with. I have found that using the defaults can easily eat up an extra 33% or more disk space.

Since MikeDee is the primary Access VBA guru in the forum, I will let him put together the templates for master tables. There are just too many ways to go right now. Everyone can have a reason for using different fields from the different tables when creating a master table.

Time to step down off the soap box.:D :D

[MikeDee]

the most common form of data corruption comes from a widows lock up, or crash. If you are in the middle of doing something when access is reading or writng to a table and your PC or windows locksp. access can get shut down with your tables open and data can get corupted.

Once this occurs any thing you are trying to do in access will fail when it hits the corrupt data.

You could try to use the tools command fix to try and fix your dd if this tool runs try the compact db again if you can compact the db it may be fixed, try to convert the prgno field.

If you are insistent on trying to save your data in your db try this. make a new query load in all the fields and set the date to filter on 1st 6 months or so of your data and make a new table with a smaller amount of data. Now go to this new table and see if you can change the prgno field to text.

If you can, repeat the process save another chunk of data to a different table. change the prgno field and now append this data to the first table. continue a process like this until you have all of the data in the new table.

delete the old table now try the compact again. If it still doesn't not work do another of you old tables. the same way.

Until you can compact your db you have corrupt data and it will continue to cause you unexpected problmes until you correct it. Or jsut start a new db

[Cliff]

Guys,

Still working on the linking of the two tables and making some progress (slowly). Here's a couple of things I ran across and could use some guidance. I went back and moved the data files to HTR, reran HTR4MSAC, Impact, etc. and exported "clean" data.

1) My MSAC table still has 182 more records than my IMPC table for the first month exported. Is there any "easy" way to determine which records are different in a whole month's worth of data? I was careful to only export racefiles that were complete with results and charts.

2) I did finally get a linkup of the two tables at the lower number of records ( -182 above ) and as mentioned in earlier posts had several duplicate data fields. Does it make any difference which set of duplicates fields I delete, or, from which table? Will I continually have to delete the duplicate fields as more data is added, or, is there a way around this?

3) Is anyone still working with the XFactors data, and, would there be any need to append this as well?

Thanks much,
Cliff

[MikeDee]

Question 1 yes there is a easy way

go to the query screen and click on
new find unmatched
follow the instructioins
the first table to put in is msac
the second is impact

when access asks what field to match on tell it one of your joined fields like date
in the next question tell it you want to see track date race ppo
run the query

the first time through ignore the resutls becasue the wizard cannot deal with more then one join field

now go to the design of this new query You will notice that the black line joining your tables has a arrow head pointing to impact.

Join your other 3 fields in the normal manner. except you have to change them to also point to the impact table

you do this by double clicking on the line and aj oin properities box will pop up. clcik on option 2 and OK You should now have a arrow pointg towards impact

do this for the other 2 join items so that all 4 point to impact.

now run this query and you should have 182 records and with the date track race and ppo

now you need to see why these are in msac and not impact

question 2
I'm not sure what you mean here that you have serval duplicate fields. When you did your link access says you have 182 unmatched records. These are not duplicates when you run your query access is showing you all the records that are matched in both tables.

Are you saying that access is telling you that you have duplicates within your matched records?

do you still have your all_msac file and your all_impact file from htr that built these two tables?
If you do run pkzip on them and send the zipped files to me at mrdez55@ameritech.net

There is no reason not to have have the same number of matching records in the msac and the impact tables.

question 3 no you don't need the x factors stuff anymore

[Bernie]

Mike,

If there are a few races that show up in one table but not the other, is there a potential problem in doing queries using both tables, i.e., will there be erroneous query results or will those races without the required data in one table be ignored during the query? In other words, should we delete races that don't appear in both tables?

[MikeDee]

no there is no problem if you are joining the tables then you are only looking at the races where you have la match so your queries are just not including the ones that where dropped becasue they did not match.

I don't normally encounter any differences when I run msac and impact and then join so I'm wondering what the differences are:confused:

[Rick]

Cliff,
You need to decide which duplicate field you want in your master table. You need to delete the duplicate fields in your master table and then make the changes in your Append Query where you are only appending the fields you want to append. You should look at the data in each field from each table and decide how you want to use it.

I decided that the data in HX2 was the direction that KM was going so I went with the fields there first. I like the way the finish and payoff are tied together in HX2 and Impact as opposed to ALL_MSAC so I went with xFIN and xWIN, xPLA, and xSHO. THen I took the nTRI and nEXA from Impact.

Just use your Append Query to set which fields from which tables.

Bernie,
Queries run faster when you create a master table than when running queries from joined tables.

[Cliff]

Mike,

Sent the files but they got kicked back. Tried to send them to the mrdezo address too and that didn't work. Explanation email followed the one with the files attached and it kicked back as well. LMK if I'm doing something wrong.

Thanks,
Cliff

cliffhartnitt@attbi.com

[MikeDee]

sorry Cliff, can't even type my email right

pls send to mrdezo55@ameritech.net

[hurrikane]

You might also run a query to check for races without results. HTR4MSAC doesn't let you export only races with results and once in a while, especially when exporting a lot of races..it is easy to overlook a race without results in the htr export. If a track closes early and you don't know you will have races without results even if you are checking for the ** before you export. You can't catch them all.
This is something you should check frequently as this can have an dramatic effect on your query results.

[Cliff]

hurrikane,

Is there a module or a SQL floating around for this query?

How could you have races without results and still have the * or ** in the HTR window?

Thanks,
Cliff

[Glen]

Cliff, we still need to hit FR w/ Castillo on day. Perhaps a bowl of wheaties in the morning, day at LS, and nite at FR would make for a complete and healthy day.

[hurrikane]

There is a query around. Something like nfin=1 and win =0.

Not sure if this is the reason but if you download tracks files in the morning and the the wimps at MNR decide it's too cold to race on the second race you have results for the 1st and 2nd race but the others are empty. Not sure if that is the reason it happens but I get single and groups of races in a day that don't have results. I didn't really investigate..just assumed that is why. In Impact and 2001 you don't get this because is doesn't export races without results unless you tell it too.

[Cliff]

Glen,

Yes, you are correct on the LS and FR day. Would be healthy for mind and body.......probably not healthy on wallet. Next time you can play bodyguard for that troublemaker TC. I'll let him tell you that story.

Just give me some advance notice so I can prepare new GF for day/night of carousing around.

HK,

D'oh on my part as far as the query. Am not seeing the forest for the trees these days. I did have 29 records of nFIN=1, nWIN$=0. Suspect that may be part of the problem. Also have many records (mostly SUN) of nFIN=2, but no nEXA$ figure and yet a figure in nTRI$. ??? Not sure I can explain that either. Have not been able to tie to the 182 record difference yet. May wait and see what MikeDee comes up with.


Cliff

[MikeDee]

Cliff,

sent you some stuff. HK is right I am 99% sure that the difference is in how htrmsac handles partial cards versus how impact does. In htrmsa I think all the races are there even the ones that did not run, due to a partial cancellation. I suspect that in htrmsac these cards come up with two stars. But impact, on the other hand only exports races with results so it does not export these races that were canceled. And these cancelled races on partial cards are the difference.

So you can use what you got because you don't want these 182 records in you db anyway they are races that were not run.