I have a problem with a table join (I think) and I'm not certain how to resolve it. I'm currently using an Inner Join. (I have other joins with no success).

Table A {oep70) = Invoice Details
Table B {slp20}= Payment Details

My report calculates sales commission on invoices based on details in the payment table (date paid, amount paid, etc). The invoice number in Table A equals a reference field in Table B.

{oep70.invn70}={slp20.lref20} except in one specific case...

When the customer is given Extended Terms on an invoice (3 equal payments for example), the field in {slp20.lref20} changes. If you take the invoice number 116942...

It's 116942 in {oep70} and 116942A, 116942B, and 116942C in {slp20}.

I'm not picking up any of the payments made against the Extended Terms invoices because their reference doesn't match the invoice number in {oep70}. They are being excluded altogether.

In my formulas, I have changed {oep70.invn70}={slp20.lref20} to {oep70.invn70}=(left({slp20.lref20},6)) but it's still not picking up the payments.

Any ideas? I cannot find any other unique way to join the tables together...

Thanks, DBlank.

I know next to nothing about stored procedures so it looks like I've got a little research to do.

I couldn't find a table that translates the join, but I was able to create a view and translate the join myself. Thanks for pointing me in the right direction!

Karen