Database and Excel Spreadsheets

[sjk]

Originally Posted by classhandicapper

I downloaded the Nov 1 Breeder's Cup files out of Formulator.



2. The major file is a horse file. It contains a header record that describes what each field contains and then hundreds of data records. is there any way of importing that header record and making those my field names so I don't have to manually type all those field names in myself?

You can select headers as field names when you import.

[classhandicapper]

Originally Posted by sjk

You can select headers as field names when you import.

Thanks. I don't know how I missed that. I guess I wasn't looking for it.

[MJC922]

Originally Posted by DJofSD

I can not disagree with anything you've said. However, I think it needs to be said that beyond the power gained vis-a-vie the query engine, it only will be capable of fulfilling that promise if the database and the tables making up the database are well formed. So, learning a bit about normal forms and what joins are will help get things off on the right foot. The wrong foot could very well mean your data is in a database but you can't use it for anything meaningful.

A long time ago, back when Paradox with the RDBMS I was using, the nice thing it offered was QBE, query by example. It allowed you to form queries by checking boxes (and a few other things) which shielded you from having to learn SQL. It help flatten the learning curve. I don't know if anything like QBE is around any more but it was what I thought of when I tried OO Base the first time.

The QBE grid is an Access feature, it may have originated elsewhere (I don't know the full history behind it) but I've seen the grid since at least Access 97. It's fantastic to put it mildly. I agree with everything Traynor said, for a beginner especially Access shines like no other and when you bring VBA into the fold Access becomes an outstanding choice even for more advanced users. The impact of VBA should not be underestimated. Where Access is a poor choice is when you're talking about the need for multiple concurrent users or larger size DBs, the limit on the Acesss db is 2GB right now.

Personal opinion here, Normalization with a db of this type is not all it's cracked up to be. A lot of the books on dbs focus heavily on proper design. It's good to know about proper design of course but it's possible do plenty without going that route.

[classhandicapper]

Here's the first issue I need to resolve.

So far I imported the Formulator BC Cards for 10/31 and 11/1 into a database that has 3 tables.

1. Running Line Table - This is the running line information for every race each has run coming into the races on those days (1 record for each race each horse has run).

2. Horse Table - This is horse specific information. It contains things like trainer win%, jockey win%, the earning box info etc... coming into the race. There is 1 record for each horse.

3. Race Info Table - General race info

At some point I might want to know information from table 1 and information from table 2 at the same time when I do a query or produce some kind of report.

The logical way to join them would be on Track, Date, Race #, Horse. For example, if I am looking at a horse's last race, but also want data from his earning record, I'd have to get it from the other table.

I could look at the Track, Date, Race #, and Horse Name, go to the other table, and get the rest of the information for that horse.

The problem is that I don't see how to accomplish that.

I looked at some tutorials on relationships etc... but I didn't see anything for joining multiple fields on 1 table to multiple fields on another. Perhaps I am way ahead of myself. Any insights?

[cj]

Originally Posted by classhandicapper

Here's the first issue I need to resolve.

So far I imported the Formulator BC Cards for 10/31 and 11/1 into a database that has 3 tables.

1. Running Line Table - This is the running line information for every race each has run coming into the races on those days (1 record for each race each horse has run).

2. Horse Table - This is horse specific information. It contains things like trainer win%, jockey win%, the earning box info etc... coming into the race. There is 1 record for each horse.

3. Race Info Table - General race info

At some point I might want to know information from table 1 and information from table 2 at the same time when I do a query or produce some kind of report.

The logical way to join them would be on Track, Date, Race #, Horse. For example, if I am looking at a horse's last race, but also want data from his earning record, I'd have to get it from the other table.

I could look at the Track, Date, Race #, and Horse Name, go to the other table, and get the rest of the information for that horse.

The problem is that I don't see how to accomplish that.

I looked at some tutorials on relationships etc... but I didn't see anything for joining multiple fields on 1 table to multiple fields on another. Perhaps I am way ahead of myself. Any insights?

Just add all the tables and link them in the query window. Just drag a line from the each field you want to link to the other. You can definitely do more than two. You can do the same thing in the Relationships window so they always link when you add the tables.

[DJofSD]

Is this using Access or something else?

When using a RDBMS and taking the 30,000 foot view, it is useful to think about the databases and the tables from different perspectives. Two fundamental perspectives are (1) how to define, i.e. create, the databases and the tables, and, (2) how to perform tasks using the databases and tables.

To create a database and table, commands/statements/directives are in the category of DDL, or, data definition language. These are used to create a database or table giving it a specific name and characteristics. See: http://en.wikipedia.org/wiki/Data_definition_language .

Once the database with a table exists, getting data into it, running queries and manipulating the content of the tables and database will use another set of commands/statements/directives referring to as DML, data manipulation language. See: http://en.wikipedia.org/wiki/Data_manipulation_language .

Before any one is going to be able to drill down to possibly show how you might be able to use the data, realistically, they'll need to have the DDL for the tables. Then, they might be able to give you the DML in the form of a query.

P.S. Or, some nice graphics with the tables and relationships.

[DeltaLover]

Originally Posted by classhandicapper

Here's the first issue I need to resolve.

So far I imported the Formulator BC Cards for 10/31 and 11/1 into a database that has 3 tables.

1. Running Line Table - This is the running line information for every race each has run coming into the races on those days (1 record for each race each horse has run).

2. Horse Table - This is horse specific information. It contains things like trainer win%, jockey win%, the earning box info etc... coming into the race. There is 1 record for each horse.

3. Race Info Table - General race info

At some point I might want to know information from table 1 and information from table 2 at the same time when I do a query or produce some kind of report.

The logical way to join them would be on Track, Date, Race #, Horse. For example, if I am looking at a horse's last race, but also want data from his earning record, I'd have to get it from the other table.

I could look at the Track, Date, Race #, and Horse Name, go to the other table, and get the rest of the information for that horse.

The problem is that I don't see how to accomplish that.

I looked at some tutorials on relationships etc... but I didn't see anything for joining multiple fields on 1 table to multiple fields on another. Perhaps I am way ahead of myself. Any insights?

I do not like the idea of storing past performances in a RDBM.

Although doable, it still presents the need for a relatively complicated and awkward to use relational model. Before you continue, think about what exactly you are trying to accomplish here.. In my case I prefer to keep the DRF files either in plain text (csv) format and parce them in real time or to store them in a NoSQL back end which is a perfect fit for it.


You can use RBDM to keep the results of the race, which represents an easier model and can be accomplished with two tables (one for the race specifc data like track, date, classification etc) and one for the starters of the race.

[classhandicapper]

Originally Posted by cj

Just add all the tables and link them in the query window. Just drag a line from the each field you want to link to the other. You can definitely do more than two. You can do the same thing in the Relationships window so they always link when you add the tables.

Do I have to define them as Key Fields first?

I see they are Key fields in your diagram, but I thought there could only be 1 primary key. When I created my table I believe it assigned a record number as the key. I could easily change that, but it limits me to one field.

[classhandicapper]

Originally Posted by classhandicapper

Do I have to define them as Key Fields first?

I see they are Key fields in your diagram, but I thought there could only be 1 primary key. When I created my table I believe it assigned a record number as the key. I could easily change that, but it limits me to one field.

I'm still curious as to how you have the multiple keys in your diagram, but I wrote my first simple query asking for info from each table and it seems to
have worked!

Thanks!

I don't know where I'm going, but I'm on my way.

[cj]

Originally Posted by classhandicapper

Do I have to define them as Key Fields first?

I see they are Key fields in your diagram, but I thought there could only be 1 primary key. When I created my table I believe it assigned a record number as the key. I could easily change that, but it limits me to one field.

No, you can make multiple fields key fields as you see. It would work without them, but it makes sure you have no duplicate records. In table design, just hold control down and select all the fields you want as part of the key, then press the key button.

[classhandicapper]

Originally Posted by cj

No, you can make multiple fields key fields as you see. It would work without them, but it makes sure you have no duplicate records. In table design, just hold control down and select all the fields you want as part of the key, then press the key button.

You are the man.

I'm still clueless, but at least I am learning the product.

[raybo]

Originally Posted by classhandicapper

You are the man.

I'm still clueless, but at least I am learning the product.

Class, did you just import the Formulator data as a csv file without the field names, or does Formulator offer some way of doing that. When I was trying to create an Access db, I imported Bris data files into Excel first, processed the data into named columns, and then sent it to Access.

[cj]

Originally Posted by raybo

Class, did you just import the Formulator data as a csv file without the field names, or does Formulator offer some way of doing that. When I was trying to create an Access db, I imported Bris data files into Excel first, processed the data into named columns, and then sent it to Access.

I personally find it easier just to create a table to match the file and name the fields to my liking. It also makes assigning data types and size for storage for each more efficient.

[classhandicapper]

Originally Posted by raybo

Class, did you just import the Formulator data as a csv file without the field names, or does Formulator offer some way of doing that. When I was trying to create an Access db, I imported Bris data files into Excel first, processed the data into named columns, and then sent it to Access.

On the first pass I downloaded the files in txt format and checked the box that kept the headers in the file so I wouldn't have to define dozens of fields. The column names aren't the greatest, but it sure beats the manual alternative of looking at the record description specs and typing them all in during the learning stage. I haven't tried yet, but there's probably a way to change the names or attributes of the fields to make them more meaningful if I wanted to.

[cj]

Originally Posted by classhandicapper

On the first pass I downloaded the files in txt format and checked the box that kept the headers in the file so I wouldn't have to define dozens of fields. The column names aren't the greatest, but it sure beats the manual alternative of looking at the record description specs and typing them all in during the learning stage. I haven't tried yet, but there's probably a way to change the names or attributes of the fields to make them more meaningful if I wanted to.

Yep, just go in Design View, you can alter names of fields any way you like, attributes too as long as it doesn't exclude data you've already imported.