Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2021
    Posts
    23

    Problem with multiple subreport in one report showing multiple times

    Hello guys,

    I've been looking for a solution to this all day and didn't come to a solution.
    I have built one report that has multiple subreports that are based on queries over multiple tables and all the subreports and queries share one common ID of a Person which I intend to filter. So I open the main Report via a Button and set a Filter on the main Report to show only his data and not everyone else's.

    I put the subreports into the detail section and the data are shown multiple times which I do not intend. What is the logic behind that? The subreport is showing up as many times as there are recordsets in the query that is tied to the subreport?
    I've been also looking here for similar problems but didn't quite find the solution.
    I came across the idea, to group the report but I think in my case, it won't help since the subreports are kind of necessary in my opinion because I cannot do ONE single query for this ONE report because there are too many connections and too many tables so that one query returns no data back, hence grouping the report wouldnt help. So this solution has been kind of recommended many times but I do not think that this may work because as I said, the stuff is way too stretched over more than just 3 tables so that I could simply do one query and put this as a recordsource for the main report. I'm not really sure about this.

    I also thought about just putting the subreports into the side headers but the thing is, the side headers appear on every page and that's exactly not what I intend haha!

    Also, I'm not sure whether Linking (Master - Child) will help here...

    Any other ideas?



    The reports (subreports) on their own work quite well, it's just that they do not work when I embed them as supreports into this main report.

    The thing is, every subreport needs to be filtered so that only the data of one selected person shows up. The Person's ID is the common ground of all the subreports which I intend to use to filter.

    I also thought about automating the creation of each of the subreports on their own and then concatenate them (somehow) automatically but this seems to be a really dirty workaround.

    I also thought about just putting all the sub reports into the (first) report header of the main report into which all subreports are embedded but this also seems kinda too dirty as a workaround since I actually need this report header indeed because it's an official document.

    I'm kind of frustrated rn and I feel really burnt and I'd be really glad for help.
    If there's additional information you need, please ask and I'll ofc answer it.

    Thank you in advance for reading, I reallly appreciate this forum and all you helpful guys, you are doing a tremendous work with your help, I really mean it!

    Greetings,

    FinisherProgrammer21

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, subreport in Detail section will display with each record of main report. That's how it's supposed to work. Yes, use Master/Child links. When you apply filter on main report to show only one person, the subreports will synchronize.

    If main report is filtered to one person, does that mean there is only one record on main report?

    What are 'side' headers?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by June7 View Post
    Yes, subreport in Detail section will display with each record of main report. That's how it's supposed to work. Yes, use Master/Child links. When you apply filter on main report to show only one person, the subreports will synchronize.

    If main report is filtered to one person, does that mean there is only one record on main report?

    What are 'side' headers?
    Hello, thank you for your reply.
    I've set the main reports recordsource only to the Person'sID (really nothing else; was this okay btw? This means I only used the main report as a "container" for all the subreports but I did not include any real data within the main report because I wanted the subreports to do this for me) which i retrieved from "tbl_Person" and the other sub reports i embedded are based on queries which also include the Person's ID (person's ID can be outside of that tbl_Person of course).


    So I should use Master/Child link to link all of the subreports to PersonID / PersonID? Like Master is PersonID and Link is also PersonID? Do I understand it correctly?

    QUOTE "If main report is filtered to one person, does that mean there is only one record on main report?" <= I do not have any data in the main report included so to so. Is this the main error why I am getting to show duplicates? Should I sort of include "Master data (Name, Lastname, Company, Fulltime/PartTime and also most importantly PersonID so that the main report actually includes data? Will the subreports filer accordingly then? Is it sufficient if I set the recordsource of the main report so that it will include PersonID but I can actually get rid of the textfield which includes this ID and is still usable in the background to synchronize the subreports?

    Do I synchronize the main report and all the subreports (which normally include multiple recordsets EACH person) only with the PersonID? Like I do not use textboxes or something else but only the plain PersonID?
    One important info: I already filtered the queries which I used to tie them to all the subreports to the PersonID which is currently in the NavigationForm's Header in a Combobox. And still, it shows duplicates and it seems like the duplicates are from the already filtered/intended Person.

    Thank you for reading, I will be asking of course if something is not understandable.

  4. #4
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by June7 View Post
    Yes, subreport in Detail section will display with each record of main report. That's how it's supposed to work. Yes, use Master/Child links. When you apply filter on main report to show only one person, the subreports will synchronize.

    If main report is filtered to one person, does that mean there is only one record on main report?

    What are 'side' headers?


    Sorry, I translated it wrong. With side headers I meant page header

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Quote Originally Posted by FinisherProgrammer21 View Post
    So I should use Master/Child link to link all of the subreports to PersonID / PersonID? Like Master is PersonID and Link is also PersonID? Do I understand it correctly?
    That was my suggestion.

    Quote Originally Posted by FinisherProgrammer21 View Post
    Should I sort of include "Master data (Name, Lastname, Company, Fulltime/PartTime and also most importantly PersonID so that the main report actually includes data? Will the subreports filer accordingly then? Is it sufficient if I set the recordsource of the main report so that it will include PersonID but I can actually get rid of the textfield which includes this ID and is still usable in the background to synchronize the subreports?
    Main report should have a RecordSource that has at least the PersonID field (even if it is not displayed), however, it makes sense to me to include other person "header" info on main report.

    Quote Originally Posted by FinisherProgrammer21 View Post
    Do I synchronize the main report and all the subreports (which normally include multiple recordsets EACH person) only with the PersonID? Like I do not use textboxes or something else but only the plain PersonID?
    One important info: I already filtered the queries which I used to tie them to all the subreports to the PersonID which is currently in the NavigationForm's Header in a Combobox. And still, it shows duplicates and it seems like the duplicates are from the already filtered/intended Person.
    As noted, PersonID does not have to be displayed in textbox on main form. If you get duplicate subreports, sounds like filter is not applied to main form. As for filtered queries, I don't use dynamic parameterized queries if I can avoid.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by June7 View Post
    That was my suggestion.

    Main report should have a RecordSource that has at least the PersonID field (even if it is not displayed), however, it makes sense to me to include other person "header" info on main report.

    As noted, PersonID does not have to be displayed in textbox on main form. If you get duplicate subreports, sounds like filter is not applied to main form. As for filtered queries, I don't use dynamic parameterized queries if I can avoid.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    Thank you for all the info. I will try to follow the advice you provided and I am thankful.

    "If you get duplicate subreports, sounds like filter is not applied to main form" Which form do you mean? Like the overall Navigation form or did you mistakenly say main form but meant main report? Thank you for answering this.
    I have kinda sensitive data in the DB so I'm gonna first get rid of all this.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, I meant " not applied to main report".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by June7 View Post
    Sorry, I meant " not applied to main report".

    Hello June7,

    thank you for all your advise
    I solved my problem! I already did it the way you explained but there seems to be an issue that sometimes the subreports don't synchronise in time so I manually have to Requery. This is a bit annoying but with Link Master Child, it worked and just tie it to the Person's ID and no duplicats appeared

    How do I mark this thread as solved actually?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use Thread Tools dropdown above first post. Done.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-24-2020, 10:16 AM
  2. run a report multiple times and print them
    By chriswrcg in forum Macros
    Replies: 9
    Last Post: 01-04-2019, 05:04 PM
  3. Replies: 1
    Last Post: 03-08-2018, 08:48 AM
  4. Report displays data multiple times
    By kermit in forum Reports
    Replies: 2
    Last Post: 05-11-2015, 03:50 PM
  5. Report And Subreport multiple user inputs
    By JGrots in forum Reports
    Replies: 16
    Last Post: 01-29-2013, 01:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums