Can use of subreport cause Grouping errors?

[Guest]

Greetings,
I have a report data sourced by a query. The query looks at two tables, one
of which holds the main identification records (which aircraft) and a
subtable holding the event records (what happened). The two tables are
related by a DMIID field and SerialNumber field. There is one record in the
main table to a maximum 3 records in the subtable

On the report I used the DMIID from the main table as a grouping field with
whole group kept together. In the detail section of the form I inserted as a
sub-report, the actual subform I have on the main data entry form. My desire
is to have the report return:

ReportHeader

Groupheader__________
DMIID

Aircraft Registration Serial Number Date

Detail_________
Subrecord 1 detail
Subrecord 2 detail
subrecord 3 detail


What I get is:


ReportHeader

Groupheader__________
DMIID

Aircraft Registration Serial Number Date

Detail_________
Subrecord 1 detail
Subrecord 2 detail
subrecord 3 detail



then

ReportHeader

Subrecord 1 detail
Subrecord 2 detail
Subrecord 3 detail


then

ReportHeader

Subrecord 1 detail
Subrecord 2 detail
Subrecord 3 detail

I understand what is happening - the report is returning a record detail for
each instance of a detail record it finds in the sub-table. What I can't
figure out is why. I have tried different grouping, setting the query to
unique records and no change.


Any suggestions?

gavin hill

 

[Marshall Barton]

You do not want the subtable data in both the main report
and the subreport.

Either get rid of the subtable data in the main report's
query and the group header, OR dump the subreport
altogether. In a simple case like this, I prefer to avoid
the use of a subreport when I don't have a good reason to
use one.

 

[Guest]

Understood.
However, for the report to be complete the data from both tables is needed.
Does Access have a method of combining two reports together in a
seperate/related manner? Or should I do an AdHoc make table query, perform
the report, then delete the AdHoc records. Or, behind door number three am I
making this much harder than it really is?

Thanks for your time
--
GmH

You do not want the subtable data in both the main report
and the subreport.

Either get rid of the subtable data in the main report's
query and the group header, OR dump the subreport
altogether. In a simple case like this, I prefer to avoid
the use of a subreport when I don't have a good reason to
use one.
--
Marsh
MVP [MS Access]

I have a report data sourced by a query. The query looks at two tables, one
of which holds the main identification records (which aircraft) and a
subtable holding the event records (what happened). The two tables are
related by a DMIID field and SerialNumber field. There is one record in the
main table to a maximum 3 records in the subtable

On the report I used the DMIID from the main table as a grouping field with
whole group kept together. In the detail section of the form I inserted as a
sub-report, the actual subform I have on the main data entry form. My desire
is to have the report return:

ReportHeader

Groupheader__________
DMIID

Aircraft Registration Serial Number Date

Detail_________
Subrecord 1 detail
Subrecord 2 detail
subrecord 3 detail


What I get is:


ReportHeader

Groupheader__________
DMIID

Aircraft Registration Serial Number Date

Detail_________
Subrecord 1 detail
Subrecord 2 detail
subrecord 3 detail



then

ReportHeader

Subrecord 1 detail
Subrecord 2 detail
Subrecord 3 detail


then

ReportHeader

Subrecord 1 detail
Subrecord 2 detail
Subrecord 3 detail

I understand what is happening - the report is returning a record detail for
each instance of a detail record it finds in the sub-table. What I can't
figure out is why. I have tried different grouping, setting the query to
unique records and no change.


Any suggestions?

gavin hill

 

[Marshall Barton]

Understood.
However, for the report to be complete the data from both tables is needed.
Does Access have a method of combining two reports together in a
seperate/related manner? Or should I do an AdHoc make table query, perform
the report, then delete the AdHoc records. Or, behind door number three am I
making this much harder than it really is?

Don't do either of those things. Just modify the main
report's query to eliminate the data that appears in the
subreport. If that still results in duplicate records, set
the query's Unique Values property.

The Link Master/Child properties will link the two so the
"detail" data in the subreport matches up with each record
in the main report.

 

[Guest]

Holy Cow Batman!

It figures the answer was in front of me all along. Thanks for turning on
the light for me Marshal.

Gavin Hill