Database and Excel Spreadsheets

[Dark Horse]

Originally Posted by classhandicapper

Is there a way to display edited fields as part of an Inquiry?

For example, suppose Odds are stored as 100 for 1.00 - 1, 1750 for 17.50 - 1 etc... and I want to display 1.00 and 17.50 etc...

I found that the answer to almost any excel related question can be found by googling it. You'd be amazed at how much people are willing to share. Often a ready made formula can be copied and pasted.

[classhandicapper]

Originally Posted by sjk

You make any expression a field in a query. In this case [odds]/100. Right click on it to get a drop down for format and choose fixed with 2 decimal places.

Yikes, I'm embarrassed I didn't think of that. It's training. I used to be a Cobol programmer (in other words I'm a dinosaur). TY

I'm getting close to starting to write more serious queries now that can actually be used as a learning tool and for information retrieval.

[classhandicapper]

Originally Posted by raybo

Dang, is that a real world example, or just an example of a field you might want displayed differently?

I'm thinking seriously about trying a database again (have failed many times in the past) but I'm going to use Excel as the front end, import all the data into a batch processing Excel routine (both cards and results files at the same time), separate the data by category and send those to different "table" worksheets and format the data their so I don't have to do any of that in the DB (all the former will be automated via macros and formulas), then import those tables from Excel into Access, or what ever DB program I decide to use (having the same table names in the DB as the ones in Excel. Trying to do everything in the DB seems way too complex and time consuming.

Setting up the database and writing basic queries was not that tough. I'm not spending a lot of time on this because I'm busy with work and other things, but I've made a lot of progress already with the help I've been getting here. Writing the tougher queries is likely to be more of a challenge for me, but you have to crawl before you can run.

[classhandicapper]

I want to create a query I can use to evaluate shippers.

Part 1.

1. I have a table with today's Track, Date, Race #, Horse, and previous Track, Date, Race # ......other miscellaneous fields (basically the PP lines for each horse).

2. I am specifically interested in the previous Track in each runner’s most recent race.

3. I only want to select a record if the previous Track is not equal to today's Track in the runner’s most recent race (it will be the first one listed under his name).

4. I want to continue doing that for the entire table.

I tried doing this using Group and First, but it wasn’t just returning a runner's most recent race. It was returning the first one in the group it found that met that criteria of him being a shipper (which could be his 2nd, 3rd, 4th race back). I hope this makes sense. I can probably play with until I get it to work, but if it's a quickie answer I'm all ears.

[sjk]

Create a query to determine the most recent race date for each horse.

Create a query using the above where you join the most recent race date with the previous date in the main data table. Select as criterion that the previous track is not equal to today's track.

In order to get off the ground you are going to have to break things into pieces and use queries to solve for the pieces.

[classhandicapper]

Originally Posted by sjk

Create a query to determine the most recent race date for each horse.

Create a query using the above where you join the most recent race date with the previous date in the main data table. Select as criterion that the previous track is not equal to today's track.

In order to get off the ground you are going to have to break things into pieces and use queries to solve for the pieces.

Thanks

If I create a query to solve the first part do I use then that query as input to the next query or do I create a table out of the first query results and then join it that way?

The good news is that loading all my tables for PPs, Results, and personal stuff is becoming a breeze. A few of the more basic queries I put together are already useful. So things are going well.

Here's one other thing.

I created a query to only select off data for a specific track after getting prompted for the Track ID. It works fine. But now suppose I wanted all of them. Is there a way to code to get all of them or whatever one I enter or do I need a separate virtually identical query to get all of them. (next I am going to code for a date range)

It's not that this stuff is difficult, but when you don't know the techniques and the online help available is either too advanced or not clear you can waste a lot of time experimenting trying to get it to work. lol

[MJC922]

You can enter wildcards in a parameter query. So if you're prompted for the track abbrev and you type a * you should get all tracks.

[sjk]

Originally Posted by classhandicapper

Thanks

If I create a query to solve the first part do I use then that query as input to the next query or do I create a table out of the first query results and then join it that way?

Either one. I would use the query as input. If you next too many queries inside others it slows things down and you make a table to improve performance. You will need a lot more complexity before that becomes an issue.

[classhandicapper]

Thanks guys.

I don't where you guys live, but if you are in NY and want to get together some time at AQU or BEL, let me know. Lunch/beers on me.

[classhandicapper]

Originally Posted by MJC922

You can enter wildcards in a parameter query. So if you're prompted for the track abbrev and you type a * you should get all tracks.

Worked like a charm.

[classhandicapper]

I'm at the stage where I accomplished a mildly more difficult query, but I'm pretty sure I didn't do it in an inefficient way.

1. Query 1 - I looked through some Horse data and calculated a Rating for each horse in each race.

2. Query 2 - I grouped the data from Query 1, by Track, Date, and Race and returned the horse with the highest Rating from each race

3. Query 3 - I went to the Results file displayed the finish position and the payoff (if it won) of the highest rated horse.


Things I want that it can't do without some manual intervention.

1. Total # of races
2. Total number of winners that had the highest rating
3. Sum of win payoffs for the horses that had the highest rating and won

I can get #1 by displaying the results of Query 3 and using one of the Total features

I can get #2 and #3, by only including the winners in Query 3 and using the Total features.

Does any of this make sense?

[sjk]

If I understand you Query 3 returns one row for each race with either a payoff or 0 depending on whether the top rated horse won.

Create query 4 using query 3 as input. Output fields should be expr1:1, expr2:iif([payoff]>0,1,0) and expr3:iif([payoff]>0,[payoff],0).

Use the sigma and set each of the expressions to be the sum. Of course if you cared to see the results by track you could also add a group by field on track.

[sjk]

Number of Races, Number of Wins and Payoff Total are probably better names than Expr1, Expr2 and Expr3.

[classhandicapper]

Originally Posted by sjk

If I understand you Query 3 returns one row for each race with either a payoff or 0 depending on whether the top rated horse won.

Correct.

I'll try what you suggested. I never saw the "iif" function. Are some of these functions available through Query Wizard or do you just have to learn them and code them yourself?

Was the general approach I took of breaking it into 3 queries OK or did I make an easy query more complicated than it had to be?

I'm a little reluctant to go too far down this path only to learn a few months from now that I did everything all ass backwards.

[sjk]

Seems like you are doing fine to me

You might want to look at the avg of iif([payoff]>0,[payoff],-1) as return.