Randall,

I guess we were confused the way you described your problem. Two simple rules: if you do Union, in both cases you have to have the exact same names and records from one table would be appended to another. If you do JOIN, it means you join tables by some criteria (usually PK and FK), so you get a wide set.

In your case you want JOIN, right? You're right, use LEFT JOIN colections1 on returns1.companyID=collections1.CompanyID. You have to list all the fields which you want to include in your SQL. Don't use the same field name twice.

>I did this:
>
>SELECT returns1.bankname, returns1.portfolio, returns1.companyid, ;
>SUM(IIF(NOT EMPTY(returns1.settdate), 1, 0)) AS tot_ret, ;
>SUM(IIF(INLIST(trancode, '21', '22', '31', '32'), returns1.amount, 0000000000.00)) AS RET_CR, ;
>SUM(IIF(INLIST(trancode, '21', '22', '31', '32'), 1, 0)) AS RET_CR_NUM , ;
>SUM(IIF(INLIST(trancode, '26', '27', '36'), returns1.amount, 0000000000.00)) AS RET_DB, ;
>SUM(IIF(INLIST(trancode, '26', '27', '36'), 1, 0)) AS RET_DB_NUM ;
>FROM returns1 ;
>WHERE returns1.portfolio = cBank ;
>GROUP BY returns1.companyid ;
>UNION ;
>SELECT collections1.bankname, collections1.portfolio, collections1.companyid ,;
>SUM(IIF(NOT EMPTY(collections1.settdate), 1, 0)) AS tot_col, ;
>SUM(IIF(INLIST(trancode, '21', '22', '31', '32'), collections1.amount, 0000000000.00)) AS nCredit, ;
>SUM(IIF(INLIST(trancode, '21', '22', '31', '32'), 1, 0)) AS nCreditnum, ;
>SUM(IIF(INLIST(trancode, '26', '27', '36'), collections1.amount, 0000000000.00)) AS nDebit, ;
>SUM(IIF(INLIST(trancode, '26', '27', '36'), 1, 0)) AS nDebit_num ;
>FROM collections1 ;
>WHERE collections1.portfolio = cBank ;
>GROUP BY collections1.companyid ;
>INTO TABLE c:\dis_returns2
>
>*!* *COPY TO mis_collections TYPE XL5
>USE IN dis_collections
>USE IN collections1
>
>This give me the fields of the second SELECT and looks like it appends the results. The second table fields should be in the same record according to companyid. I guess I should be using the UNION command for what I want? If I use a LEFT OUTER JOIN here I get that desired results.
>
>Randall