Problem With comparing numbers

[Cort]

Sorry for the weak discription of the problem but I'll explain the best I can.

CR 8.5

Here is the table structure:
TRNX# TRNXTYPE TRNXAMOUNT CONTRACT
101 PD 500.00 10234
102 PD 750.00 10156
103 CN 500.00 10234
104 PD 1200.00 10234

Here is what I am trying to do. PD = check issued, CN = cancelled check. I'm trying to isolate contracts where we are cancelling and reissuing checks that we shouldn't be (and, therefore, wasting money).

So I want to compare and isolate PD's and CN's on the same contract with the same TRNXAMOUNT. Any idea on how to accomplish this?

Thanks

 

[synapsevampire]

Group by the Contract and sort by TRNXAMOUNT.

Now a formula in the details section will tell you what you're after:

if next(TRNXAMOUNT) = TRNXAMOUNT
and
next(TRNXTYPE) <> TRNXTYPE then
&quot;Looks like a cancel!&quot;

Normally a system would have a check referenced check/transaction number which makes this much simpler, you could do a self-join by the check/transaction number, very odd that you don't.

-k

 

[lbass]

First group by {table.contract} and then by {table.trnxamount}.

Then go to Report->Edit Selection Formula->Group and enter:

count({table.trnxamount},{table.trnxamount}) > 1 and
distinctcount({table.trnxtype}, {table.trnxamount}) = 2

Now your display would look something like this, assuming there could be multiple entries per type per contract with the same trnxamount:

Contract 1234
Trnxamount 500
CN 500
CN 500
PD 500
PD 500
PD 500

If you want to summarize the total amount cancelled per Trnxamount, create a formula:

if {table.trnxtype} = &quot;CN&quot; then {table.trnxamount}

Place this in the details section and insert summaries on it (a sum and a count) and drag the fields to the group 2 header (trnxamount). Adding some text fields to the header, you could then suppress the details if you wished for a display like this:

Contract 1234
Trnxamount 500 Number of Cancellations: 2 Total Cancelled: 1,000

-LB