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!AjGyJyJaARk2hf9JgTU3EMLIEAnZvg?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!!