I believe this should be a trivial case, but after days and days of experimenting, I can't get it. I'm creating a report of customers' transactions. The customer table links to a transaction header table (default join type). There are 2 tables which link to the transaction header, one for normal transaction line items, one for refunds. There is no correlation between the line items in the transaction table and those in the refund table, and they may or may not share a header. I have linked these two tables to the transaction header with left-outer-joins. What I want in the dataset, for a given transaction header, are all normal line items followed by all the refund line items (or vice versa). What I get, however, is a record for each combination of transaction details and refund details, whenever they share a common header. So, for example, if I have transaction line items 1, 2, 3, and 4 for a given header, plus refund lines A and B for the same header (as might happen if a customer returned 2 products at the same time he bought 4), what I want is a data set of 1,2,3,4,A,B. Instead, what I get is 1A,1B,2A,2B,3A,3B,4A,4B.
How can I get the result I need? Is this simply a join issue, or must I weed out the duplication using complex formula scripts? Or a subreport? Or something else?
thanks.
John