Printing a complicated report for every month of a year 1

[SteveCop2]

I have devised a report which gives the following type of information for a single month:

ACCOUNTS OPENED
John Smith
Richard Jones
ACCOUNTS CLOSED
Jim Arnold
TOTAL OPEN AT END OF MONTH: 37

The data all comes from a single table and is presented using 3 sub reports, each fed with a suitable query.

What I would like to do is to print the above monthly group of reports not just once but 12 times consecutively, one for each month of the year. Now I am really stuck! I can;t see how, having printed the 3 sub reports once, I can change the month number and then print them again etc and control the loop so that they stop after 12 months. Having thought about this for days I can only see creating a mass of sub reports, each group of 3 working on a specific month number but when it gets that crude and untidy I have to be wrong! Can anyone help with a suggestion please?

 

[GingerR]

OK--so make the recordsource of the entire Report be a list of 12 months in the range you desire. You might accomplish this by creating a table that shows

ReportMonth

and has all months in eternity in it, then your report might be "Select * from tblReportMonths where Year(ReportMonth) = 2007" or whatever - whatever you want to do to get the list of 12 months. Might be that a user picks a Month/Year from a combo box on a form for the "First Month" or "Last Month" and hits a button which loops thru code that builds the "ReportMonths"; depends on what you are going for (just a single year's worth of months, or a variable range depending on what the user wants to see), but in any case there are a plethora of choices.

But anyhow, however you get them, the Recordsource of the Main Report will be a list of 12 months.

Then the recordsources of each subreport will include the criteria:

Reports!ReportMainName!ReportMonth

I do not use Master/Child links in subreport - i use the criteria.

So this way, The ReportMOnth is displayed on a page of the report, and the subreports are showing the data for that month. Each month gets it's own "page". The report will be 12 pages long.

Try that out and let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SteveCop2]

Fantastic!!!! Thank you very much indeed.

I had a similar inspiration shortly after posting (after 4 days I would have hoped for at least a germ of an idea!) but your solution is more stylish. I just went for a 12 month table and used the month number in the Master/Child link which most certainly works. However your method is much better, especially that I can select a range to print of less than 12 months. Excellent! I am interested in your idea of using the recordsource of the sub reports to specify the criteria - had'nt thought of that before. With this report, though, there will be relatively few lines so I dont mind month following month on a page. Haven't time to play with this further now but I'll come back in a couple of days. Very many thanks indeed for your excellent and clear guidance.

 

[SteveCop2]

OK - I made time (!) and the report works like a dream using all of your suggestions. The modification to recordsource is a total success too and I will be able to use that elsewhere as well as here. I am so pleased with your help - my very sincere thanks.

 

[GingerR]

Glad to help!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html