Database and Excel Spreadsheets

[classhandicapper]

Originally Posted by MJC922

Let's start by getting the ranking query right first. Please try something like:

SELECT (select count(*) from MyTable as MyTable2 where
MyTable.MyFieldToBeRanked <= MyTable2.MyFieldToBeRanked and MyTable.Track = MyTable2.Track and MyTable.NewDate = MyTable2.NewDate and MyTable.Race = MyTable2.Race) AS Rank, *
FROM MyTable;


Then create another query which uses all of the fields from the ranking query as its source with <=5 criteria set on the rank field.

I solved all my prior problems, now I have a new ones. I am searching for a better technique than ranking because that's too slow for a large sample.

I have a query that looks through my entire database and selects off each horse's last 5 races heading into any race (or less if it had fewer starts). Once I have those 5 races, I use another query to calculate a rating for each race.

So far so good.

Here's the tricky part.

I want to weight each of a horses last 5 races (or less if it had fewer starts) differently depending on whether it was the horse's last race, next to last race, 2nd to last etc...

My first idea was to rank each by date (1-5). Then I could look at the rank and always know which was the last one, next to last one and do the calculation. But given that the ranking query is an unworkable monster, I need a different technique.

Is there a standard technique I can use for something like that?

I am probably going to run this application fairly often in the beginning until I get the results I am looking by experimenting with the weights.

[classhandicapper]

Originally Posted by classhandicapper

Is there a standard technique I can use for something like that?

It's very easy to do ranking in Excel. As a last resort I could always export the data to Excel, manually add in a couple of columns with the ranking and weighting formulas, and then import it back to an Access table.

If that's the best I can do, is there any way to automate the export (easy with a macro), the spreadsheet updates (not so easy), and then an import back to a table (easy with a macro)?

[AltonKelsey]

Thought some programmers might be amused by this.

I've got an Excel database program that I've worked on for years. Some time ago , maybe over a year or so, I added some code that ranked the odds just to see what 'choice' a horse was on my screen.

No biggie. But I noticed a problem today and went back to the sheet (there are many in the workbook) where the calc is done, and noticed very slow scrolling when moving around the sheet. High cpu use too.

By process of elimination, I finally narrowed the problem down to conditional formatting that was being copied along with a row of data, every race.

Excel is pretty dumb about this apparently, so every time i copied the row, it created a duplicate of the format . Needed to copy and paste special, values only, to avoid this.

So there was supposed to be only 1 conditional format but over time there was ............................ 11,500.

A miracle the workbook didn't melt down long ago, but seems if the sheet is not visible (this one works in the background) then conditional formatting doesn't actually execute.

PS The really funny part is, the original problem that took me back to the sheet for debugging, had NOTHING to do with this major issue.

[classhandicapper]

Interesting one.

[BrownBear]

Originally Posted by classhandicapper

Here's the tricky part.

I want to weight each of a horses last 5 races (or less if it had fewer starts) differently depending on whether it was the horse's last race, next to last race, 2nd to last etc...

My first idea was to rank each by date (1-5). Then I could look at the rank and always know which was the last one, next to last one and do the calculation. But given that the ranking query is an unworkable monster, I need a different technique.

Is there a standard technique I can use for something like that?

Assign a "Delay Period" or "Days Since" calculated from the date of the form line (or historic race) to the date of the race. If you're using access, I believe you can use the DateDiff function to automate this process. This will return an integer, or number of days between the two dates. Alternatively, you can encode it with the date as YYYYMMDD in a field. Once there is an order you can use that in a query with ORDER BY. If you want to add an ordinal order, then you can use the same sorting or ordering method in a query and assign a value 1 to N.

SELECT * FROM RaceHistory WHERE HorseName = "BOB" ORDER BY DelayPeriod ASC

The above would return all of the runners races starting from the most recent.

Not sure if that points you in the right direction, but hope it helps.

[classhandicapper]

bump

just wanted to back and reread some of the notes in this thread.

[jay68802]

Do not know if anyone is doing this. I started this about 2 yrs ago and find it helpful. Each day and race come with 2 "numbers". One number is date specific and one is race specific. The one that is date specific is just a count of each running style that won on that day. E, EP, P, and C. so a date that looks like 6300 would simply mean that there were 6 wire to wire winners and 3 winners that were within 1 length of the lead. The number that is race specific is a count of the number of horses within a length of the lead at the first, second and stretch call of sprints, and the the 4f, 6f, and stretch call of routes. I think it helps to judge a horses running line.

[Dave Schwartz]

Originally Posted by jay68802

Do not know if anyone is doing this. I started this about 2 yrs ago and find it helpful. Each day and race come with 2 "numbers". One number is date specific and one is race specific. The one that is date specific is just a count of each running style that won on that day. E, EP, P, and C. so a date that looks like 6300 would simply mean that there were 6 wire to wire winners and 3 winners that were within 1 length of the lead. The number that is race specific is a count of the number of horses within a length of the lead at the first, second and stretch call of sprints, and the the 4f, 6f, and stretch call of routes. I think it helps to judge a horses running line.

I really like this idea.

This is going in my new software. Beta version in May (I hope).

[classhandicapper]

Originally Posted by jay68802

Do not know if anyone is doing this. I started this about 2 yrs ago and find it helpful. Each day and race come with 2 "numbers". One number is date specific and one is race specific. The one that is date specific is just a count of each running style that won on that day. E, EP, P, and C. so a date that looks like 6300 would simply mean that there were 6 wire to wire winners and 3 winners that were within 1 length of the lead. The number that is race specific is a count of the number of horses within a length of the lead at the first, second and stretch call of sprints, and the the 4f, 6f, and stretch call of routes. I think it helps to judge a horses running line.

I like it.

I don't have something that automatically updates a field like that, but I have queries that I can run that will look at a Track, Date Range, Surface, Distance and quickly build a profile of W2W winners, average early position of winner, and things like that.

[Track Collector]

Here is a question from someone who can use Access at a very basic level only.

I have an Access table where I want to extract a sub-set of information for further study. I want to look at all the records which meet a specific date, track, and race# criteria. For example:

1/3/2018 , CTX , 4
1/7/2018, Sun , 7
1/8/2018, Tam , 1
1/8/2018, TuP , 5

(For point of reference, the above criteria is composed of 4 different "data sets").

I have created a data query where I manually input a limited number of data sets, run the query, then copy and paste the returned info into an excel spreadsheet. I then close out the query, re-open, enter in different data sets, run the query, rinse and repeat until all the different data sets have been entered and data returned. This process often takes hours as the number of different data sets number in the hundreds.

Is there a way to batch load the entire list of data sets rather than manually loading them into the query. To help, I do have a way of getting the list of different data sets into an excel file with the date, track, and race# in their own separate columns. (Not sure though how to easily convert all the dates like 1/3/2018 to #1/3/2018# which is the required Access format.).

Thanks for any help!

[cj]

Originally Posted by Track Collector

Here is a question from someone who can use Access at a very basic level only.

I have an Access table where I want to extract a sub-set of information for further study. I want to look at all the records which meet a specific date, track, and race# criteria. For example:

1/3/2018 , CTX , 4
1/7/2018, Sun , 7
1/8/2018, Tam , 1
1/8/2018, TuP , 5

(For point of reference, the above criteria is composed of 4 different "data sets").

I have created a data query where I manually input a limited number of data sets, run the query, then copy and paste the returned info into an excel spreadsheet. I then close out the query, re-open, enter in different data sets, run the query, rinse and repeat until all the different data sets have been entered and data returned. This process often takes hours as the number of different data sets number in the hundreds.

Is there a way to batch load the entire list of data sets rather than manually loading them into the query. To help, I do have a way of getting the list of different data sets into an excel file with the date, track, and race# in their own separate columns. (Not sure though how to easily convert all the dates like 1/3/2018 to #1/3/2018# which is the required Access format.).

Thanks for any help!

Can you put all the data in Excel then filter by the above? I use Access all the time, not sure I can think of a way to do what you are looking for that would take less time.

[Track Collector]

Originally Posted by cj

Can you put all the data in Excel then filter by the above?

Unfortunately not a time-saving option vs. the current Access Query method.

[classhandicapper]

Originally Posted by Track Collector

Here is a question from someone who can use Access at a very basic level only.

I have an Access table where I want to extract a sub-set of information for further study. I want to look at all the records which meet a specific date, track, and race# criteria. For example:

1/3/2018 , CTX , 4
1/7/2018, Sun , 7
1/8/2018, Tam , 1
1/8/2018, TuP , 5

(For point of reference, the above criteria is composed of 4 different "data sets").

I have created a data query where I manually input a limited number of data sets, run the query, then copy and paste the returned info into an excel spreadsheet. I then close out the query, re-open, enter in different data sets, run the query, rinse and repeat until all the different data sets have been entered and data returned. This process often takes hours as the number of different data sets number in the hundreds.

Is there a way to batch load the entire list of data sets rather than manually loading them into the query. To help, I do have a way of getting the list of different data sets into an excel file with the date, track, and race# in their own separate columns. (Not sure though how to easily convert all the dates like 1/3/2018 to #1/3/2018# which is the required Access format.).

Thanks for any help!

I'm not sure I fully understand what you are doing.

You can batch load an Excel file into a table and then use the table as input to the query. I do that all the time with Track, Date, Race# etc...

The date format is not an issue. I can walk you through that if I understand what you are doing better.

Is the type of information you are inputting different for each of these passes through the query?

If so, maybe you could you add a "Record Type" field to each of the datasets so you know what you are dealing with. Then you could make them one big input into a table and run the Query multiple times by record type.

If the information is always the same basic fields, then the idea would be to combine it before you put it into Access as a table.

[sjk]

If I understand you correctly just make a table out of the list of dates, tracks and races that you want to query.

Create another query with the original query and the new table where the fields are joined.

The results of this query would seem to be what you need.

[Track Collector]

Originally Posted by sjk

If I understand you correctly just make a table out of the list of dates, tracks and races that you want to query.

Create another query with the original query and the new table where the fields are joined.

The results of this query would seem to be what you need.

It was not clear to me exactly what you proposed, but I played around, and now it looks like I have what I want in a much quicker process.

THANK YOU!!!