Report with Single Subreport Printing Multiple Pages of Same Data

[MJ]

I know this is going to be a simple solution for someone in the community,
but having a problem with a Report producing more pages of information than
needs to. I am building a report consolidating analysis information
pertaining to a single issue from two tables (tblCommon and tblAnalysis)
having the Problem ID in common (one to many relationship).

Initially, thought the multiple page issue would be resolved by using a
sub-report. It did and it did not... it got all of the analysis entries down
to a single list vice a single entry printed on a single sheet, but now it is
still printing multiple pages just with more entries on each.

The report and subreport are using the same record source: qry-rpt 1

SELECT tblCommon.[Date Created], tblCommon.IdentifiedBy,
tblCommon.IssueDesc, tblCommon.System, tblCommon.ID,
tblAnalysis.Analysis, tblAnalysis.EntryBy, tblCommon.Resolution,
tblCommon.AssignedTo, tblCommon.PerComplete
FROM tblCommon LEFT JOIN tblAnalysis ON tblCommon.ID = tblAnalysis.ProbNum
WHERE (((tblCommon.ID)=[Enter Problem Number:]));

The main reports uses: Problem ID; Date Created; Identified By; IssueDesc;
and Resolution. The user is asked to enter the problem ID when accessing the
report.

The sub-report uses the Problem ID to filter tblAnalysis for the associated
entries.

Where am I going wrong? How can I get the report to quit giving me 6 pages
when 1 page with 6 entries on it will do?

Thank you in advance for the assist.
MJ

 

[Marshall Barton]

I know this is going to be a simple solution for someone in the community,
but having a problem with a Report producing more pages of information than
needs to. I am building a report consolidating analysis information
pertaining to a single issue from two tables (tblCommon and tblAnalysis)
having the Problem ID in common (one to many relationship).

Initially, thought the multiple page issue would be resolved by using a
sub-report. It did and it did not... it got all of the analysis entries down
to a single list vice a single entry printed on a single sheet, but now it is
still printing multiple pages just with more entries on each.

The report and subreport are using the same record source: qry-rpt 1

SELECT tblCommon.[Date Created], tblCommon.IdentifiedBy,
tblCommon.IssueDesc, tblCommon.System, tblCommon.ID,
tblAnalysis.Analysis, tblAnalysis.EntryBy, tblCommon.Resolution,
tblCommon.AssignedTo, tblCommon.PerComplete
FROM tblCommon LEFT JOIN tblAnalysis ON tblCommon.ID = tblAnalysis.ProbNum
WHERE (((tblCommon.ID)=[Enter Problem Number:]));

The main reports uses: Problem ID; Date Created; Identified By; IssueDesc;
and Resolution. The user is asked to enter the problem ID when accessing the
report.

The sub-report uses the Problem ID to filter tblAnalysis for the associated
entries.

Where am I going wrong? How can I get the report to quit giving me 6 pages
when 1 page with 6 entries on it will do?

The main report's query should not join to the analysis
table and the subreport's query should not include the
common table. The subreport control's Link Master/Child
Fields properties are used to synchronize the subreport to
the current min report detail.

You can probably get the desired result using your query and
a report without a subreport by using Sorting and Grouping
(view menu) to group on the ID field. Move the common field
control's to the group header section and only put the
analysis controls in the detail section.