Duplicate Data from Multiple Tables

[MikeCopeland]

Using CR 8.5...
I have a problem with SQL access of multiple tables that's producing incorrect CR results.
Here is how the data looks:
//////////////////////////////////////////////////////
Table1
ID: 212283
Table2
ClientKey: 212283 Enroll: 2/2/2007 Close: 5/1/2007
ClientKey: 212283 Enroll: 6/2/2006 Close: 2/1/2007
ClientKey: 212283 Enroll: 4/7/2005 Close: 3/31/2006
//////////////////////////////////////////////////////
I am linking the ID to the ClientKey...
Of the 3 records in Table2 I wish to process ONLY the 1st 2, and I need to link to subsequent tables via them. I am using a formula to qualify the record selection:
//---------------------------------------------------
{ClientEnrollment.Enroll} <= {?EndDate}
AND ({@Discharge} >= {?StartDate} AND
{@Discharge} <= {?EndDate})
AND {Clients.ClientLastName} <> "TEST"
//---------------------------------------------------
And the Discharge formula is:
//---------------------------------------------------
// @Discharge
WhileReadingRecords;
if IsNull({ClientEnrollment.Close})
then {?EndDate}
else {ClientEnrollment.Close}
//---------------------------------------------------

The problem is that, although my report shows 2 RECORDS (as it should), the data in them is DUPLICATED: it's the data from the FIRST record in Table2. How do I correct this? TIA

 

[groggle]

From what you describe, the fact you are seeing two records from the joining table is not surprising. An equal join will return a matching record in table 2 for each matching record in table 1. 2 records with the same ID in table 1 will return 2 duplicate records from table 2.

Some possible solutions:

Group the report by the ID field and summarize the data and display it in the Group Footer. This way you'll only see one line per ID.

Create a subreport to include the data from the second table, link it by the ID and summarize the data in the subreport to only show one row per id. Place the subreport in whichever section you wish to see it. Usually the details or group footer. Subreports do slow the processing time of the report.

 

[MikeCopeland]

I have found the problem, and it's somewhat as you describe (as I understand your reply). I reconstructed the report, piece by piece, to see where it fails (starts to display some duplicates). For the first 3 tables I linked, it was okay; when I added one more (for an associated piece of information required in each Detail line), I immediately saw the duplicates...
It turns out that the last table I linked had more than one record which matched the key in Table1. Thus, it appears to me that I was careless in setting up and qualifying the tables and links: when I add more "Select" criteria to exclude the non-pertinent records in the last table, the report now is okay.
I'm replying to your post not so much to give you feedback - which I presume isn't needed - but to alert other CR users of this pitfall/issue. I hope it helps...