I wrote the following query a while back which is supposed provide back the maximum value of ARLoad for each combination of ARExercise, ARSets and ARPosEquip.
SELECT qryFirstRecords.ARExercise, qryFirstRecords.Exercise, qryFirstRecords.PosEquip, qryFirstRecords.ARSets, MAX(qryFirstRecords.ARLoad) as ARLoad, qryFirstRecords.ARDate
FROM qryFirstRecords
GROUP BY qryFirstRecords.ARExercise, qryFirstRecords.Exercise, qryFirstRecords.PosEquip, qryFirstRecords.ARSets, qryFirstRecords.ARDate
HAVING (((qryFirstRecords.ARDate)<=[forms]![frmMainDashboard]![txtStartDate]));
I have found, however, that the query appears to just be spitting back everything from qryFirstRecords with a value in ARDate <= txtStartDate on the main dashboard form. Can someone point me in the direction of what I did wrong??? I've thought up until now that this query was doing what it was supposed to be now with more data that I'm working with, it is apparent that it is not.
SELECT qryFirstRecords.ARExercise, qryFirstRecords.Exercise, qryFirstRecords.PosEquip, qryFirstRecords.ARSets, MAX(qryFirstRecords.ARLoad)
WHERE qryFirstRecords.ARDate<=[Forms]![frmMainDashboard]![txtStartDate]
GROUP BY qryFirstRecords.ARExercise, qryFirstRecords.Exercise, qryFirstRecords.PosEquip, qryFirstRecords.ARSets
Hi Hans, I tried the query text below that you suggested but it doesn't work for me. My guess is because qryFirstRecords.ARDate is not included on the SELECT line anymore? Also, I know that you didn't ask for it yet but I attached a stripped down version of the database to this post as well.
You do not have the required permissions to view the files attached to this post.
The attached database has a query for this, but it is really EXTREMELY slow - it takes minutes on my fast PC. There should be a better way - I'd love to see if someone else has a better idea.
PR Tracker_PRBeforeDate.zip
You do not have the required permissions to view the files attached to this post.
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.
Hi Hans, I'm glad that you were able to simplify it and get it to work, however, it isn't quite what I need. I need the final query to include the date value in tblAllRecords.ARDate corresponding to those records and I noticed that your queries do not include this field. How do I need to modify the query/queries so that this date is included?
Fantastic! Works the way I want it!! I had a bit of trouble at first when I tried re-writing the queries in my main database file but everything appears to be in working order now. I appreciate your assistance so much.
