Complicated Main Report with 9 Control Fields reporting Out of Order

[SMarkSch]

Hello,

I am trying to produce a master report with nine different control fields to group and sort my data by. The first control field is always company code. The eight remaining fields (2-9) define the rest of the reports. This means there are only two control fields being reported at any one time (per subreport).

I am producing eight different subreports reporting the same information in the columns after the control fields.

In the end I want one master report with sequental page numbering (Page 1 of #) for these eight subreports.

The final report needs to look as follows as one master report with sequental page numbering (Page 1 of #):
Company Code 1 - by Control Field 1
Company Code 1 - by Control Field 2
Company Code 1 - by Control Field 3
...
Company Code 1 - by Control Field 8
Company Code 2 - repeat as above, for all remaining Company Codes

By accident I was able to produce a master report as follows:
Control Field 1 - for All Company Codes sequentially
Control Field 2 - for All Company Codes sequentially
Control Field 3 - for All Company Codes sequentially
...
Control Field 8 - for All Company Codes sequentially
I did it by creating eight reports each with their own source query and then a master report that pulls in the eight reports as subreports.
I had a sample report with around 40 pages. When I changed the source query from eight seperate queries to one master query the number of pages blew up to 30,000+ pages with information being a cross product of some kind.-So I dont think this is the way to go.

I can hold my own when it comes to tables and queries but am only a novice at reports. I suspect I may need to use VBA to accomplish this. I will be a novice but am usually a quick leaner.

Any help would be appreciated.

 

[dhookom]

I'm confused by the need or context for something like this. What is a "control field"? Can you help by giving us some idea of the data?

Duane
Hook'D on Access
MS Access MVP

 

[SMarkSch]

Sorry for the confusion, but thanks for your help so far...

The data is in an insurance concept and is a block of insurance policies and due to privacy concerns I cannot supply example datal, but if needed could mock up some data.

The control feilds (as I call them-I think it's and old Easytrieve language term) are something that I would want to group and sort the data by.
Control Field 1 is company code
2 Plan Code
3 Issue Year
4 Issue State
5-8 similar type fields, etc.
For each of the eight reports only two of these fields (control field 1 and the control field 2-9) would be used at any one time for grouping and sorting purposes. This would mean that the company code totals would be identical for each of the eight reports.

As far as the need, simply put the report is replacing a legacy system (Fortran) report and my management is saying it must look exactly like the old report but the process to create it must use specific modern era products including MS Access for the reporting step.

Thanks in advance. Any help would be appreciated.

 

[MajP]

Main form:
recordsource: Select companyCode from some table order by companyCode
make header for company code

Detail section has 8 subforms
subForm1: sorted and grouped on plan Code linked to main form by companyCode
subform2: sorted and grouped on Issue year linked to main form by companycode
subform3: sorted and grouped on Issue state linked to main form by companycode
subform8: sorted and grouped on field 8 linked ot main form by companycode

 

[dhookom]

Assuming you want to allow the user to select any one of many fields from the [Employees] table to sort/group by in your report, you could:
1) Create a form [frmRptSort]
2) add list boxes with a type of Field List and control source of your table/query name. Name them lboSort1, lboSort2, etc
3) Create your record source query like:

SELECT Choose([forms]![frmrptSort].[lboSort1].[listindex]+1,[EmployeeID],[Lastname],[MI],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region]) AS Sort1, 
Choose([forms]![frmrptSort].[lboSort2].[listindex]+1,[EmployeeID],[Lastname],[MI],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region]) AS Sort2, 
[green]--- add more Choose() here ---[/green]
Employees.*
FROM Employees;

Your report can then use Sort1, Sort2, etc for sorting and grouping.

Duane
Hook'D on Access
MS Access MVP