Report Span with Subreports

[shelby55]

Hi

I am working with Access 2002.

I have a report that is set to show at least 12 months of data. Thanks to MajP I've been able to set a "start month" which is selected by the user and then that month plus 12 afterwards show on the report. The query is such that each month has its own data i.e. M1cases, M1days, M2cases, M2days up to M12cases, M12days.

I knew that I could fit 6 months of data on the same report widthwise. I thought that for formatting it might be best to split out the data within 2 subreports i.e. M1 to M6 in subreport 1 and M7 to M12 in subreport 2. But now I have a problem in that if there is 6 months or less of data in the database subreport 2 will still print but with no data.

The other reason for having 2 subreports is so that I could show the group field on each i.e. the report is by patient disposition and even if it grouped to using the second page (so second subreport) the user would still have the disposition printing on all pages.

So should I place all 12 on one and it will only skip to page 2 if required (and not print the disposition for the second page)? Or is there another way to not have subreport 2 print if no data exists? Thanks.

 

[shelby55]

Hi

I'm hoping someone can answer this? I've searched the web and this site and can't find a resolution....thanks.

 

[dhookom]

I'm not sure how you have this set up. I would use a combination of the FAQ in this forum regarding monthly crosstab reports as well the FAQ in the queries forum for creating multi-values crosstab queries.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

Thanks for responding. I actually have the queries and everything the way I want it...the problem is when the data expands beyond 6 months.

So the data looks like this:
[tt] Apr May Jun Jul
Cases Days ALOS Cases Days ALOS Cases Days ALOS Cases Days ALOS
To Home 5 20 4.0 10 100 10.0 5 25 5.0 8 80 10.0
Died 15 30 2.0 20 100 5.0 8 40 5.0 2 20 10.0[/tt]

The above report is a subreport placed in a main report that isn't based on a query or table..it is acting as shell for subreports (there will be 5). Whether the data starts at April or whatever is based on the start date selected via a report dialog box by the user.

The query is combining all 12 queries that I have for month1, month2, month3 etc. The first one is "startdate" which is a function driven by the entry in the report dialog box. The others are an "addmonth" function adding to the startdate so that the months of data aren't "hard coded".

The report will display up to 12 months depending on the start date and how many months of data are in the database (the data will be updated monthly) but not all 12 can fit on an 8 1/2 x 11 page landscape orientation.

Questions:
1. Do I stretch subreport for all 12 months and it will shrink if not required for full size? I tried this but it prints blank 2nd page but maybe I'm not doing something correctly
2. Do I add the last 6 months in another subreport? If so, how do I get it not to print if months of data isn't > 6 months?

Thanks very much.

 

[dhookom]

You mention 12 queries and 5 subreports. Are there 5 different record sources or 1?

I think the layout of the report depends on what you (your users) want to see.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

HI

Each subreport will be set up the same i.e. based on a query which is based on the 12 months set beginning with "start date" selected by user and adding a month for each after that.


For now let's just discuss the one subreport. I guess I'm not describing well because I thought I already advised what I want to see which is 12 months of data if there is 12 months but if under 6 months then I don't want an extra page to print which is what is happening now. Thus my question: split 12 months into two subreports which would sit side by side in the main report if I can somehow get the subreport not to print if no data or how can I do this so the blanks aren't producing another page. Thanks.

 

[dhookom]

So each subreport has the same record source which includes columns for all 12 months. The first 6 months are bound to controls in the first subreport and the second 6 months are bound to controls in the second subreport.

I think you could just set all of the controls in the second subreport to can shrink. Also set the subreport to can shrink.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

I did both of the things you suggested and it didn't work....a blank page still showed up on page 2 even though there wasn't any data.

Other suggestions?

 

[dhookom]

Did the second subreport take any space? Did anything shrink? Are the bound values in the second subreport not null?

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

No to all of your questions which is why I couldn't figure out why it was still showing.

 

[dhookom]

If you controls in the subreport aren't returning Null then they won't shrink. Even one control horizontally will cause the area to not shrink.

I would either make sure the non-populated fields are null rather than 0, use some code to hide the subreport based on some values from the query which might mean adding a record source for the main report, or re-design your record sources using the methods that I suggested earlier.

It's a bit difficult to make an exact recommendation without understanding your original data structure as well as the SQL of your recordsources.

Duane
Hook'D on Access
MS Access MVP