 |
|
06-18-2016, 01:48 PM
|
#361
|
|
EXCEL with SUPERFECTAS
Join Date: Mar 2004
Posts: 10,206
|
Quote:
|
Originally Posted by AltonKelsey
Quick idea, sort them and then pick off the top 4 .
|
I use that in Excel sometimes. Other times I add .000 to the 1st horse's rating, .001 to the 2nd, .003 to the third horse's rating, etc. So, the ratings themselves are not changed significantly, but there can be no ties, which is what I am after.
|
|
|
|
06-18-2016, 02:40 PM
|
#362
|
|
Registered User
Join Date: Dec 2005
Location: MI
Posts: 6,330
|
I use a similar routine. Rank them and then use a vlookup on rank #2, if no rank #2 exist use rank #1.
__________________
"The Law, in its majestic equality, forbids the rich, as well as the poor, to sleep under bridges, to beg in the streets, and to steal bread."
Anatole France
|
|
|
|
|
06-18-2016, 03:09 PM
|
#363
|
|
Registered User
Join Date: Dec 2001
Location: JCapper Platinum: Kind of like Deep Blue... but for horses.
Posts: 5,286
|
Quote:
|
The one I am working on now is looking at a horse's last 5 races. I am attempting to drop off the race with the maximum value in a certain field and return the other 4.
|
Suppose for the sake of argument your table is named MyTable and that it contains the following data fields:
Code:
Field Name Data Type Description
---------- ----------- ----------------------
raceValue double a factor numeric value
---------- ----------- ----------------------
xthRaceBack integer number of races back
1=most recent race
2=second race back, etc.
Something like the following (which uses the TOP and and ORDER BY keywords to sort the query results to return the lowest 4) should do the trick:
SELECT TOP 4 * from MyTable WHERE xthRaceBack BETWEEN 1 AND 5
ORDER BY raceValue ASC
Alternately, if you want the query results to return the highest 4, the following should do the trick:
SELECT TOP 4 * from MyTable WHERE xthRaceBack BETWEEN 1 AND 5
ORDER BY raceValue DESC
-jp
.
__________________
Team JCapper: 2011 PAIHL Regular Season ROI Leader after 15 weeks
www.JCapper.com
|
|
|
|
06-18-2016, 03:38 PM
|
#364
|
|
Registered User
Join Date: Jun 2011
Posts: 588
|
So you can't make another column with an adjusted rank like Ray is saying?
|
|
|
|
|
06-18-2016, 10:04 PM
|
#365
|
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,604
|
Those kinds of manipulations where you add a small incremental value are fairly easy to do in Excel, but not so much in Access.
The other issue is that not all horses actually have 5 starts. Some have only have 1, 2, 3, or 4. That's why I have been using min or max. With min or max I can grab the maximum or minimum value from whatever number of races the horse has had.
The way I have it set up, it will grab 4 of 5 races, 3 of 4 race, 2 of 3 races, 1 of 2 races or the single 1. It just screws up when there are duplicate values.
__________________
"Unlearning is the highest form of learning"
Last edited by classhandicapper; 06-18-2016 at 10:06 PM.
|
|
|
|
|
06-18-2016, 11:43 PM
|
#366
|
|
EXCEL with SUPERFECTAS
Join Date: Mar 2004
Posts: 10,206
|
Quote:
|
Originally Posted by classhandicapper
Those kinds of manipulations where you add a small incremental value are fairly easy to do in Excel, but not so much in Access.
The other issue is that not all horses actually have 5 starts. Some have only have 1, 2, 3, or 4. That's why I have been using min or max. With min or max I can grab the maximum or minimum value from whatever number of races the horse has had.
The way I have it set up, it will grab 4 of 5 races, 3 of 4 race, 2 of 3 races, 1 of 2 races or the single 1. It just screws up when there are duplicate values.
|
Yeah, I understand that Access is not as user friendly as Excel, but the solution is to make sure you never have duplicate values, without changing those values significantly. Duplicate values are a problem in Excel also, but if you make sure there is no chance of duplicate values, in the first place, then you never have to worry about those duplicates causing problems when ranking or selecting them. I guess what I'm saying is that the solution has to take place during the value calculation processes, rather than in the ranking/selection processes.
|
|
|
|
|
06-19-2016, 07:39 AM
|
#367
|
|
Registered User
Join Date: Apr 2006
Posts: 278
|
Quote:
|
Originally Posted by classhandicapper
It's a new day and I have a new problem. 
I have a bunch of queries written that will select a horse's last race, last 2 races, last 3 races etc.... all the way to last 10 races. I use whichever is relevant for each specific application.
The one I am working on now is looking at a horse's last 5 races. I am attempting to drop off the race with the maximum value in a certain field and return the other 4.
It works fine for example, if the 5 races have values of 1, 2, 3, 4 ,5. It will return the records with a value of 1, 2, 3, 4. However, if the value are 1, 2, 3, 4, 4, it will drop of BOTH of the 4s and return 1, 2, 3. It may be that I don't have a very good approach and need a new one, or perhaps there's a way of working with the Min and Max functions to return what I am looking for.
The other thing I tried to do was create a kind of counter field to concatenate with my significant field that would differentiate between the two 4s and only drop one, but at counter field is much easier to work with tables than queries. It turned into a mess.
Is there a technique for doing this kind of thing?
I think I need a fresh set of eyes and a fresh brain before I go too far off on a tangent accomplishing this in a difficult way when there is an easier solution. |
select HORSE,ROW_NUMBER() over (PARTITION BY HORSE order by RACE_DATE desc) as LAST_START
Use where here to select out the values.
Subquery that and select LAST_START in (1,2,3,4)
Last edited by o_crunk; 06-19-2016 at 07:40 AM.
|
|
|
|
|
06-19-2016, 08:23 AM
|
#368
|
|
Registered User
Join Date: Feb 2003
Posts: 2,105
|
Append the data to a new table with a counter field. Choose the sorts so that the line you wish to exclude is always last and use the counter as criterion to exclude the line in your query.
Of course the indexed table will need remade whenever the dataset changes.
|
|
|
|
|
06-19-2016, 09:38 AM
|
#369
|
|
Registered User
Join Date: Dec 2005
Location: MI
Posts: 6,330
|
Quote:
|
Originally Posted by Capper Al
I use a similar routine. Rank them and then use a vlookup on rank #2, if no rank #2 exist use rank #1.
|
Is this not complicated enough for you? I also use the other method mentioned that adds a small fraction to each number. Both of these are easy solutions.
__________________
"The Law, in its majestic equality, forbids the rich, as well as the poor, to sleep under bridges, to beg in the streets, and to steal bread."
Anatole France
|
|
|
|
|
06-20-2016, 04:40 PM
|
#370
|
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,604
|
Quote:
|
Originally Posted by sjk
Append the data to a new table with a counter field. Choose the sorts so that the line you wish to exclude is always last and use the counter as criterion to exclude the line in your query.
Of course the indexed table will need remade whenever the dataset changes.
|
Are you talking about creating a table that has a field with datatype AutoNumber?
I use that technique to help me get last 2 races, last 3 races, last 4 races etc... but it can get a little tedious if you aren't careful.
Every time I run those queries I feel like I have to double check the output before I continue. Maybe there's a better way to do it, but with Access I find that "if it's not broken don't fix it". I've had way too many time consuming disasters trying to streamline or improve a process that was already working.
I can't believe there isn't some really simple way to do this. You would think this comes up constantly when people are working with minimum and maximum values.
__________________
"Unlearning is the highest form of learning"
Last edited by classhandicapper; 06-20-2016 at 04:47 PM.
|
|
|
|
|
06-23-2016, 12:28 PM
|
#371
|
|
Registered User
Join Date: Feb 2003
Posts: 2,105
|
Code:
NAME PDATE SP RATING
ABRAN PASO 2/21/2016 -4.37635689419911
ABRAN PASO 1/31/2016 25.2828596536973
ABRAN PASO 1/16/2016 41.8820448577677
ABRAN PASO 2/22/2015 19.9778323135338
ABRAN PASO 10/27/2014 38.0443862398989
ACCELERATO 6/16/2016 39.9416428655098
ACCELERATO 5/30/2016 31.7067170449216
ACTION AMOUR 6/2/2016 34.4451868100831
ACTION AMOUR 3/29/2015 20.6270253522822
ACTION AMOUR 1/16/2015 24.6326183224922
AFTER THE GOLDRUSH 5/29/2016 34.3078001232938
AFTER THE GOLDRUSH 5/6/2016 41.9937828020845
AFTER THE GOLDRUSH 4/9/2016 23.5772453411666
AFTER THE GOLDRUSH 3/19/2016 54.8028873107904
AFTER THE GOLDRUSH 1/10/2016 56.0233139527162
ALBANO 5/21/2016 90.8337854918294
ALBANO 8/30/2015 101.945145362918
ALBANO 8/1/2015 85.8306008801825
ALBANO 5/30/2015 77.6737642485744
ALBANO 3/28/2015 101.994671418633
Code:
ctr NAME PDATE SP RATING
918 ABRAN PASO 1/16/2016 41.8820448577677
919 ABRAN PASO 10/27/2014 38.0443862398989
920 ABRAN PASO 1/31/2016 25.2828596536973
921 ABRAN PASO 2/22/2015 19.9778323135338
922 ABRAN PASO 2/21/2016 -4.37635689419911
923 ACCELERATO 6/16/2016 39.9416428655098
924 ACCELERATO 5/30/2016 31.7067170449216
925 ACTION AMOUR 6/2/2016 34.4451868100831
926 ACTION AMOUR 1/16/2015 24.6326183224922
927 ACTION AMOUR 3/29/2015 20.6270253522822
928 AFTER THE GOLDRUSH 1/10/2016 56.0233139527162
929 AFTER THE GOLDRUSH 3/19/2016 54.8028873107904
930 AFTER THE GOLDRUSH 5/6/2016 41.9937828020845
931 AFTER THE GOLDRUSH 5/29/2016 34.3078001232938
932 AFTER THE GOLDRUSH 4/9/2016 23.5772453411666
933 ALBANO 3/28/2015 101.994671418633
934 ALBANO 8/30/2015 101.945145362918
935 ALBANO 5/21/2016 90.8337854918294
936 ALBANO 8/1/2015 85.8306008801825
937 ALBANO 5/30/2015 77.6737642485744
Code:
NAME MaxOfctr
ABRAN PASO 922
ACCELERATO 924
ACTION AMOUR 927
AFTER THE GOLDRUSH 932
ALBANO 937
Code:
MaxOfctr NAME PDATE SP RATING
ABRAN PASO 1/31/2016 25.2828596536973
ABRAN PASO 1/16/2016 41.8820448577677
ABRAN PASO 2/22/2015 19.9778323135338
ABRAN PASO 10/27/2014 38.0443862398989
ACCELERATO 6/16/2016 39.9416428655098
ACTION AMOUR 6/2/2016 34.4451868100831
ACTION AMOUR 1/16/2015 24.6326183224922
AFTER THE GOLDRUSH 5/29/2016 34.3078001232938
AFTER THE GOLDRUSH 5/6/2016 41.9937828020845
AFTER THE GOLDRUSH 3/19/2016 54.8028873107904
AFTER THE GOLDRUSH 1/10/2016 56.0233139527162
ALBANO 5/21/2016 90.8337854918294
ALBANO 8/30/2015 101.945145362918
ALBANO 8/1/2015 85.8306008801825
ALBANO 3/28/2015 101.994671418633
It took 5-10 minutes to write a few queries to do what I suggested and I could put those in a macro that would perform all the actions in a few seconds any day I wanted to.
I have dozens of tables that are recalculated each day and that I have not looked at in 15 years. They always calculate correctly.
Last edited by sjk; 06-23-2016 at 12:29 PM.
|
|
|
|
|
06-23-2016, 12:59 PM
|
#372
|
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,604
|
Quote:
|
Originally Posted by sjk
Code:
NAME PDATE SP RATING
ABRAN PASO 2/21/2016 -4.37635689419911
ABRAN PASO 1/31/2016 25.2828596536973
ABRAN PASO 1/16/2016 41.8820448577677
ABRAN PASO 2/22/2015 19.9778323135338
ABRAN PASO 10/27/2014 38.0443862398989
ACCELERATO 6/16/2016 39.9416428655098
ACCELERATO 5/30/2016 31.7067170449216
ACTION AMOUR 6/2/2016 34.4451868100831
ACTION AMOUR 3/29/2015 20.6270253522822
ACTION AMOUR 1/16/2015 24.6326183224922
AFTER THE GOLDRUSH 5/29/2016 34.3078001232938
AFTER THE GOLDRUSH 5/6/2016 41.9937828020845
AFTER THE GOLDRUSH 4/9/2016 23.5772453411666
AFTER THE GOLDRUSH 3/19/2016 54.8028873107904
AFTER THE GOLDRUSH 1/10/2016 56.0233139527162
ALBANO 5/21/2016 90.8337854918294
ALBANO 8/30/2015 101.945145362918
ALBANO 8/1/2015 85.8306008801825
ALBANO 5/30/2015 77.6737642485744
ALBANO 3/28/2015 101.994671418633
Code:
ctr NAME PDATE SP RATING
918 ABRAN PASO 1/16/2016 41.8820448577677
919 ABRAN PASO 10/27/2014 38.0443862398989
920 ABRAN PASO 1/31/2016 25.2828596536973
921 ABRAN PASO 2/22/2015 19.9778323135338
922 ABRAN PASO 2/21/2016 -4.37635689419911
923 ACCELERATO 6/16/2016 39.9416428655098
924 ACCELERATO 5/30/2016 31.7067170449216
925 ACTION AMOUR 6/2/2016 34.4451868100831
926 ACTION AMOUR 1/16/2015 24.6326183224922
927 ACTION AMOUR 3/29/2015 20.6270253522822
928 AFTER THE GOLDRUSH 1/10/2016 56.0233139527162
929 AFTER THE GOLDRUSH 3/19/2016 54.8028873107904
930 AFTER THE GOLDRUSH 5/6/2016 41.9937828020845
931 AFTER THE GOLDRUSH 5/29/2016 34.3078001232938
932 AFTER THE GOLDRUSH 4/9/2016 23.5772453411666
933 ALBANO 3/28/2015 101.994671418633
934 ALBANO 8/30/2015 101.945145362918
935 ALBANO 5/21/2016 90.8337854918294
936 ALBANO 8/1/2015 85.8306008801825
937 ALBANO 5/30/2015 77.6737642485744
Code:
NAME MaxOfctr
ABRAN PASO 922
ACCELERATO 924
ACTION AMOUR 927
AFTER THE GOLDRUSH 932
ALBANO 937
Code:
MaxOfctr NAME PDATE SP RATING
ABRAN PASO 1/31/2016 25.2828596536973
ABRAN PASO 1/16/2016 41.8820448577677
ABRAN PASO 2/22/2015 19.9778323135338
ABRAN PASO 10/27/2014 38.0443862398989
ACCELERATO 6/16/2016 39.9416428655098
ACTION AMOUR 6/2/2016 34.4451868100831
ACTION AMOUR 1/16/2015 24.6326183224922
AFTER THE GOLDRUSH 5/29/2016 34.3078001232938
AFTER THE GOLDRUSH 5/6/2016 41.9937828020845
AFTER THE GOLDRUSH 3/19/2016 54.8028873107904
AFTER THE GOLDRUSH 1/10/2016 56.0233139527162
ALBANO 5/21/2016 90.8337854918294
ALBANO 8/30/2015 101.945145362918
ALBANO 8/1/2015 85.8306008801825
ALBANO 3/28/2015 101.994671418633
It took 5-10 minutes to write a few queries to do what I suggested and I could put those in a macro that would perform all the actions in a few seconds any day I wanted to.
I have dozens of tables that are recalculated each day and that I have not looked at in 15 years. They always calculate correctly. |
This is what has happened to me in the past.
I'd create a table with an autonumber field that had all the data sorted correctly. My process would work fine. Somewhere along the line I'd go into that table and resort the data manually to do some research. When I'd exit, I'd accidentally save it. Next time the process ran it would create the next set of records in the new (and incorrect) sort sequence and the autonumber field would be screwed up. Then I'd have to regenerate the whole thing the correct way. Naturally, that's my fault for being careless. But I move in and out of tables manually all the time looking things up and sometimes forget to not save it if the sort sequence is critical.
I was able to create the table the way you suggested the other night very quickly. I'm sure it will work fine when I'm done developing all the queries in the sequence....until I screw up the sort again.
I probably should just put a sort into the sequence of queries to make sure it's always right before I add any new records.
__________________
"Unlearning is the highest form of learning"
Last edited by classhandicapper; 06-23-2016 at 01:03 PM.
|
|
|
|
|
06-23-2016, 01:07 PM
|
#373
|
|
Registered User
Join Date: Feb 2003
Posts: 2,105
|
Glad to hear it is coming together.
|
|
|
|
|
06-24-2016, 08:45 AM
|
#374
|
|
Registered User
Join Date: Mar 2005
Location: Queens, NY
Posts: 20,604
|
Quote:
|
Originally Posted by sjk
Glad to hear it is coming together.
|
Thanks for all the help.
I'm still at the stage where I'm spending a lot of time developing reports, applications, and research queries to enhance my information. I keep coming up with new ideas. When that finally stabilizes I should have more time to expand wagering opportunities.
__________________
"Unlearning is the highest form of learning"
|
|
|
|
|
06-24-2016, 09:03 AM
|
#375
|
|
Registered User
Join Date: Dec 2005
Location: MI
Posts: 6,330
|
Quote:
|
Originally Posted by classhandicapper
Thanks for all the help.
I'm still at the stage where I'm spending a lot of time developing reports, applications, and research queries to enhance my information. I keep coming up with new ideas. When that finally stabilizes I should have more time to expand wagering opportunities.
|
Sorry to tell you this, it never ends. More info ends up with more questions.
__________________
"The Law, in its majestic equality, forbids the rich, as well as the poor, to sleep under bridges, to beg in the streets, and to steal bread."
Anatole France
|
|
|
|
|
|
| Thread Tools |
|
|
| Rate This Thread |
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
