Query to Find Max Value On or Before Date

EnginerdUNH
StarLounger
Posts: 50
Joined: 14 Aug 2019, 00:12

Query to Find Max Value On or Before Date

Post by EnginerdUNH »

Hi,

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.

Code: Select all

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.

User avatar
HansV
Administrator
Posts: 75203
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to Find Max Value On or Before Date

Post by HansV »

Does this work?


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
Regards,
Hans

EnginerdUNH
StarLounger
Posts: 50
Joined: 14 Aug 2019, 00:12

Re: Query to Find Max Value On or Before Date

Post by EnginerdUNH »

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.

User avatar
HansV
Administrator
Posts: 75203
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to Find Max Value On or Before Date

Post by HansV »

It's too late in the evening, I cannot get my head around it any more. I'll try tomorrow if there are no other replies.
Regards,
Hans

User avatar
HansV
Administrator
Posts: 75203
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to Find Max Value On or Before Date

Post by HansV »

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.
Regards,
Hans

EnginerdUNH
StarLounger
Posts: 50
Joined: 14 Aug 2019, 00:12

Re: Query to Find Max Value On or Before Date

Post by EnginerdUNH »

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.

User avatar
HansV
Administrator
Posts: 75203
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to Find Max Value On or Before Date

Post by HansV »

I started again from scratch and came up with a simpler series of queries. See the attached version.

PR Tracker_PRBeforeDate.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

EnginerdUNH
StarLounger
Posts: 50
Joined: 14 Aug 2019, 00:12

Re: Query to Find Max Value On or Before Date

Post by EnginerdUNH »

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?

EnginerdUNH
StarLounger
Posts: 50
Joined: 14 Aug 2019, 00:12

Re: Query to Find Max Value On or Before Date

Post by EnginerdUNH »

Maybe I could add the ID field to the queries and create a new query after qryPRsBeforeDate which adds the date field back in?

User avatar
HansV
Administrator
Posts: 75203
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to Find Max Value On or Before Date

Post by HansV »

That requires an extra query again, but it won't have a large impact.

PR Tracker_PRBeforeDate.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

EnginerdUNH
StarLounger
Posts: 50
Joined: 14 Aug 2019, 00:12

Re: Query to Find Max Value On or Before Date

Post by EnginerdUNH »

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.