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.
Show only first instance of value in access report
-
- StarLounger
- Posts: 94
- Joined: 14 Aug 2019, 00:12
Show only first instance of value in access report
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Show only first instance of value in access report
Could you provide a small sample of what you would want the result to be?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 14 Aug 2019, 00:12
Re: Show only first instance of value in access report
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Show only first instance of value in access report
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 14 Aug 2019, 00:12
Re: Show only first instance of value in access report
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Show only first instance of value in access report
I think the attached version will do what you want, but please check it carefully.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 14 Aug 2019, 00:12
Re: Show only first instance of value in access report
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.
-
- StarLounger
- Posts: 94
- Joined: 14 Aug 2019, 00:12
Re: Show only first instance of value in access report
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!
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Show only first instance of value in access report
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)
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)
Best wishes,
Hans
Hans