Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2005
    Posts
    29

    Unanswered: How to Represent One to many relationship on the report

    I have one to many relationship tables.At the table level the relationship is clearly represented by the main table (on the 'one'side') and the subdatasheet for the 'many' side(second table).These tables are being front-ended by a mainform(on the 'one' side) and a subform(on the 'many' side).My reports are based on underlying queries.My concern here is on how to represent this data relationships on the reports???? with subtotals and grand totals.????I have tried to put a subreport at a details section of the main report but there was some data duplicates....the same applies when I ran the query itself....is there any suggestions???Below is the sample illustration:
    Here MainTable One(on the 'one' side)relates to Second Table Many(on the 'many' side)

    Table One Table Many
    Record A A1,A2,A3,A4
    Record B B1,B2,B3,B4

    The Detail report should display
    Record A
    ` A1
    A2
    A3
    A4
    subtotalsA

    Record B
    B1
    B2
    B3
    B4
    subtotalsB

    Grand totals:Record A + Record B + subtotalsA + subtotalsB

    Could this be represented in summary form also????
    IS there any way to improve the above representation????
    Any suggetion is highly welcome and appreciated!!
    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Read up on Grouping Levels. They will do what you want.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Teddy's right about the grouping levels for reports, but what problem are you having when you run just the query without the report?

  4. #4
    Join Date
    Nov 2005
    Posts
    29
    Well thanks. Now I can manage to group them accordingly .But the problem currently is how to get grand totals from those groups...I have used the folowing solution but it doesnt work:

    Subreport name = SalesSubreport
    Field Name = ProductA
    TextBox name = ProductACost - at the report footer section
    ControlSource :=sum([ProductACost]) - for the above textbox,these are subtotals,seem to work fine

    Mainreport Name : Sales
    TextBox name= TotalProductACost
    ControlSource:=[SalesSubreport].[Report]![ProductACost] -at the mainreport footer section

    Is there anyproblem with above expressions?????By the way ,How can I suppress detail section so that I can have a summary version of it???

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    put some code in the relevant reports on print or on format report to set cancel = true - this inhibits printing

    to get your totals in the main report - don't understand

    you could repeat you sum formula in hte sub reports report footer

    Incidentaly it sounds to me as if your report design is iffy. I'm guessing htat you should create a query incorporating all relevant fields and use that query to do your report, using appropriate sorts & grouping. The whole ethos behind relational design is that you should be able ot acess different views of your data depending on the relational keys. As far as reporting is conecerned it shouldn't matter how the tables are designed. Effectively your reports (and forms) can treat a query (view in SQL server speak) as a psuedo table.

    HTH

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What? Sub report? How did you "group" these things? I never mentioned anything about subreports...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Just goes to show Teddy, now matter how you (or Microsoft) want 'em to do a task there is always soemone who wants to do it their own way, and wonder why they hit troubles.

  8. #8
    Join Date
    Nov 2005
    Posts
    29

    summarized version of the detailed report

    Thanks very much for your inputs.Well I could organise my reports using grouping levels concepts and now they are working fine.Now I just want to produce a summarised report for that detailed report ie I just want to suppress a detail part of subreport and hence to remain with only subtotals.I have tried to put visible property=No (for the subreport) ,this works fine, but with lots of blank spaces between one record and another....is there any way to do it better???
    Last edited by fadone; 01-02-06 at 09:09.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    suggest you use a parameter pulled in form a form, or possibly when you load / open the report

    if the parameter=true then set the Cancel flag in the report details on print (possibly on format - I forget which) events.
    eg
    Cancel=<MySummariseParameter>
    HTH

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Subreports are NOT the same as grouping levels, in fact, not even close...

    Please clarify EXACTY what you did...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Nov 2005
    Posts
    29
    Quote Originally Posted by Teddy
    Subreports are NOT the same as grouping levels, in fact, not even close...

    Please clarify EXACTY what you did...
    Well I have created a group level on my report through "Sorting And Grouping " option, then I put my subreport into this group level.I have also defined subtotals for the subreport at this level.The report is working very fine except as I mentioned earlier,am still struggling to get a summarized report in a proper way.
    I hope you have understood...

  12. #12
    Join Date
    Nov 2005
    Posts
    29
    Quote Originally Posted by Teddy
    Subreports are NOT the same as grouping levels, in fact, not even close...

    Please clarify EXACTY what you did...
    Well I have created a group level on my report through "Sorting And Grouping " option, then I put my subreport into this group level.I have also defined subtotals for the subreport at this level.The report is working very fine except as I mentioned earlier,am still struggling to get a summarized report in a proper way.
    I hope you have understood...

  13. #13
    Join Date
    Nov 2005
    Posts
    29
    Quote Originally Posted by Teddy
    Subreports are NOT the same as grouping levels, in fact, not even close...

    Please clarify EXACTY what you did...
    Well I have created a group level on my report through "Sorting And Grouping " option, then I put my subreport into this group level.I have also defined subtotals for the subreport at this level.The report is working very fine except as I mentioned earlier,am still struggling to get a summarized report in a proper way.
    I hope you have understood...

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why are you using a subreport? This is what I'm asking for.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Nov 2005
    Posts
    29
    the reason is to represent one to many relationship on the report.............

Posting Permissions

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