locked
Formating an access report correctly using multiple fields to group RRS feed

  • Question

  • I've been working for a few days now to try and get an access report to print the desired records in a database without adding a blank page at the end or the beginning of a report.  Let me explain what I'm trying accomplish. I have a table that I need to group by 2 fields. An example of the table would be the following:

    Year

    Month

    Detail

    2001

    01

    Xxx

    2001

    01

    Xxx

    2002

    01

    Xxx

    2002

    01

    Xxx

    2002

    02

    Xxx

    2002

    02

    Xxx

    2002

    03

    Xxx

    2002

    03

    Xxx

    2002

    03

    Xxx

    2002

    04

    xxx

    So my primary group would be year and my secondary group with be month.  I need to print a new page anytime the group changes or the year changes. I’m building my report over a query that is sorted by year and month so I get the records in the order in the example.  In this example I should have a 5 page report.  I’ve tried everything in the book to property set all the parameters correctly to get it to work but have yet to accomplish a clean report without a funky page at the front or back of the report. 

    Any help solving this would be greatly appreciated. 

    Tuesday, November 7, 2017 3:46 PM

Answers

  • Hi,

    Have you tried adding a calculated column in your query combining both year and month and then use it for your grouping instead of separate groups for year and month?

    Just curious...


    • Edited by .theDBguy Tuesday, November 7, 2017 4:21 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, November 8, 2017 3:15 AM
    • Marked as answer by DataCowboy Thursday, November 16, 2017 3:12 PM
    Tuesday, November 7, 2017 4:20 PM

All replies

  • Hi,

    Have you tried adding a calculated column in your query combining both year and month and then use it for your grouping instead of separate groups for year and month?

    Just curious...


    • Edited by .theDBguy Tuesday, November 7, 2017 4:21 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, November 8, 2017 3:15 AM
    • Marked as answer by DataCowboy Thursday, November 16, 2017 3:12 PM
    Tuesday, November 7, 2017 4:20 PM
  • If you order the report by year, and then group it by month, giving the month group a group footer, you can set the ForceNewPage property of the group footer to 'After Section'.  A page break will not be forced following the final instance of the footer, however, so you will not get an empty final page.

    If you are not including any controls in the group footer, size it to zero height.  Otherwise it is possible for the empty footer to spill over onto an empty final page.  If you do include controls in the group footer be sure that there is no 'white space' below the controls, for the same reason.

    Ken Sheridan, Stafford, England

    • Proposed as answer by Terry Xu - MSFT Wednesday, November 8, 2017 3:15 AM
    Tuesday, November 7, 2017 4:56 PM
  • This is a brilliant idea and it worked.  Getting a report to format correctly in access is a nightmare when using multiple groupings.  It appears you need to try and do as much work as possible in the query leaving very little to the report to get it to work.  Thank you for your help.  I sincerely appreciate it.
    Tuesday, November 7, 2017 5:20 PM
  • Hi,

    You're welcome. Glad to hear you got it to work. Ken and I were happy to assist. Good luck with your project.


    • Edited by .theDBguy Tuesday, November 7, 2017 6:41 PM
    Tuesday, November 7, 2017 6:40 PM
  • Hi DataCowboy,

    It seems that your original issue has been resolved. I would suggest you mark useful reply to close this thread. If you have any other issue, please feel free to post thread to let us know.

    Thanks for understanding.

    Best Regards,

    Terry 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 8:06 AM