Database and Excel Spreadsheets

[cj]

Originally Posted by bcgreg

CJ

I am pretty sure that Access tables are limited to 256 fields.

bcgreg

Yes, true, but there aren't that many fields in the file I was discussing if I remember right. It has been a few years though.

[cj]

Originally Posted by classhandicapper

Let's do the simplest case first (1 race card from 1 racetrack).

We agree that you can get a Formulator extract for a single day and single track as 1 file or multiple files.

The reason the multiple file option exists is that each of those files contains unique information, unique fields, and is in a unique format (trainer stats, jockey stats, horse data, tomlinson data, earning box data, Beyer data, workouts...). If they are merged into 1 file, you have a lot of different record layouts inside the same file.

It's quite possible that when you were using Formulator, you were only using the main PP file and discarding the other specialized Trainer, Jockey, Workout, Beyer and Tomlinson data that are in the other records that are part of DRF PPs.

I am using some of that other data. So I am building the main PP file into 1 table and the specialized data I want into others. Then I am joining them.

That may explain the disconnect.

As far as merging multiple days or cards goes, you solved my problem. I was able to do a copy and merge several days of data and turn it into one import for a table. So one major issue has been solved well enough. Thanks!!!! When you visit NY, lunch is on me.

Told you there was a disconnect, I get it now. I didn't realize all the data wasn't included in the single file. It must have been stuff I wasn't using.

[raybo]

Originally Posted by cj

Told you there was a disconnect, I get it now. I didn't realize all the data wasn't included in the single file. It must have been stuff I wasn't using.

I bet all that data is also in the single file, but those fields are mixed in all over the place in the single file. I know that Brisnet's multifile, and their single file contain the same data, it's just that in the multifile the data has been separated by type into separate files, for ease of use and ease of import into apps that only have 256 fields/columns.

[cj]

Originally Posted by raybo

I bet all that data is also in the single file, but those fields are mixed in all over the place in the single file. I know that Brisnet's multifile, and their single file contain the same data, it's just that in the multifile the data has been separated by type into separate files, for ease of use and ease of import into apps that only have 256 fields/columns.

I don't think all the data is there, but again, it has been a few years. I definitely don't remember Tomlinson Ratings for example.

You are definitely correct about BRIS, same data regardless of format.

[classhandicapper]

Originally Posted by raybo

I bet all that data is also in the single file, but those fields are mixed in all over the place in the single file. I know that Brisnet's multifile, and their single file contain the same data, it's just that in the multifile the data has been separated by type into separate files, for ease of use and ease of import into apps that only have 256 fields/columns.

If the DRF data is easy to separate inside the single file, it's not in the documentation. Visually it looks like a nightmare to me. That's why I immediately went the multi file direction.

Besides, I have no interest in the Tomlinson numbers, top Beyers on each surface, or even the workouts at this point. But I am interested in some of the trainer data, jockey data, and the consistency box. So I created tables for those and discarded the others.

At this stage though, you guys have already helped me get to the point where I am fast closing in on having the database I want and a process for loading it that won't be too time consuming.

[classhandicapper]

Originally Posted by MJC922

Again I come back to advising the use of a staging table. Once you have it in there you can do something with it. Try not to think about filtering on the way in, get it in first, even though it's all chaotic and messy in the staging table you can work with it. For example if there's an S to identify that entire record as race data then a select query on the staging table in the QBE grid with an S added as criteria for that field brings up only those records. Switch that from a select query to an append query to push those records out to a specific table.

Can we revisit this?

I had no difficult creating a query that will prompt me for the Track and Date (the 2 fields I need added to me staging table). It will then generate results that include both the Track and Date I entered and the rest of the fields I need for just "S" records.

It's getting from there to Append that's giving me trouble.

Do I have to recreate the entire query as an Append query to accomplish the task of doing an actual append?

That the way it seems. Somehow I accidentally wiped out my query but just switching to append.

Also, one minor quirk. When you click on the "*" to add all the fields of a table to the query (there are 92 fields in this case), is there any way to say "all except 1 or 2 fields" you might want to leave off instead of listing all 90?

[MJC922]

Originally Posted by classhandicapper

Can we revisit this?

I had no difficult creating a query that will prompt me for the Track and Date (the 2 fields I need added to me staging table). It will then generate results that include both the Track and Date I entered and the rest of the fields I need for just "S" records.

It's getting from there to Append that's giving me trouble.

Do I have to recreate the entire query as an Append query to accomplish the task of doing an actual append?

That the way it seems. Somehow I accidentally wiped out my query but just switching to append.

Also, one minor quirk. When you click on the "*" to add all the fields of a table to the query (there are 92 fields in this case), is there any way to say "all except 1 or 2 fields" you might want to leave off instead of listing all 90?

I would recommend saving the select query. Then on the left side of the interface you should have a list of All Access Objects, expand Queries, the query you saved should be listed, right-click it and choose copy, right click somewhere in the same area and choose paste. Now you can right click on the copy choose design, go up top to the ribbon and click on Append, it will prompt you for a table to append to, Choose the appropriate table from the drop down menu, click ok, save the query then you can run it. Rename the append query later to something more appropriate than copy of etc.

Queries can call other queries so descriptive naming will help in the long run. For example you aren't limited to creating a new query and adding a table to work with, you can set up a totals query to group by certain fields and average on others, this might use another query as its source. Nested queries can be slower to finish executing but it's a cool option to play around with and can be changed into a make table query for example.

For the minor quirk you mentioned, in the query design area you should be able to shift click all of the fields in the table and then ctrl click on the ones you don't want, right click on what's selected and drag it onto the grid. You can also just shift click to select them all, drag them down and just uncheck the fields you don't want shown in the result. I prefer the latter method, that way those fields can still be used for criteria even if you don't want them to show. If you don't include them at all then you can't set criteria there. Your call though, just depends on the situation.

[classhandicapper]

MJC922,

I got the Append working properly. Thanks.

Basically, I still have a bit of a tedious process for the Chart Results, but it's WAY better now.

1. Download the Track/Date Charts I want from DRF.
2. Upload the specific one I want first into a Staging Table.
3. Run the "S" record Append to it's appropriate table.
4. Run the "R" record Append to it's appropriate table.
5. Run a Delete Query to initialize the Staging Table
6. Go to step 2.

I don't see much of a way to streamline it from here. It takes a couple of minutes per Track/Date. If I was doing this for 20 tracks, it would be tedious. But I'm only doing a few.

The Appends work nicely. I manually enter the Track and Date I want inserted into the table, it selects the records I want, and adds them with my manual fields.

[MJC922]

Sounds good Class. Adding one last step to this -- every so often go to the File tab and click the 'Compact and repair' button. You're doing the right thing to clear the staging table(s), keep in mind however the bloat from deleted records in Access isn't cleared out until you compact the db. If not compacted regularly Access can slow down, eventually to a crawl.

A nice option in some cases is to configure 'Compact on close' under File tab>Options> Current Database.

[classhandicapper]

Thanks

I ran across a problem with the "S" Append that I wasn't expecting after the "R" Append worked so smoothly.

When I originally imported the Chart File into the Staging table, it defined everything as Short Text even though there are numeric fields here or there depending which record type you are looking at.

In my final tables (both R and S) some of the fields were defined as numeric when I created those table using an Excel file as input. The R's are appending fine, but I'm getting the error message on the "S" append. The problem is it won't even tell me which field(s) are a problem so I can see what's going on.

After I got the problem, I decided to see if I could import the same exact data as an Excel file instead of as an Append and it worked fine.

Is there any way I can trace the problem field(s) or get a clue as to what the issue is?

Error

"MSFT Access set 113 fields to to null due to a type conversion failure and it
didn't add 0 records due to key error, 0 due to lock, 0 due to rules violations. To ignore and run query click Yes."

I click Yes, it seems to run, nothing gets added. No messages.

[MJC922]

Sometimes Access will create an extra table in these cases named errors or something like that so you can open it up and at least track down the field name. I don't know how many fields are in the file but worst case you may have to just pick through it using the table designer to see what doesn't look right. Take a look at the saddlecloth number field, it should be text but can get tagged as numeric.

[classhandicapper]

Originally Posted by MJC922

Sometimes Access will create an extra table in these cases named errors or something like that so you can open it up and at least track down the field name. I don't know how many fields are in the file but worst case you may have to just pick through it using the table designer to see what doesn't look right. Take a look at the saddlecloth number field, it should be text but can get tagged as numeric.

I get that extra table from time to time with the errors, but unfortunately not the one time I needed it.

I'll have some time to take a better look at it field by field tonight......all 92 of them. :-)

[sjk]

Use the function val([xyz]) if you want to convert a string of numeric characters to the number it looks like.

[MJC922]

Originally Posted by classhandicapper

I get that extra table from time to time with the errors, but unfortunately not the one time I needed it.

I'll have some time to take a better look at it field by field tonight......all 92 of them. :-)

I tell you folks, it's harder than it looks. It's a long way to the top if you wanna rock n roll.

[classhandicapper]

No luck whatsoever.

I checked every field identified as numeric in the target table to make sure the source field actually had a number in it. Every one did.

Now granted, even though they contain numbers, some of those fields in the staging table are identified as text. So that could be the issue. The system may think it's trying to move text to numeric. However, that was true of the "R" record append also and it worked.

I could go into the Staging table and define all the numeric fields on the S records as numeric, but that may cause other problems. Then some of the text fields in the R record will be defined as numeric when they really contain text.

If I identified some of the numeric fields as text in the target table I assume I wouldn't be able to use them for numeric calculations anymore. Correct?

I could also easily do it the long way and manually edit my Excel files and then import them, but the whole point of using the Append was avoid all the manual stuff.

If both Appends weren't working, this would be less frustrating. As it is it just makes me hate the product for giving me an error but absolutely no clue as to which fields are a problem and why.