OK, if I'm understanding this correctly, you want to keep the data in which OrigID_Reposted = 1, and exclude the data in which OrigID_Rev = 1, and the original data, in which both OrigID_Rev is NULL and OrigID_reposted is NULL.  Is this correct?  Is this pattern always going to hold?

The SQL for something like this would look like:

SELECT AH1.*
FROM AcctHistory AH1
LEFT JOIN
    (SELECT Acct FROM AcctHistory WHERE OrigID_Rev IS NOT NULL) AH2
    ON AH1.Acct = AH2.Acct
WHERE AH1.OrigID_Reposted IS NOT NULL
    OR AH2.Acct IS NULL

This would return the third row of 98, because OrigID_Reposted isn't null, and the first row of 99, because there isn't a row where OrigID_Rev isn't null.

Unfortunately, doing this sort of thing natively in Crystal is tricky.  It doesn't support subqueries, or most tricky joins.

There is one option, if you don't mind pulling down all the data, and simply suppressing the data you don't want.

Create a group based on Acct.  Create a conditional suppression on the Details section.  The formula should look like:

Count ({OrigID_Rev},{Acct}) <> 0 AND IsNull({OrigID_Rev}

This will suppress the row if there is a reversal transaction for the account, and the current transaction is not a repost.





Edited by Lugh - 16 Nov 2007 at 7:06am