Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2019, 08:37 AM   #16
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Lookup the value from a previous record

OK - you know where to come if you do need further support.
If its not directly related to the serialize function you may be better off starting a new thread and if necessary linking back to this one.
Good luck

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Jossy (08-12-2019)
Old 08-11-2019, 02:53 AM   #17
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 stuff, thanks and will do.
Jossy is offline   Reply With Quote
Old 08-12-2019, 05:00 AM   #18
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 isladogs View Post
OK - you know where to come if you do need further support.
If its not directly related to the serialize function you may be better off starting a new thread and if necessary linking back to this one.
Good luck
Started a new thread on something slightly different but related. Do I just put the link to the new thread here?

Jossy is offline   Reply With Quote
Old 08-12-2019, 05:36 AM   #19
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Lookup the value from a previous record

No. Put a link to this in the other thread
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-12-2019, 06:08 AM   #20
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

Will do. Thanks
Jossy is offline   Reply With Quote
Old 08-12-2019, 12:38 PM   #21
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 isladogs View Post
OK - you know where to come if you do need further support.
If its not directly related to the serialize function you may be better off starting a new thread and if necessary linking back to this one.
Good luck
Hello again Isladogs. Mind if I take you up on that offer? This isn't related to the serialize function but is completely in line with the original post I made.

Here goes... so I've used serialize to create a rank field for my data and I've now been looking at how I use this lookup a previous record in another field. However, I've realise things aren't as simple as I originally thought.

Here's the non-simplified version fo the background and output...

...ELO was a system devised by a famous chess player of the same name to measure the strength of his opponents. There are various versions but in mine a player starts with an ELO score of 1500. After every game, the winning player takes points from the losing one. The difference between the ratings of the winner and loser determines the total number of points gained or lost after a game. In a series of games between a high-rated player and a low-rated player, the high-rated player is expected to win more. If the high-rated player wins, then only a few rating points will be taken from the low-rated player. However, if the lower-rated player wins, many rating points will be transferred.

Now there are lots of complicated calculations to underpin this but the basic principle is that a player's ELO rating is determined by a formula based upon both players' ELO scores going into that match. If it helps then I've put together a small Excel sheet for Novak Djokovic - https://1drv.ms/x/s!AjGyJyJaARk2hf9J...AnZvg?e=orc1dQ. In cell K111 you can see that Djokovic starts with a score of 1500 and his opponent starts with a score of 1434 (cell S111). These two scores mean that Djokovic had a 59% chance of winning the match (which he did). Combining this probability with something called a 'K' factor (basically a number based on the number of previous matches played) means Djokovic has a new ELO score of 1553 (an increase as he won). This is then carried through to his next match in cell K110 and so on and so on. You can see the way his ELO score progresses from the first game in this dataset to his current score which is equal to Roger Federer in the final game. Note: I have kept the formulas in the Djokovic specific cells but I have to leave out formulas for the other players as this would require all their matches to be in the dataset and then all their opponents matches and all their opponents matches... etc etc.

I'm able to build a query that replicates nearly all of this Excel - the only columns I'm stuck on are K and S which are the starting ELO scores for player 1 and player 2. As per the previous posts I've used the serialise function to build a ranking system that I can use to find a previous ELO score for each player from another field. However, the big problem is that the previous record isn't in another table - it needs to be generated by the query itself as every ELO score is the output of the previous match. I've tried using a sub-query to lookup a field generated by the query but this errors (probably obviously!). I then thought about having two identical queries to reference each other but I don't think this is right as you still need one of them to generate the previous ELO score. Is there a simple SQL solution to this or will I maybe have to drastically think about the data structures I'm using?

I realise this it's a huge ask to read all this but I'm at my wit's end. Thanks in advance!!
Jossy is offline   Reply With Quote
Old 08-12-2019, 12:54 PM   #22
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Lookup the value from a previous record

Hi Jossy
I understand the principle involved but, due to other commitments, I really haven't time to study the Excel file and attempt to provide a solution for you at the moment.
However, there are plenty of other forum members who may be willing to take this on.
As many forum members will not open files posted externally, I suggest you zip the Excel file and post it here

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-12-2019, 02:25 PM   #23
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 isladogs View Post
Hi Jossy
I understand the principle involved but, due to other commitments, I really haven't time to study the Excel file and attempt to provide a solution for you at the moment.
However, there are plenty of other forum members who may be willing to take this on.
As many forum members will not open files posted externally, I suggest you zip the Excel file and post it here
Completely understand and thanks for your help so far. Attached the file to this post.
Attached Files
File Type: xlsx ELO Excel to Access - Djokovic.xlsx (41.4 KB, 5 views)
Jossy is offline   Reply With Quote
Old 08-12-2019, 03:09 PM   #24
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

not sure if I am in a position to offer any help. But the way a problem is solved in excel is frequently not the way you would solve it in a database.

For a start, data needs to be normalised in a database and Excel is generally as far from normalised as you can get.

Secondly the required output - Excel by it's nature ends up with lots of rows, lots of columns which you then have to parse/filter to find the information your require. Do you really need a query that presents all rows for all players? or are you only interested (at any one time) in the players in an upcoming match?

Thirdly, your explanation misses out the most important bit (and I'm not going to try to work it out from the spreadsheet) which is
Quote:
These two scores mean that Djokovic had a 59% chance of winning the match (which he did). Combining this probability with something called a 'K' factor (basically a number based on the number of previous matches played)
Why is the P2 CurrentELO hardcoded? Why is the P2 K overall value hard coded?

Is there any relevance of the location - are you saying if player A plays player B the 'projected outcome' would be the same regardless on where they play
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-13-2019, 12:14 AM   #25
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 for picking up the mantle CJ.

Quote:
Do you really need a query that presents all rows for all players? or are you only interested (at any one time) in the players in an upcoming match?
There will be two main outputs needed:

1. The ability to see the latest ELO rating for a given player. In this case it wouldn't be necessary to calculate all rows for all players. However, the way the calculation works it will probably be close to all records as you will need the scores of each of their opponents for all their previous matches. In order to get those you will need the scores of all the opponents' opponents and so on and so on.

2. A comparison of the accuracy of the forecast results to the actual results, e.g. did the model predict a winner with 75% accuracy when the player was calculated to have a 75% probability of winning? I will then add in a range of additional metrics and calculations to try and improve this accuracy, e.g. the number of points won in a match.

Quote:
Why is the P2 CurrentELO hardcoded? Why is the P2 K overall value hard coded?
CurrentELO and K are only hardcoded for Djokovic's opponent. Otherwise you need all their matches in the dataset to calculate cELO and K, and then all their opponents' matches and so on. The file size would have been above the 2Mb limit.

Quote:
Is there any relevance of the location - are you saying if player A plays player B the 'projected outcome' would be the same regardless on where they play
I assume by this you mean the name of the tournament? No - just a bad habit of always seeing it so adding it to the query! At a later point I will want to create a query to calculate ELO for a specific surface, e.g. grass or clay, but once I figure out the 'overall' method then I'm sure I can adapt it.
Jossy is offline   Reply With Quote
Old 08-13-2019, 01:47 AM   #26
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

I don't have a lot of time today, but first you need to normalise your data. Suggest the structure would be something like


tblLocations (not really required since it does not bear on the calculation but included for completeness)
LocationPK
Location
Name

tblGames
GamePK
LocationFK
GameDate


tblPlayers
PlayerPK
PlayerName
ELO

tblScores
ScorePK
GameFK
PlayerFK
Winner

you then need to populate tblScores for the earliest score for each player with an earliest gamedate (could be 1/1/1900) - unless all players default to a score of 1500

after that your query for a new game would be something like

Code:
SELECT W.PlayerFK, W.ELO, L.PlayerFK, L.ELO, doyourcalcshere
FROM tblPlayers W INNER JOIN (SELECT PlayerFK, Count(*) FROM tblScores GROUP BY PlayerFK) WL ON W.PlayerFK= WL.PlayerFK, tblPlayers L INNER JOIN (SELECT PlayerFK, Count(*) FROM tblScores GROUP BY PlayerFK) CL ON L.PlayerFK=CL.PlayerFK
WHERE W.P
to build your history you will need to populate tblGames with all the games, then using vba, loop through from earliest to latest to calculate the latest score and update the ELO value in tblPlayers
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-13-2019, 02:19 AM   #27
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 - really do appreciate your help. The data is normalised - it's a well structured third party product. The Excel is a copy and paste from a query of a few tables - the structure of which is very similar to yours :-)

So I need to go the VBA route you reckon and build a table...? Okay dokey - that might stretch my VBA abilities but I shall give it a go!

One quick question - I know calculated fields in tables are usually a no no but in this instance would it be best to create the non-ELO fields as calculated fields?These depend on, or input into the ELO scores, so they'll need to be created at the same time as the VBA loops through each line. Seems sensible to get Access to do the work for these rather than build the VBA?
Jossy is offline   Reply With Quote
Old 08-13-2019, 04:04 AM   #28
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:
I know calculated fields in tables are usually a no no
they are limited in scope - and certainly can't do what you are look for
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-13-2019, 04:46 AM   #29
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

Ok thanks. Will work out the best way to do this.
Jossy is offline   Reply With Quote
Old 08-13-2019, 08:15 AM   #30
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

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
Attached Files
File Type: accdb ELO.accdb (948.0 KB, 4 views)

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London 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 07:13 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