Lookup the value from a previous record (1 Viewer)

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
Hi all. I'm trying (the operative word!) to build a query for an Access tennis database that is provided and updated by a third party. The query will be based on a table of historic match results that has the Date, the Winning Player and the Losing Player. The query needs to assign a fixed numeric Current Score (let's say 100) to a player if it is their first match in the database. Based upon whether they then win or lose the query needs to calculate a New Score for them. The New Score calculation is complicated but for simplicity's sake let's say the Winning Player gets +5 and the Losing Player -5. Now the next time one of the players plays I need the query to 'lookup' what their New Score was for their previous match and this becomes the Current Score for that match.

So if Federer beats Nadal and this match is both players' first match in the database then they the query will give them both a Current Score of 100. It will also give Federer a New Score of 105 and Nadal a New Score of 95. The next time Federer plays then his Current Score will be 105 and the next time Nadal plays then his Current Score will be 95. It's important to highlight at this stage that players can obviously appear in either the winning or Losing Player column.

Currently I export the table to Excel, order it in decending order of date and then use if statements and lookups to find the last record for a player in either the Winning Player or Losing Player columns. However, Excel is starting to really strain with the weight of 20,000 matches and I want to increase this to circa 300,000!

It's safe to say that I have no idea how to replicate the Excel functionality in Access and googling 'Access lookups' brings up a never ending list of simple 'how to lookup using queries' pages. I'm stuck - any ideas??
 

plog

Banishment Pending
Local time
Today, 13:40
Joined
May 11, 2011
Messages
11,611
This doesn't really involve looking up a prior record. This is a summation of all records (since the prior one will be based on its prior, which is based on its prior, etc.). What you want is running sum query (google and search the forum for that term).

A big issue I see is determining the order of your matches. You say you have a date field, but is that enough? Could a player play multiple matches a day? If so, and you want to specifically order your data and not just by date, then you need more grainular data to determine the order.

Since you haven't provided specifics I can only tell you how to do this in general. Like I said before--search 'running sum query'. You will simply determine how much a win/loss is worth and then add up all those values for a player and add 100 to that value.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:40
Joined
May 7, 2009
Messages
19,169
so what are the columns involved from the table, guess:

MatchDate, Winner(name), Losser(name) , CalculatedScore ?
 

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
This doesn't really involve looking up a prior record. This is a summation of all records (since the prior one will be based on its prior, which is based on its prior, etc.). What you want is running sum query (google and search the forum for that term).

A big issue I see is determining the order of your matches. You say you have a date field, but is that enough? Could a player play multiple matches a day? If so, and you want to specifically order your data and not just by date, then you need more grainular data to determine the order.

Since you haven't provided specifics I can only tell you how to do this in general. Like I said before--search 'running sum query'. You will simply determine how much a win/loss is worth and then add up all those values for a player and add 100 to that value.

This is a great shout but you're right - in rare cases a player can play two games in a day and that would throw things out. I'll try the solution from isladogs and see how I get on! Thanks loads for replying so quickly.
 

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
Thanks for the heads up isladogs. Looks fairly complicated but then I figured it would be :) Will try and see how I get on!
 

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
so what are the columns involved from the table, guess:

MatchDate, Winner(name), Losser(name) , CalculatedScore ?

Hi. Date, Winner Name, Winner Current Score, Winner New Score, Loser Name, Loser Current Score, Loser New Score
 

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
There are several ways of doing this.
See this link for my approach http://www.mendipdatasystems.co.uk/getpreviousrecordvalue/4594484854

Hi isladogs. So I've been trying this but I came unstuck as I couldn't add the Serialize code to the database as I kept getting an error saying that someone else is using it. Tis is probably because it's a third party product.

To get round this I created another database, linked all the tables, added a module, inserted the code and built a query using the Serialize function. Query runs and displays some records but is 'locked up' and I can't select records or navigate. I thought this must be because the function is overloading Access as it's pulling in over 200,000 lines so I built another query to only filter out all but 700 recent events (using date field) and then pointed the original query at this. I'm sure there's a way to write SQL to do this filter in the original query but I'm not close to knowing enough about this yet. Anyway...

...the new query works better and I can navigate records but there is a seriously noticable lag as if the query is updating itself everytime I change record. This seems strange for only 700 records. Here's the SQL:

Code:
SELECT games_atp.DATE_G AS Match_date, tours_atp.NAME_T, tours_atp.TIER_T, stat_atp.ID1 AS Winner_ID, players_atp.NAME_P, stat_atp.ID2 AS Loser_ID, players_atp_1.NAME_P, stat_atp.FS_1 AS No_first_serves, stat_atp.ID_R AS Round_ID, CLng([Match_date]) & "_" & [Round_ID] & "_" & [Winner_ID] AS Concat, Serialize("ELO_pre-builder_atp_1","Concat",[Concat]) AS Rank
FROM players_atp AS players_atp_1 INNER JOIN (players_atp INNER JOIN (tours_atp INNER JOIN (games_atp INNER JOIN stat_atp ON (games_atp.ID_T_G = stat_atp.ID_T) AND (games_atp.ID1_G = stat_atp.ID1) AND (games_atp.ID_R_G = stat_atp.ID_R) AND (games_atp.ID2_G = stat_atp.ID2)) ON (tours_atp.ID_T = games_atp.ID_T_G) AND (tours_atp.ID_T = games_atp.ID_T_G)) ON players_atp.ID_P = games_atp.ID1_G) ON players_atp_1.ID_P = games_atp.ID2_G
WHERE (((games_atp.DATE_G) Is Not Null And (games_atp.DATE_G)>#7/23/2019#) AND ((tours_atp.TIER_T) Like "Challenger*" Or (tours_atp.TIER_T) Like "ATP World Tour*" Or (tours_atp.TIER_T)="Grand Slam" Or (tours_atp.TIER_T)="Finals"));

Realise this contains more tables and fields than I referred to in my original post but I was trying to simplify.

So... is Serialize simply a very intensive function so not suitable for anything over a handful of records?

Or... could this be something to do with the fact I've had to create a linked database?

Or... could it be something completely different?

Thanks in advance.
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
Hi

I couldn't add the Serialize code to the database as I kept getting an error saying that someone else is using it. This is probably because it's a third party product.
Sorry - don't understand this.
Try holding the shift key down to bypass any startup code ...then add the Serialize function code

...the new query works better and I can navigate records but there is a seriously noticeable lag as if the query is updating itself everytime I change record. This seems strange for only 700 records....

Realise this contains more tables and fields than I referred to in my original post but I was trying to simplify.

I've just tested this using a linked table with 2.6 million records.
The query ran instantaneously though of course scrolling to the last record took a very long time (as it would without the serialize function).
I then repeated it using a query with another 5 linked tables joined with inner joins. Once again it was very fast though scrolling to the end now locked the database. When it recovered I saw an error message that I don't recall ever seeing before in over 20 years of using Access:



Mind you the new test query contained some 42 million records ;)

So... is Serialize simply a very intensive function so not suitable for anything over a handful of records?
No

Or... could this be something to do with the fact I've had to create a linked database?
Not really though it may be a contributory factor

Or... could it be something completely different?
Did you check the time needed to run the query with no ranking?
It is likely to be slow anyway.

Trying to use serialize on a concatenated field is likely to give poor performance. Recommend using a single field ONLY. Also make Serialize the first field in your query.

Try indexing the field that you are applying the rank order to.
That should significantly speed up the ranking query (or any query involving a search)
Having said that, my test queries were ranked on an un-indexed field.

If you MUST use a concatenated expression do this as a stacked query.
Create your Concat field in a separate saved query then make a new query with Serialize based on that

Also as your query involves several filter criteria, ensure the fields used in those filters are indexed and that the filter can use your indexes. The date filters may need rewriting to do this.
You may find this article useful: Optimise Queries
 

Attachments

  • Capture.PNG
    Capture.PNG
    4.9 KB · Views: 848
Last edited:

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
Thanks a million for your help - will work on these tomorrow. On the 'more than one user' issue - I tried holding down shift which didn't enable macros but as soon as I opened up a VB window the warning came back. The exact message is: "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later".

Anyhow - I'll follow your advice on optimising the queries and see where that gets me!
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
OK - is this a split multi user database? If so, how are the FE & BE setup?
If unsplit and standalone, the lock file may not have been deleted from a previous session.
Check using Task Manager for instances of Access still in use.
If all fails, restart Windows and try again opening Access before doing anything else
 

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
Hi again. Sorry if I'm becoming a pain now. Here's the update from this morning...

Did you check the time needed to run the query with no ranking?
It is likely to be slow anyway.

It's super fast.

Try indexing the field that you are applying the rank order to.

I'm new to Access but I think you can only index fields in tables? If yes, then I can't do this as the company have locked down the tables and you can't make edits.

If you MUST use a concatenated expression do this as a stacked query.
Create your Concat field in a separate saved query then make a new query with Serialize based on that

Done this - still locks up.

Also as your query involves several filter criteria, ensure the fields used in those filters are indexed and that the filter can use your indexes. The date filters may need rewriting to do this.

Same issue here around tables being locked down. Also figured that as I was creating a stacked query for the concat then the final query didn't actually have any filters etc?

So next I tried creating an offline version of the database to test the theory around the linked version being an issue. I copied all the tables across to a new database plus two queries (one to build the concat and the second to use serialize to build the ranked output). Running this was just as slow as the linked version.

Next up I copied just the data from the concat query into a new database and built a serialize query for this and lo and behold it worked fast as lightning! Even tested it with the 200k records and perfecto.

So I have a workaround - export the data from the concat query in the original database into a new one and run the serialize query on that. However, this won't work in the long term as once I've finished the query (need to look at all the calculated fields now from my original post) then I want to be able to use the results of this in the original database, e.g. to pull the calculated score for upcoming matches (using queries of other tables). I've tried creating a table in the original database from the concat query but it doesn't do anything so I figure this has been locked down too.

Any other thoughts on why the serialize query in the original database could be taking so long? It just seems so odd that it works fine against a table but not against the concat query...
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
You didn't answer the questions in my last post.

You can check whether table fields are indexed by viewing the table design.
If not, it would be worth asking that someone with permissions to edit the tables adds indexes where relevant. Doing so will significantly improve the speed of all select queries on those table fields though it will slow down update queries.

If the fields are indexed, you still need to ensure the filter uses those indexes. See my link on optimising queries.

As previously stated, its not a good idea to do a rank order on a concatenated field. Try to change this if possible.

Failing that you could use a make table query to create a modified local version of your table complete with indexes and your concat field. If doing so repeatedly, instead use a delete query and append query to repopulate the same table when needed. In either case, it will lead to database bloat so avoid if possible.

How did you manage to create a table in the original database if its locked down? What does it doesn't do anything mean?

I think that covers everything you asked but let me know if not.

You mentioned calculated fields in your table. These are considered to be a bad idea as they duplicate data and waste space. Use queries for calculated values.

One final thing.
Have you tried Allen Browne's alternative approach I mentioned in the link in my article? It uses subqueries instead.
I think it will be slower but worth trying anyway.
 
Last edited:

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
You didn't answer the questions in my last post.

Sorry - been trying so many things I forgotten to update on this. Restarting Access sorted this out and allowed me to add and index tables.

So I've gone round the houses today... internet outages, DLL errors, missing references and about 50 lockups of Access have conspired against me.

However, I think I've got there with the local table route :) Serialize query is almost instantaneous.

When I refered to caculated fields I meant from a query - think I probably wasn't using the right language. That's what I'm going to work on now and my hunch is that I may need a little more help. Please don't give up on me yet!
 

isladogs

MVP / VIP
Local time
Today, 18:40
Joined
Jan 14, 2017
Messages
18,186
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
 

Jossy

Registered User.
Local time
Today, 18:40
Joined
Aug 24, 2009
Messages
32
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?
 

Users who are viewing this thread

Top Bottom