Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2019, 09:12 AM   #31
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 32
Thanks: 5
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Re: Lookup the value from a previous record

Really appreciate this CJ, I really do. I've been working on the VBA this afternoon and managed to find a useful post from "asc" about half way down this page: https://forum.punterslounge.com/topi...nction/page/2/

Interestingly his data looks like it is formatted in the same way as my Excel, i.e. both the Winner and the Loser are on the same row. Do you think that's a hindrance in anyway? The main database is also structured like that...

If you're stacked please don't worry about replying to this quickly - I've got loads to work on with your recommendations and the VBA. Thanks again.

Jossy is offline   Reply With Quote
Old 08-13-2019, 09:29 AM   #32
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup the value from a previous record

Quote:
both the Winner and the Loser are on the same row. Do you think that's a hindrance in anyway?
you can do it that way, but it makes for more complicated queries. Don't confuse presentation with table design which is the Excel way. tables are for data, queries/forms/reports for presentation. If you look at qryCalcNewELO you will see the data is presented with winner and loser on the same row
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Jossy (08-13-2019)
Old 08-13-2019, 01:48 PM   #33
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 32
Thanks: 5
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Re: Lookup the value from a previous record

Thanks CJ. Will have a look at doing it this way.

Jossy is offline   Reply With Quote
Old 08-18-2019, 09:33 AM   #34
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 32
Thanks: 5
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Re: Lookup the value from a previous record

Quote:
Originally Posted by CJ_London View Post
please find attached my attempt to help get you going. The data is based on your spreadsheet less a number of duplicate records such as these

16/11/2018 Nitto ATP Finals - London Novak Djokovic Marin Cilic
16/11/2018 Nitto ATP Finals - London Novak Djokovic Marin Cilic
16/11/2018 Nitto ATP Finals - London Novak Djokovic Marin Cilic
14/11/2018 Nitto ATP Finals - London Novak Djokovic Alexander Zverev
14/11/2018 Nitto ATP Finals - London Novak Djokovic Alexander Zverev
14/11/2018 Nitto ATP Finals - London Novak Djokovic Alexander Zverev
12/11/2018 Nitto ATP Finals - London Novak Djokovic John Isner
12/11/2018 Nitto ATP Finals - London Novak Djokovic John Isner
12/11/2018 Nitto ATP Finals - London Novak Djokovic John Isner

it is not complete by far but hopefully will point you in the right direction

you need to write a vba function to loop through the game dates (earliest first) and then an update query to update eloPlayers. Manually, when you open qryCalcNewELO you will be prompted for a date - enter 16/1/18 to replicate the last row of your spreadsheet. You can use the newELO values to base an update query to update ELO fields in the players table. Then run the query again for the next date (18/1/18). and so on. I would estimate it should only take a couple of seconds to run.

Some of your hard coded values do not relate to the formula. for example on the last row you have hardcoded the P2K value as 122.1 whereas the calculation returns 131.3

Best I can do for you - now need to focus on work that pays
Hi CJ. Firstly - thank you so much for building this - it must have taken a while. I really do appreciate it. I've also learnt loads from looking at the table and query structures and reading the SQL, e.g. aliases, Nz functions to name a couple.

Have a few questions if you don't mind?

1. Plugging in 16/1/18 works perfectly however plugging in 18/1/18 (or any other subsequent date) still returns the data for the match on 16/1/18. Am I doing something wrong?

As I've not been able to follow through the logic then I apologise for a few of these questions - I might have been able to figure them out...

2. I'm not so used to update queries - are you recommending that I build an update query based on qryNewCalcELO? Or should I run qryNewCalcELO and then an update query off the results?

3. Looking at the SQL for qryNewCalcELO it selects only Djokovic - I assume that I'd remove this in the full version as it will need to calculate ELO scores match by match for both players?

4. Also on the SQL for qryNewCalcELO it uses TOP 1 - wouldn't this mean it will only select the top record for that date? Surely I'll want to pull all match records for that date and then update eloPlayers with the results?
Jossy is offline   Reply With Quote
Old 08-18-2019, 02:24 PM   #35
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup the value from a previous record

It's a while since I looked at this, but working from memory

Quote:
1. Plugging in 16/1/18 works perfectly however plugging in 18/1/18 (or any other subsequent date) still returns the data for the match on 16/1/18. Am I doing something wrong?
assuming you are using my example, no - the reason for the date is to force the query to display the earliest record and show the calc for PC and K factors to prove the calculation works. If you put it 18/1 you should be seeing the values (newELO) generated that need to be saved to move your calculation forward. technically you do not need the date criteria and it can be removed once you start to update the EOL values.

Quote:
2. I'm not so used to update queries - are you recommending that I build an update query based on qryNewCalcELO? Or should I run qryNewCalcELO and then an update query off the results?
yes and no - I would expect you do this in vba because a) the qryCalcNewELO cannot be made updateable and b) it needs to be run multiple times (for each date) to bring the ELO up to date - so easier to do in vba - you would need to remove the date criteria.#

Quote:
3. Looking at the SQL for qryNewCalcELO it selects only Djokovic - I assume that I'd remove this in the full version as it will need to calculate ELO scores match by match for both players?
correct - in hindsight that was not required but was originally intended to get to the earliest record.

Quote:
4. Also on the SQL for qryNewCalcELO it uses TOP 1 - wouldn't this mean it will only select the top record for that date? Surely I'll want to pull all match records for that date and then update eloPlayers with the results?
No, you have to run this on a game by game basis. What if a player plays two games on the same day? Also see below

Once you have the data up to date, the principle would be to use qryFutureProb to make your prediction. Once the game is played and winner/loser known, run the qryCalcNewELO (in VBA) to update the ELO field.

Not clear what your actual process would be - perhaps you run qryCalcNewELO (in vba) after each game, once a day, after the tournament?

And you have data inconsistencies - you have duplicate records and some of the hard coded values do not match the calculation. In addition your date might need to include a time element - if a player plays two games on the same day?

No doubt the design can be improved, for example you might want to store a history of ELO's in which case you need an extra table and make changes to the queries, but that is not apparent from the description of your requirement.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-20-2019, 01:34 AM   #36
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 32
Thanks: 5
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Re: Lookup the value from a previous record

As ever CJ - thanks.

Quote:
Not clear what your actual process would be - perhaps you run qryCalcNewELO (in vba) after each game, once a day, after the tournament?
Will run 1-2 times per day depending on location of matches, e.g. Europe, US, Asia.

Quote:
And you have data inconsistencies - you have duplicate records and some of the hard coded values do not match the calculation. In addition your date might need to include a time element - if a player plays two games on the same day?
The dupes were me not joining correctly - fixed this now. The hard coded values were necessary as otherwise I'd have had to have all the non-Djokovic player matches in there. So values wouldn't calculate correctly if I switched to formulas as the K values would be off due to low match counts. The time element was a pain for exactly the reason you mention - there is a round ID which can be used to create a time orderable field so in the end I built an append query to import all new data into a fresh table in sequence of date and round. I then put an autonumber primary key in so make indexing searching etc lots easier.

Quote:
No doubt the design can be improved, for example you might want to store a history of ELO's in which case you need an extra table and make changes to the queries, but that is not apparent from the description of your requirement.
I've actually adapted your design quite a bit already. The biggest change was the fact that using count in SQL took ages when searching 650k records. I read a lot about recordsets and using .filter here I've managed to reduce the time from several seconds per record to one second :-)

On the storing of previous ELO values - it's definitely worthwhile but I've avoided it so far. What would you think - a table with Date, Player ID, Old and New?

Again - thank you so so much for your help with this. I'm pretty chuffed sat watching the database crunch through 325k match records at one a second. Even the time prediction of 88 hours isn't dampening my spirits!
Jossy is offline   Reply With Quote
Old 08-20-2019, 03:26 AM   #37
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup the value from a previous record

Quote:
The biggest change was the fact that using count in SQL took ages when searching 650k records
make sure the appropriate fields are indexed

Quote:
What would you think - a table with Date, Player ID, Old and New?
No - just date, playerID and ELO - latest is then the one with the latest date

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-20-2019, 04:17 AM   #38
Jossy
Newly Registered User
 
Join Date: Aug 2009
Posts: 32
Thanks: 5
Thanked 0 Times in 0 Posts
Jossy is on a distinguished road
Re: Lookup the value from a previous record

Great, thanks again CJ.

Jossy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] How to use a previous Lookup to narrowdown the next Lookup Lucas79 Forms 4 08-22-2015 08:47 AM
Lookup previous value for employee essexguy2015 Queries 3 08-09-2015 05:00 PM
Lookup at the previous record in the table bimmer5 Queries 18 01-27-2014 03:57 AM
Lookup List of Values depending on a previous lookup list ds3010 Queries 6 11-07-2011 08:35 AM
[SOLVED] How to lookup value in the previous record? maxkmv Modules & VBA 1 03-13-2002 02:03 PM




All times are GMT -8. The time now is 08:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World