One to many relationship returning records multiple times

IO_Dork used Ask the Experts™
I have a report that uses a table that stores multiple records for a field in another table....basically a many to one relationship.

Problem is that when I try to return the many fields in table 2 against each corresponding field in Table 1, crystal reports returns that record in Table 1 multiple times according to table 2 instead of returning all the related records in Table 2 to each related record in Table 1.

For example,
For every record in Table 2 with TradeID = 12345, those records should all be returned in the report to one row bc each row needs to represent the record TradeID from Table 1
Table 1 is joined to Table 2 using the field TradeID. Table 2 contains multiple records for each TradeID. TradeID and the data associated with it should only be brought back once in the report. Is there anyway to accomplish this without having to group the report on TradeID?  I have existing reports that I don't want to have to reconfigure with grouping.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Each record contains data from both tables so there is no easy way to get the data from table 1 only once.

One method (don't necessarily recommend it) is to use a subreport for Table 2.
However, a subreport slows the execution of the report and if it is in the detail section as it seems then execution of the report may not be fast enough.

The best practice way to do it is as you suggested add a group on the TradeID field and put the common information in the group header and the details from table 2 in the detail section.

ANother way to do it somewhat the same but may be easier
Add a second details section
In details A put the common information from table 1.
IN details B put the information from Table 2
Suppress details A conditionally with this statement

Not OnFirstRecord and Previous({Table1.TradeID}) = {Table1.TradeID}

Open in new window



solutions sound good, but I still need to be able to sum all the dollar amounts from the fee record in table 1 with the adjustment items dollar amounts in Table 2 for every instance of TradeID. is that possible?


nevermind, is see using the details A and B solution allows for a custom formula that will sum all records up for each TradeID and that can be put into a Group footer for TradeID.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial