Hans, thank you for taking a look into this for me. I will do some fiddling with the queries myself as well while we wait to see if anyone else has any ideas but I did just come up with a thought that I wanted to run by you and/or the others...
In the database file I provided, there are 3 main queries that I have been using to try and accomplish what I want:
- qryFirstSets which pulls the first instance of each exercise, set number, position, equipment, and load combination from tblAllRecords
- qryFirstRecords which pulls only the records from qryFirstSets where a heavier load was achieved for each exercise, set number, position and equipment combination (i.e. if the loads achieved in order of date are 20, 30, 12, and 45 for a specific exercise, set number, position, and equipment then qryFirstRecords would only return 20, 30, and 45)
- qryPRsBeforeStart is my attempt to have the database return the most recent heaviest load for each exercise, set number, position and equipment combination which occurred on or before the given date on the main dashboard form. In the case of the example above, lets say the dates corresponding to those loads are 1/1/22, 1/15/22, 2/6/22, and 4/9/22. If the date specified was between 1/1/22 and 1/14/22, the query would return 20 as the load. If the date specified was between 1/15/22 and 2/5/22, it would return 30. And in the case of my example, if the date specified was between 2/6/22 and 4/8/22, it would return 45.
As for my thought, do you think I could make things simpler by cutting out the middle step performed by qryFirstRecords? My thought is that it may make things work smoother/faster if there's only two (or three) queries that have to do the work rather than four in the case of your example process.