Show only first instance of value in access report

[EnginerdUNH]

Hi,

I am working on developing an access database to compile all of my personal bests at the gym. I have a simple form called frmAllRecords where I enter each movement record, either selecting different options from a few dropdown boxes or entering the text directly into a few textboxes. This form is linked to a table called tblAllRecords. From there, I have a query called qryAllRecords that pulls all of the records from tblAllRecords where the number of sets (called ARSets in tblAllRecords) is equal to 5, 4, 3, 2, or 1 and I have a report called rptAllRecords which shows this query data. When the report runs now, it gives me a listing of all records at each weight, some of which I may have only done once, and others could be shown 5 or 6 or more times on different days. What I am looking to do is only show the first instance of each weight attempt (called ARLoad in tblAllRecords) in rptAllRecords rather than showing each time. What I mean by this is that the report would only show each load once and only show the first date that I was successful at achieving it rather than showing each date.

Now, I know that I could set up logic to have the database look through each time I go to enter a new record and see if a PR for a particular movement at that load for that number of sets (or a higher load for that number of sets) had been achieved or not and only enter in a new record if it was a new load for that number of sets but I want to be able to go back later and see how many times I hit a specific weight on a movement and/or how many times I stayed at that weight or went down in weight before hitting my next PR. To do that last part, I really do need all of the records to be stored in the main table and not just the first instances.

Can anyone help me figure out how I can modify the existing rptAllRecords to show only the first instance of a successful attempt at a weight for each movement in the report? I have attached a copy of the database to this post as well. Any questions, feel free to ask.

[HansV]

Could you provide a small sample of what you would want the result to be?

[EnginerdUNH]

Hans, sure I can provide something. I have attached a screenshot of how part of the report is current displaying.

The first thing I would like to do is only show the first instance of a load in the report; so that would mean that only the record for 1/10/22 would show for 45 lbs and not the two records for 1/21/22. Side note: I'm not sure why the records are showing out of order but in the case of the example above, I would actually want to make sure that only the records for 60, 62.5, 80, and 90 lbs for this example would display.

[HansV]

Thanks! See the attached version. I created two new queries: a totals query to return the first date for each load within a group, and a query that combines qryAllRecords with the totals query. The latter is the new record source for the report.

PR Tracker.zip

[EnginerdUNH]

Thank you Hans! I didn't even think about doing it that way! My only question now is that the report is still showing records out of order (it's showing them in order of smallest to biggest load) and I would like to also exclude records where a smaller load occurs after a bigger one. Using the example above, if we were to put the loads in order of date and only using the first instance, they would be 60, 62.5, 45, 80, and 90. Because 45 is less than the first load, it should be excluded from the report.

[HansV]

I think the attached version will do what you want, but please check it carefully.

PR Tracker.zip

[EnginerdUNH]

that looks to be absolutely what I'm looking for, thank you!! One last question though, I'm trying to go back and recreate what you did in the last version you uploaded so that I can learn how to duplicate it for myself in the future when needed. I have gotten the records to appear in the correct order but I don't see how you were able to get the records to not display if the load is less than the previous date's record.

[EnginerdUNH]

Actually, on further inspection of the queries, I found the piece I was missing in the SQL code for qryFirstRecords. Thank you so much again for your help!

[HansV]

You're welcome!

Sorry, I had missed that you added a question (in general, it's better to post a new reply than to edit an existing reply - I can see in the Portal if there is a new reply but not if a reply has been edited)