I am trying to use Crystal to report on 3 tables. One table, the inventory transactions table (ICFIT) is the controlling table. ICFIT does not contain pricing information so I have to get it from the other two tables SOFPL and SOFOD. If I were doing this in Foxpro, I would set relation (additive) to the two tables. ICFIT has one record per shipment. Table 2 is called SOFPL and it contains pricing information for certain of the inventory transactions but not all of them. I am joining the tables against Part_id, since that is the only column in common everywhere.

Some transactions in ICFIT are priced out of SOFPL, and some are priced out of SOFOD. I can tell by a formula, which record will price out of SOFPL and which will price out of SOFOD. When I do a left join of ICFIT against SOFPL and select the correct records I will get a report which makes sense so far, and leaves the SOFOD - priced records having null values in the pricing column. But when I left join to SOFOD I get way too many rows, since every row which is priced out of SOFPL picks up a collection of rows out of SOFPL. I only want to pick the first row or just one row from SOFOD when I am pricing out of SOFPL, since I am not using the columns from that row in that case anyway. The same thing happens for values priced out of SOFOD which pick up multiple records in SOFPL.

Should I use Subreports and just make two reports, one for SOFPL and one for SOFOD or should I do something SQL to eliminate extra rows