Large Report with Multiple Sub Reports 1

[ccma]

Hi~
I am creating a database for our Client Services department. Currently all calculations are done manually and updated into excel - the reports were then emailed to the client in excel. Each client is assigned a "CSA" number and each tab in the spreadsheet would be titled according to the CSA number. I was asked to create a better/easier process, due to the fact that this department runs this data monthly for at least 50 specific clients. I have created all queries for the report and am almost home- so I thought until I started creating the report.

Problem:
The excel spreadsheet has the column headings by month and the row headings as the statistics. There are 41 pieces of information that need to be reported by month --- so eventually this report will have a rough total of 492 fields. In the end the data is separated into four separate queries (due to the fact that queries can only hold so many fields) and the report is composed of three sub-reports. When I do that, I get random blank pages. I have not inserted any page breaks or groupings in the report. The grow and shrink properties in the sub-report are set to Yes.
Are there any other options?
CC

 

[DougP]

if you have numbers like 492 logic tells you to break it up in smaller increments.

DougP, MCP, A+

 

[dhookom]

WHy would there not be 12 monthly records with 41 pieces of information? You haven't provided near enough information for someone to provide a good solution.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ccma]

OK the department head wants all previous months to be displayed on one page, and not have January on one page and February on another. The excel spreadsheet looks like this

January February March April etc...
$s 100.00 110.00 200.0 250.0
$s/Hr 25.00 30.00 50.0 60.00
Hrs
Contacts

All my data is entered into the database in the following format:
Month CSA# Dollars (Column Headings)
06/01/04 340 25000.00
06/01/04 320 50000.00
05/01/04 320 30000.00

After running the calculations needed - all the data is crosstabed. I end up with about 53 crosstab queries that make up the report - In the end I end up with four select queries and the crosstab queries within the select queries are linked by CSA#. Each of these four queries make up separate subreports for the exception of one select query which makes up the parent report or master report. When the three subreports are placed on the master report - I receive random page breaks and i have not inserted any page breaks or created any sortings and/or groupings.

 

[dhookom]

I would create a query (I think you have this) with results like:

CSA Mth Dollars DollarsPerHr Hrs Contacts ....
320 1 50000 ...
340 1 25000 ...
320 2 45000 ...

I would then use either a 12 column report or a report with 12 copies of the same subreport.

There are some sample crosstab reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Check out CrosstabReport.mdb. It might help.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ccma]

Thank you --- I looked at the website and it is exactly what I am looking for! Cherly