We help IT Professionals succeed at work.

Ms Access report format

Hi Experts,
I have a question about report design in access 2016.  I have a report to show the total of the Order and shipped from 1/1/2018 to 1/31/2020, we need to compare the total of each year.  The report is order by year then each month (from January to December), is anyway I can change the design to show both years side by side in one page?  instead showing each year in one page, I want to show two years side by side so that I can see the difference between 2018 and 2019 or show all three years on a report if possible, otherwise at lease show two years in a report. so the format will be look like below:

First Page of the report

Year     Month
  2018                                                                               2019
         January                                                                              January
                       Total of Order: 200                                                        Total of Order: 200
                       Total of shipped : 150                                                    Total of shipped: 90
       
         February                                                                          February
                       Total of Order: 300                                                        Total of Order: 300
                       Total of shipped : 150                                                    Total of shipped: 30

       December                                                                          December
                       Total of Order: 300                                                         Total of Order : 600
                       Total of shipped : 150                                                     Total of Shipped: 400
         

         
Second Page of the report    
2020
       January
                       Total of Order: 200
                       Total of shipped : 150


     
I tried to set the column 2 in the page setting, but it seems did not work.  any ideas?

Thanks,
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

Two column approach is the way to go....there's no other way to so it easily.


The biggest thing with the two column approach is that the width of the detail sections cannot be wider than the columns.


Probably the best way to move forward with this is to first get a report where the years a listed as you want one after the other.  Then move to the two column format.


To get the years, you'll probably want to do a group by on the year, hide the detail, and do a group header/footer.   Header will be the year name, followed by a footer with the totals.


Do you already have the output in a single column like that or no?


Jim.   



Author

Commented:
@Jim,
Yes, I already have s output in a single column, it shows (below is my original format in single column) :

Year
          Month            Total of Order/Shipped

2018
          January      
                                Total of Order
                                 Total of Shipped

          December

2019
          January
                              Total of Order
                              Total of Shippped

          December
 etc.

I tried the two columns, but some of the month got move to the second column and year 2019 was move to almost to the end of the page.  I can't figured it out how to make the two years side by side to view.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

On your footer for the year, set the New Row or Column property to After Section:


Jim.

Distinguished Expert 2017
Commented:

I think just changing to 2 columns might work if all the data will then fit on a single page.  If not, the report may not line up the way you want.


If the 2-column solution doesn't work for you, here are two other options.

1. Create two queries.  One for each year and then a third query to join the other two.  Use the third query as the RecordSource for the reports.  I would use alias names in the third query that call items year1 and year2.  Don't hard code the selection criteria in the first two queries.  Use a single field on a form to select the first year and you add 1 to select the second year.  But, if you want the report to compare any two years, then allow the user to enter both year1 and year2.

2. Use two subreports on an unbound main report.  let subreport1 select year1 and subreport2 select year2.

Author

Commented:
@Jim,
I did tried your suggestion, but it doesn't work.

@pathartman,
I'm the only one run this report and no need to select anything.  I already have two different queries, one for Order, one for Shipped then I combine use this two queries as third the query for this report.  as you said, the since it has to show 12 months, so the data don't fit in one page and shift have of the year to the second column and the year 2019 show in the bottom of the second column.

Author

Commented:
@PatHartman,
I used your way to create two subreports and add to a unbound report, it works.  Thanks!

Author

Commented:
@Jim @ PatHartman,
Thank you for your helped!!
Distinguished Expert 2017

Commented:

You're welcome.