Been trying to make this very simple report for a while now and I cant get CR to filter the report the way I need it to!  And this is a 3 table, dead simple report.

Table #1 = STOCK (holds all details from our stock, uses primary key STM_AUTO_KEY)
Table #2 = ADJUST (holds an STM key and details of adjustments)
Table #3 = TRANS (holds an STM key and details of adjusments)

Tables #2 and #3 hold the same information about costs & quantity adjustments, but depending on how it is done in the software a single adjustment could be stored in table #1 or #2 or both!

So I created this selection formula which seemed to work fine...

IF    {STOCK.STM_AUTO_KEY} = {STOCK_ADJUST.STM_AUTO_KEY}
AND {STOCK.STM_AUTO_KEY} = {STOCK_TRANS.STM_AUTO_KEY}
THEN  {STOCK_ADJUST.COST_ADJ} <> {STOCK_TRANS.COST_ADJ}

The problem is that I still have several hundred double entries If there's more than 1 adjustment for that stockline (STM_AUTO_KEY) it cannot intelligently match the COST_ADJ fields to eliminate the doubles...  So if 4 adjustments are done a certain way, you could have 4 entries in ADJUST and 4 entries in TRANS.  Then CR cannot mismatch these....

Any ideas?

Of course the tables are linked, I have been using CR for almost 2 years now. This report seemed simpled at first...  I'll post some more table info...

[STOCK]
- STM_AUTO_KEY (primary key for the table & left outer join from this field to the two others)
- All other fields contain stock info and arent important for the report

[STOCK_ADJUST]
- SAJ_AUTO_KEY (its own primary key)
- STM_AUTO_KEY
- CHANGE_DATE
- COST_ADJ
- QTY_ADJ

[STOCK_TRANS]
- STT_AUTO_KEY (its own primary key)
- STM_AUTO_KEY
- CHANGE_DATE
- COST_ADJ
- QTY_ADJ

Im filtering all the stocklines that are in either of the "STOCK_XXXX" tables with the COST_ADJ field.  So that field must contain an amount in either table, this way I can calculate total cost adjustments of our stock daily, monthly, etc...

I noticed that depending on the adjustment, the same information can be inserted in both the TRANS & ADJUST tables. So I added this filter...

IF    {STOCK.STM_AUTO_KEY} = {STOCK_ADJUST.STM_AUTO_KEY}
AND {STOCK.STM_AUTO_KEY} = {STOCK_TRANS.STM_AUTO_KEY}
THEN  {STOCK_ADJUST.COST_ADJ} <> {STOCK_TRANS.COST_ADJ}

Find the double entry and if the cost is the same remove one... this didnt work too well but it did filter out about 20,000 stocklines out of 250,000.

Here's some typical data Im getting right now...