Report Data repeating or not showing

[John]

I have a database that contains three tables (PEP, PEP Comments, and
Milestones) The PEP Table has a PKey and is related to fields in PEP
Comments and Milestones via this PKey. One to Many in each case

I am trying to generate a report that will display info from the PEP table
(header type data), then display info from the Milestones table (may have 1
MS or 20), then display additional data from the PEP Table (more detailed in
nature) and finally display info from the PEP Comments table (may have 1
comment or 20).

When i base it on a query(for PEP #), it looks fine but only displays the
first of whatever number of milestones and comments related to that PEP. If
I try a report of all PEPs, I am getting things repeating themselves
throughout, a huge mess overall.

My tables are left joined.

Any help would be appreciated.

qwerty

 

[Rob Parker]

Hi John,

Base your report on the PEP table, and include two subreports to display the
data from the PEP Comments and Milestones tables, each based on the
respective table. Link the subreports via the Pkey field. You can position
the subreports whereever you want them in the main report.

It's the same principle as using a subform on a form.

HTH,

Rob

 

[John]

Rob - Thanks for the reply. Your suggested method is what I used to build
the report when it came out with blank (no field labels or columns)
sub-reports in the report preview. I tried it again today based on your
suggestion and I achieved the same result. My method:
1 - Built the PEP report based on PEP Table
2 - Built the Milestone report based on Milestone Table
3 - Built the Comments report based on Comments Table
All three reports contain the PEPID PKey field
4 - I then dragged the Milestone and Comments reports into the PEP report.
The two sub-reports' properties LINK CHILD FIELDS and LINK MASTER FIELDS are
set to PEPID

Am I enjoying too much eggnog?

Merry Christmas and thanks for your help.

 

[Rob Parker]

Hi John,

Is there such a thing as too much eggnog?

What you describe is correct, and should work. Are you sure that your data
does have matching records in the Milestone and Comments tables for the
PEPID entries in the PEP table?

I can't - off the top of my head at the moment - think of anything else that
would cause the problem you describe. If you'd like me to take a look at
this, you can send a (stripped-down if necessary) copy of your mdb file (in
A2002 or earlier format) to me at my gmail address: robpparker AT gmail DOT
com That's not something that I usually do, but this has got me intrigued.

Rob

 

[Rob Parker]

Posted for the benefit of anyone searching this thread at a later date:

I think there must have been too much eggnog ;-)

John sent me a stripped-down version of his database, and I discovered that
the problem was due simply to there not being comment /milestone records for
the first 10 (or thereabouts) records displayed in his report/subreports.
The record order for the report was set such that latest records displayed
first; the older records with child comment/milestone records in the "child"
tables were displayed later in the report; John had not scrolled
sufficiently far through the report to see these records.

The report/subreport method John used works exactly as it should.

Rob

 

[John]

Thanks Rob for the offline help.
As you discovered the issue was due to missing data for some
records, and the report/subreports approach is indeed correct.--
qwerty