Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2009
    Posts
    8

    Red face Unanswered: subreport duplicating records

    Hi all,

    I have spent several hours searching to the answer to my problem, but all I found were others with the same problem, but no solution. I hope someone can shed some light on how to fix this issue. I am using Access 2003.

    Problem:
    Report with multiple subreports; each subreport does not duplicate records when viewed by themselves, but when opened in the main report, they print the same records several times.

    Clue:
    There is a known problem with the relationship between them. Say we have Fields A, B, and C. The main report query contains all three fields, the subreport for field B contains fields A and B (plus other data related to B which is why it's a subreport), but the subreport for field C only has A and C with no relationship to B, other than both B and C having a relationship to A. The subreport for C is in the details section of the main report (when I tried putting it elsewhere in the report, like in the header section with A, it doesn't print all the records). The master/child links on the subreports include all that apply.

    Solutions tried that didn't work:
    I have tried removing the master/child links for the other fields except A, but it still duplicates records, just in a different order. I have tried making field B a footer, but it still repeats. I have tried Event Procedure OnFormat of the page footer for B (or when it was in a header of main report, same thing):

    Option Compare Database
    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    If DupeFooter = Me![FieldB] Then
    Me![FieldB].Visible = False
    Else
    Me![FieldB].Visible = True
    End If
    DupeFooter = Me![FieldB]
    End Sub

    Private Sub Report_Activate()

    End Sub


    I have tried adding the header field to the details in the subreport (made that field it not visible) and told it to "hide duplicates." Didn't work. Nothing I tried has worked. Both subreports B and C duplicate records in the main report, but not when they are viewed alone.

    Any suggestions?

    Thanks
    Last edited by LisaSimpson; 01-22-09 at 16:45.

  2. #2
    Join Date
    Jan 2009
    Posts
    11
    Hi Lisa

    I've spent a bunch of time on reports/subreports. I always have the subreports link to the main on the same master field. There's no reason you shoudn't be able to maintain this consistency. If necessary, add a new field to the sub report's underlying table/query to make it so. Also, all my subs are in the detail section of the master. If one of the subs has a sub, they link through their own master/child link.

    HTH,

    Kevin

  3. #3
    Join Date
    Jan 2009
    Posts
    8
    Thanks Kevin. I can't really add field C to field B's table because the relationship is unknown and if I tried to add it, it would duplicate every value in field C with every value in field B.

    I will try to put all of the subreports in the details section and see what that does, but I think the fundamental problem is that B and C are not related other than both being related to A.

    Thanks again and I'll keep tinkering.

  4. #4
    Join Date
    Jan 2009
    Posts
    11
    I'm probably missing part of your point, but my experience is that subs don't need to relate to each other, they only need to both have a field that links to the same field in the master. Sub 1, 2, and 3 all need to have field A as the link field. If 1 is linked through A and 2 is linked through B, then you'll get massive record duplication for each of the combinations.

  5. #5
    Join Date
    Jan 2009
    Posts
    8
    nothing worked so far. I have tried putting the subreports in the details section and it still duplicates the records, just in a different order than it did when one of the subreports was in a header section. I have tried variations of the master/child links and nothing works. very frustrating.

  6. #6
    Join Date
    Jan 2009
    Posts
    11
    Have you had success with other reports w/subs in the past? I'm confident I could solve the problem if you want to email a copy.

  7. #7
    Join Date
    Jan 2009
    Posts
    8
    Yes, I have the original report that this one is based on. The reason I had to make 2 reports is because for this second set of data, the relationship between fields B & C are unknown and I want only for them to be linked to A. The original report included known relationships between all 3, so that report works just fine. It's the lack of link in the second one that I believe is the problem. This database is 2GB, so I can't really send it. Thanks for the offer. I sent an email today to the instructor of my advanced Access class to see if he can help. Thanks. If I get really desperate, I may make a copy of the database, chop it down to a manageable size and send to you ;-) Thanks.

  8. #8
    Join Date
    Jan 2009
    Posts
    8
    I found out what I was doing wrong. When I made the separate tables for the subreports, I forgot to remove that data from the source for the main report and that's why it was duplicating. Once I remove that, it should work fine.

    thanks anyway!

  9. #9
    Join Date
    Jan 2009
    Posts
    11
    Well done!

  10. #10
    Join Date
    Jan 2009
    Posts
    8
    Yep, it worked!!!! YAY!!!
    thanks and putting all the subreports in the detail section worked fine, as you suggested.

    thanks!

  11. #11
    Join Date
    Jul 2010
    Posts
    1
    Quote Originally Posted by LisaSimpson View Post
    I found out what I was doing wrong. When I made the separate tables for the subreports, I forgot to remove that data from the source for the main report and that's why it was duplicating. Once I remove that, it should work fine.

    thanks anyway!
    Lisa, I am facing the same exact situation. Seems like you got your problem solved, but I am not sure I quite understand how you did it? My subreport has no connection (i.e. no Child Link etc) to the Main Report. My client just wants the info there. The subreport prints correctly, when printed by itself. When pulled up in through the Main Report, it seems to be holding a history of the 1st record from the last time it was ran....resulting in an extra (previous) record.

  12. #12
    Join Date
    Jan 2009
    Posts
    8
    Nemesis7,

    As I understand it, you're master report & subreport HAVE to be connected with at least one linked field, otherwise, for each record in your master report, I expect it will match it up to every record in the subreport.

    For example, if you have names of customers in your master report & addresses in your subreport (pulling data from different tables or queries) & they are NOT linked with at least one field in the master and child sections linking the data with something like customer number that is in both tables or queries, then the report will match every customer with every address & that would be totally inaccurate.

    Does that make sense? I don't know what the nature of your data is, so I can't be more specific. If you still need help, give me example of data & I will see if I can help.

  13. #13
    Join Date
    Jul 2010
    Posts
    6
    Quote Originally Posted by nemesis7 View Post
    Lisa, I am facing the same exact situation. Seems like you got your problem solved, but I am not sure I quite understand how you did it? My subreport has no connection (i.e. no Child Link etc) to the Main Report. My client just wants the info there. The subreport prints correctly, when printed by itself. When pulled up in through the Main Report, it seems to be holding a history of the 1st record from the last time it was ran....resulting in an extra (previous) record.
    Same problem, I'm getting

  14. #14
    Join Date
    Jan 2015
    Posts
    1
    I believe you need to unlink your main report to a query or table. Then you just need to build your report from exsisting subreports exclusively.

    Quote Originally Posted by nemesis7 View Post
    Lisa, I am facing the same exact situation. Seems like you got your problem solved, but I am not sure I quite understand how you did it? My subreport has no connection (i.e. no Child Link etc) to the Main Report. My client just wants the info there. The subreport prints correctly, when printed by itself. When pulled up in through the Main Report, it seems to be holding a history of the 1st record from the last time it was ran....resulting in an extra (previous) record.

  15. #15
    Join Date
    Jan 2009
    Posts
    11
    In Access 2003, and maybe other versions, if you have a report with subreports where the subreports are not related to each other or to the main, that is your main report simply serves as a means to hold the subs, you may get instances of duplicate records in the subs. Often the solution is to create a group header in the subs. If there is no field to group on, either use or create a field that has a common value for all records.

    HTH,

    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •