I am trying to build a report off of a table much like an accounting transaction log where a subsequent (payment) transaction points to a previous (charge) transaction on the same table. Most of the data I need to display is off of the payment transaction but the column header in the cross-tab needs to pull off of the initial, charge transaction (the data is not stamped on the payment transaction).
Whenever I add the second instance of the table to my report (as the column label) my summary data is doubled or worse. I have tried to create a calculated field to filter and hopefully only sum up the payment transactions and use the calculated field as the summary data field but that is not affecting the totals.
The truly odd thing about this is the summary field on the charge transaction does not contain any data, only the payment transactions have data in this field so the sum totals should be correct despite the double pull from the table.
How can I read data from related records on the same table without affecting the sum totals?
Any guidance would be appreciated!! Thanks!