Data For a Subreport Within a Subreport Is Duplicated 8x On Export

[ApocY85]

Hello everyone!

I am experiencing a peculiar issue with an Access report. This is the first time I've ever made an Access report, however I have made numerous, fairly complex reports in Crystal and SSRS (BIDS).

I am reporting off of a fairly large-scale database, as it contains numerous tables, of which some use 1-1, and others use 1-* relationships.

To describe my problem, let me use certain tables as an example. I have one table called tblClaim which has a 1-* relationship with a table called tblPassenger. Each record within tblPassenger can contain multiple records from tblPhone. In my report, I have a subreport called srpPhone which is contained within a subreport srpPassenger. srpPassenger is then contained within the main report.

When I view ("Print Preview") the report or print the report out on paper, everything looks as it should. However, when I export the report (when I use DoCmd.SendObject or DoCmd.OutputTo, or I click File > Send To or File > Export), the report looks different. Instead of having one, two or three phone numbers for each passenger, the report shows 8 of each phone number (8, 16, or 24 phone numbers). Not only is the data duplicated, but the labels for these phone numbers are duplicated as well, even if there is no phone number for that passenger.

The following URLs provide screenshots of a section of the report with blackened-out data (to hide personal information) and a fake phone number:

http://www.mediafire.com/?s8bykkxp5ssg50x

http://www.mediafire.com/?h5pdn6v2s1kc9e8

One screenshot shows how the phone number should be displayed, and the other illustrates the report exported incorrectly.

Please let me know if more information is required. I greatly appreciate the help that anyone can provide! Thank you in advance for your time.

 

[ApocY85]

I just happened to notice that the srpPhone subreport is used 8 times. I took out one instance of it, and the duplicated phone numbers each decreased by 1. So, I then went on to think that you cannot use the same subreport twice within a single report, but found resources on the internet that claim you may. I am now trying to play around with the master/child linked ID fields, yet nothing seems to work. Any thoughts?

 

[dhookom]

Why you would you use the same subreport more than once?
Can we assume the main report is based on only tblClaim. It contains a subreport based on tblPassenger and has a parent/child link on ClaimID (?). The passenger subreport contains a subreport based on tblPhone with a parent/child link on PassengerID (?).

Duane
Hook'D on Access
MS Access MVP

 

[ApocY85]

Rather than creating numerous copies of the srpPhone subreport for each person type (passenger, renter, TP passenger, etc), I thought it would be cleaner and easier to maintain to use a single subreport.

All information is based off of the "main table" tblClaim. Although the table does have a claim_nbr field (and identifier), it is not used within the other tables. However, there is an key that can be used called Session_id.

The parent/child link between, say, srpPassenger and srpPhone is:

PARENT --> Session_id;passenger_string;Passenger_id
CHILD --> Session_id;person_type;person_id

where person_type contains just that ("Passenger", "Renter", "TP Passenger", etc), person_id is the identifier for that particular person, and Session_id as mentioned above.

I have each *instance* of the srpPhone subreport named uniquely (srpPassengerPhone, srpRenterPhone, etc).

Another interesting thing to note in the Bad.bmp file I linked is that the selected records are correct, just duplicated. Also, all the field labels are duplicated one after another, THEN all the data is duplicated. This makes me wonder if there is nothing wrong with the master/child links, but rather something with each instance of the srpPhone subreport.

Any further thoughts? Once again, thank you for your help!

 

[dhookom]

I wouldn't expect to see Session_ID in the phone table. I would expect the link of the single subreport for phone would be Person_ID and Passenger_ID.

What are you doing with the passenger types? Isn't this just a grouping level in the passenger subreport?

Duane
Hook'D on Access
MS Access MVP