Database and Excel Spreadsheets

[raybo]

Originally Posted by cj

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.

Can you explain the bolded part?

[sjk]

You will want to take care that fields like date, track, race # in one table have the same meaning in two tables you are joining. If the fields in one table refer to the upcoming race (11/1) and the fields in the other table refer to the attributes of the past races these may not be what you want to join.

You may want to incorporate the date, track, race information for the upcoming race into the other tables so as to be able to recover all of the data through joins.

You are not stuck with the table as you import it. You can use make table and append queries to add additional fields in a new and potentially more useful table.

[MJC922]

sjk touched on one thing that I have found very important to the way I use Access. That is, I import the data from the file into a table pretty much raw, and that's what I essentially call a 'staging' table -- after that I use make table queries against that staging table to get the fields into a brand new table, maybe even into a brand new db, a table which will contain the data the way I want and need the data to be for whatever my specific plans are.

That make table query might involve dozens of calculated fields I've taken the time to set up to do all sorts of things e.g. to get the times into seconds, segments, velocity, pull fields in from other tables whatever.

A basic example, you mentioned that you have a date field a track field and a race field in the staging table, well maybe I want in addition to those a field in the new table that looks like the traditional 22Sep14 9PRX we're so accustomed to seeing. And maybe I want to join on that field later on in a query of some sort as opposed to joining on those three separate fields.

Why? keep in mind performance considerations, three separate joins create more overhead. If you can do it with one join that might make all the difference in getting a query to complete when the db gets large. Indexing can become critically important to query performance, as far as I can recall a join that is only made between tables at the query level is unable index on the join as opposed to if you have set up an actual relationship between the tables on indexed fields.

There are db architects who would scoff at storing anything that can be derived from a calculated field in the database. I pay no mind to any of that. Explore and find through experience what works for you. Please make backup copies of your db all the time too, at least daily when you're running all of these action queries, if something gets messed up you need a way to go back. Good luck.

[Tom]

Class, you can also copy and save as a renamed file, and check structure only.
then make changes and add keys, then re-import data and eliminate duplicates.

[cj]

Originally Posted by raybo

Can you explain the bolded part?

Example would be trying to change a long integer field to integer when already has long integers in it, or restrict a string 10 characters when there are larger in the table already.

[raybo]

Originally Posted by cj

Example would be trying to change a long integer field to integer when already has long integers in it, or restrict a string 10 characters when there are larger in the table already.

Thanks CJ! Yeah, forgot about all the data types you have to get right in a DB (for performance enhancement and memory savings, if I remember right), sure am glad I don't have to do that stuff in Excel.

[classhandicapper]

Originally Posted by MJC922

That make table query might involve dozens of calculated fields I've taken

Speaking of calculated fields, suppose I wanted to write a query that looked at various combinations of Beyer Figures.

1. Last Race
2. Best of last 2
3. Best of Last 3
4. various averages
5. etc

The goal would be to scan through the database and see how a few rules, (Beyer figures rule among them), performed.

Is something like that easier to code in a query itself or would you create a new field that could automatically be updated with the appropriate values in one of the databases?

[JJMartin]

Originally Posted by classhandicapper

Speaking of calculated fields, suppose I wanted to write a query that looked at various combinations of Beyer Figures.

1. Last Race
2. Best of last 2
3. Best of Last 3
4. various averages
5. etc

The goal would be to scan through the database and see how a few rules, (Beyer figures rule among them), performed.

Is something like that easier to code in a query itself or would you create a new field that could automatically be updated with the appropriate values in one of the databases?

You would probably need to create an additional column that would distinguish and rank factors for each individual race but I only work with Excel so its just a guess.

[MJC922]

Tough questions like this already? Damn. There are many ways to skin a cat. Some SQL expert can probably give you much better advice than I will in this case. A relatively straightforward way though is to use Excel. I'm not averse to shuffling data back and forth between the apps for research purposes, these two apps are made to compliment one another. Your query on the Access-side is easy, using a simple select query, for this example we'll choose three fields, we'll use HorseName, DateOfRace, BeyerFig, (for clarity on my own figs it's called PerfPriorRace1 not BeyerFig, doesn't matter though) just set the sorting in the query to HorseName Ascending, DateOfRace descending. Run the query. When it completes, click the external data tab, click the Excel button, choose to keep formatting etc, blast that out to Excel.

HorseName	DateOfRace	PerfPriorRace1
Gather No Moss	17-Nov-12	22
Gather No Moss	20-Oct-12	23
Gather No Moss	06-Oct-12	18.75
Gather No Moss	31-Mar-12	15.75
Gather No Moss	19-Nov-11	21.25

So now I'm in Excel looking at columns A,B,C above and on my rating scale it's sheet-style i.e. the lower the better, for you it's Beyer-style so you would use MAX instead of MIN:

cell D1 is just a label type: BestOfLast2

In cell D2 use the formula: =IF($A2=$A3,MIN($C2:$C3),"")

Fill that down to the bottom

This means if the horse name is the same in cell A2 & A3 then return the Min of C2 to C3 otherwise leave it blank.

cell E1 is just a label : BestOfLast3

In cell E2 use the formula: =IF($A2=$A4,MIN($C2:$C4),"")

Fill down -- I think you get the idea from here. It's not elegant in the least, it's brute force, but who cares you're getting things done. IMO the object is to get the data into the format you want it to be in for research purposes not to win design points on the way. I'm not an expert in apps or code but I sure as hell don't give up easy.

Now back to Access, create a new blank Access db, click the external data tab, where it says Import and Link, click Excel. Import it back as a new table. You may have to go into table design and change some field types to numeric but I think this gets you just about where you want to be.

Surely there are many other paths to get to this point, Autonumber fields linking up to self-joins, vba functions, etc, you can even use Excel functions inside of Access vba code if the correct references are configured.

[JJMartin]

Originally Posted by MJC922

HorseName	DateOfRace	PerfPriorRace1
 Gather No Moss	17-Nov-12	22
 Gather No Moss	20-Oct-12	23
 Gather No Moss	06-Oct-12	18.75
 Gather No Moss	31-Mar-12	15.75
 Gather No Moss	19-Nov-11	21.25

So now I'm in Excel looking at columns A,B,C above and on my rating scale it's sheet-style i.e. the lower the better, for you it's Beyer-style so you would use MAX instead of MIN:

cell D1 is just a label type: BestOfLast2

In cell D2 use the formula: =IF($A2=$A3,MIN($C2:$C3),"")

With that formula it will also output the min of the 3rd and 4th row in row 3 and so on and that is fine if that's what you want but if you just want to isolate the last 2 races,you could amend it like this:
=IF(and($A2=$A3,$A2<>$A1),MIN($C2:$C3),"")

This will only show 1 figure per horse's record.

[MJC922]

Originally Posted by JJMartin

With that formula it will also output the min of the 3rd and 4th row in row 3 and so on and that is fine if that's what you want but if you just want to isolate the last 2 races,you could amend it like this:
=IF(and($A2=$A3,$A2<>$A1),MIN($C2:$C3),"")

This will only show 1 figure per horse's record.

Yes it's definitely what I personally want but for reasons left out of the example, it's also about as far as I'm going to go... examples on a forum aren't easy to provide and some things are better left to gain through experience. Thanks for providing another formula though, I'm sure there are people out there who will prefer yours for their own reasons.

[sjk]

It is pretty easy to do in Access if you break the problem into pieces. Often you will want to create a series of queries to answer a question and this is particularly true if you are dealing with last, last 2, etc.

1). You will want the data to be sorted by starter and prior race date descending and in a table with a counter. If you data is not set up this way append the data to a table that is.

2). Find the minimum counter associated with each horse to identify the last race.

3). Join that query on the horse name to the data table and set the criterion that the counter in the data table is equal to the minimum counter (for last race only), equal to that or the next greater value (for last two) etc.

4). Create a query that uses the data selected by the previous and does the required calculation of the speed figure or other data point, ie max or avg.

Bear in mind that if you nest very many queries things will bog down and the solve for this is to use make table queries to flatten the data (and if there are many steps involved a macro or code to run through a sequence of such actions).

[MJC922]

Originally Posted by sjk

It is pretty easy to do in Access if you break the problem into pieces. Often you will want to create a series of queries to answer a question and this is particularly true if you are dealing with last, last 2, etc.

1). You will want the data to be sorted by starter and prior race date descending and in a table with a counter. If you data is not set up this way append the data to a table that is.

2). Find the minimum counter associated with each horse to identify the last race.

3). Join that query on the horse name to the data table and set the criterion that the counter in the data table is equal to the minimum counter (for last race only), equal to that or the next greater value (for last two) etc.

4). Create a query that uses the data selected by the previous and does the required calculation of the speed figure or other data point, ie max or avg.

Bear in mind that if you nest very many queries things will bog down and the solve for this is to use make table queries to flatten the data (and if there are many steps involved a macro or code to run through a sequence of such actions).

Nice work, glad you posted it. This was what I was on about in the last bit at the end with the autonumber (as a counter) and the self-join to the next highest. Lots of ways to skin cats, and frankly to me that's part of the fun, creative solutions to problems, though I can see where for others it's nothing more than frustration and throwing in the towel.

[classhandicapper]

Thanks guys. That should keep me thinking for awhile.

[classhandicapper]

This is both related and unrelated to the previous question.

1. I have a table. It just has the horse's name, date of race, race # and some general information about the horse on that specific date.

2. I'd like to physically add several fields to that table because that's where they logically belong.

3. I know I can physically create the new fields in that table.

4. If I have the data that belongs in those new fields in an Excel spreadsheet, can I somehow import that data into the appropriate fields in the table without destroying what's already there?