Access Report Creating Duplicates

[ZenDiagram]

I have a report with two subreports.

When printing, if a parent record has more than one child record, which shows as a list in the subreport, access duplicates a report for each subreport row (child record) when printing.

I'm new to this, so my explanation may be unclear.
I have attached a cut down version of the db.
The report is 61 Application.
View it in print preview and you will see that it is duplicating the report.

Please help!

Thanks!!!

 

[Ranman256]

Either :
Key the subTable to prevent duplicates,
Or
Make a query to show unique records....Set UNIQUE VALUES= true.

 

[plog]

This is not a report issue. This is a data issue. Here's how you work on it:

Get the SQL from the Reports Record Source property and paste it into the SQL view of a new query. Save that query as 'ReportSource'.

Run that query and you will see that the data is not truly duplicated. When I run it, I get 3 distinct records. For example, in the [Deed Book] column you have the values 15555, 15644, and 12345. Which one of those 3 values do you want on the report? Why not the other 2?

 

[ZenDiagram]

Let me clarify:

There is no duplicate data in the underlying tables/queries. Every record is distinct.
I want to show all data, or child records, on my report -for a certain parent record.
For example,
C000 is the Account # and the 'parent' record.

the subreport: "rpt_Chapter Accounts Query subreport" shows all 'child' records for C000.

the subreport: "Forestry Details Query subreport for Application" shows all 'child' records associated to C000.

The main report and sub reports are linked by the Account #.

My issue is,
when trying to print the 61 Application (the report) the whole report is duplicated based on (I'm guessing) the number of child records.

If C000 has 3 child records it will print 3 applications. I only want 1 application showing all three child records.

I hope this helps...

 

[plog]

Again, this is a data issue. Working with and thinking in terms of the report just adds an unnecessary layer of complexity. Copy out the SQL like I said and put it in a query. You need to get that query down to 1 record if thats how many instances of the Detail section of your report you want.

My guess is you've included an unnecessary data source in that query.

 

[ZenDiagram]

"Again, this is a data issue. Working with and thinking in terms of the report just adds an unnecessary layer of complexity."
- sorry for the complexity, I just need this report to stop printing duplicate detail sections.

"Copy out the SQL like I said and put it in a query."
- I did. and all values are unique in the query.

"You need to get that query down to 1 record if thats how many instances of the Detail section of your report you want."
- I need to show all (3) unique records in 1 instance of the Detail section. Is this possible?

My guess is you've included an unnecessary data source in that query.
- not sure how to find this out/ what constitutes as unnecessary.

Thank you plog for your patience

 

[plog]

- I need to show all (3) unique records in 1 instance of the Detail section. Is this possible?

Huh? 3 unique..1 instance? You make no sense.

The Details section shows once for every record your report is based on. Your report is based on a query that generates 3 records, thus the Detail section repeats 3 times. If that is not what you want, then you need to make your report based on a query that generates 1 record.

My guess is you've conflated what needs to go into the main report and your sub reports. My suggestion is to move the SQL your report is based on to a new query and save it. Then start a new report and copy elements over from your existing report to the new one and run it each time to see which element causes the "duplication".

 

[ZenDiagram]

Thank you, I will give that a try.

 

[arnelgp]

see the record sorce of the report i remove unnecessary fields. also grouped them.

 

[ZenDiagram]

arnelgp,

Thank you! That seems to have worked!
Now I just have to replicate in the original database.

Thanks again!!!

 

[arnelgp]

youre welcome.

 

[arnelgp]

cant help but notice that the boxes begind the 2 subreports are not adjusting to their height. tried to fix that. see the code behind the 2 subreports.