i have 2 table ARCASH and ARMAST
i select from arcash  -  invno,custno, paidamt
i select from armast  -  invno,custno,invamt

when i drop from the arcash table invno custno paidamt it work nice
now when i drop from the armast table invno i get that nice but it mess up the arcash invno custno paidamt one value get repeat over and over the first record value of the arcash

i have no clue what to do here

kevin.

ok but b4 i go i tell what i am trying to do
in the arcash it has record for customer that wont be in the armast table and the same way around for the armast and arcash so i am trying to get custno to display all armast and arcash





Edited by kevincloud999 - 15 May 2008 at 9:43am

hey lugh i did what you said samething one record over and over
what do you mean by tests for null?


Edited by kevincloud999 - 16 May 2008 at 1:12pm

I would be willing to bet that, if you look far enough down your report, the INVAMT does change.  At which point, you see all the PAIDAMT values in order again.  Basically, I think you've ended up with a very large cross-product.

One question that pops into my head is this: What role, if any, does the invno field play in this relationship?  Can you have multiple invno records per custno?  If so, I'd wager that's where your cross-product is happening.

I'm thinking you're going to need to use the SQL Command to pull this off.  Mostly because of the UNION bit.  I think the Command should look like:

SELECT Cash.*, Mast.*

FROM

    (SELECT CustNo, InvNo, PaidAmt FROM ARCASH
     UNION
     SELECT CustNo, InvNo, PaidAmt FROM ARYCSH) Cash
FULL JOIN
    (SELECT CustNo, InvNo, PaidAmt FROM ARMAST
     UNION
     SELECT CustNo, InvNo, PaidAmt FROM ARYMST) Mast
ON CASH.CustNo = Mast.CustNo AND Cash.InvNo = Mast.InvNo

Now, obviously, you're going to want to flesh that out a bit with WHERE clauses, but I think the structure of the final query should be clear.

I added a clause to the join condition for the InvNo fields to also match up.  That may not be what you actually want.  Another option is to leave the InvNo out of the equation altogether.  In order to do that, you will need to summarize the data, so that you have a single record per CustNo on each side of the join, like so:

SELECT Cash.*, Mast.*


FROM


    (SELECT CustNo, SUM(PaidAmt) AS PaidCash
     FROM
        (SELECT CustNo, PaidAmt FROM ARCASH
         UNION

         SELECT CustNo, PaidAmt FROM ARYCSH)
     GROUP BY CustNo) Cash

FULL JOIN


    (SELECT CustNo, SUM(PaidAmt) AS PaidMast

     FROM

        (SELECT CustNo, PaidAmt FROM ARMAST

         UNION


         SELECT CustNo, PaidAmt FROM ARYMST)

     GROUP BY CustNo) Mast

ON CASH.CustNo = Mast.CustNo