Access Report Creating Duplicates (1 Viewer)

ZenDiagram

Registered User.
Local time
Today, 10:41
Joined
Aug 28, 2018
Messages
37
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!!!
 

Attachments

  • Chapter Database_Example.accdb
    1.3 MB · Views: 111

Ranman256

Well-known member
Local time
Today, 09:41
Joined
Apr 9, 2015
Messages
4,339
Either :
Key the subTable to prevent duplicates,
Or
Make a query to show unique records....Set UNIQUE VALUES= true.
 

plog

Banishment Pending
Local time
Today, 08:41
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 10:41
Joined
Aug 28, 2018
Messages
37
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

Banishment Pending
Local time
Today, 08:41
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 10:41
Joined
Aug 28, 2018
Messages
37
"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

Banishment Pending
Local time
Today, 08:41
Joined
May 11, 2011
Messages
11,611
- 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".
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,169
see the record sorce of the report i remove unnecessary fields. also grouped them.
 

Attachments

  • Chapter Database_Example.accdb
    1.3 MB · Views: 138

ZenDiagram

Registered User.
Local time
Today, 10:41
Joined
Aug 28, 2018
Messages
37
arnelgp,

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

Thanks again!!! :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,169
youre welcome.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,169
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.
 

Attachments

  • Chapter Database_Example.zip
    532.6 KB · Views: 63

Users who are viewing this thread

Top Bottom