Hi All,

I'm trying to join two tables.

The first table is my transaction table and one record is created for each service performed.

The second table is my cash receipts table and each time a payment is made on a transaction a record is created in the cash receipts table.

There may be no corresponding records in the cash receipts table, if no payments have been made, there may be just one record in the cash receipts table if one payment in full was made, or therE may be many corresponding records if more than one partial payment was made on a transaction.

I want my result cursor to list each and every transaction whether any payment has been made or not and, to include all payment records for each transaction record.

I've tried doing this in two stages but just can't seem to get the results I know I should be getting.

Here's the first sql on the transaction table:

select hftrns where hftrns.acct_no = ;
"26733" order by hftrns.stateno, hftrns.seqno into cursor transtable

This creates a cursor that has all the transaction information the report requires.

Now the part I can't get is adding in the cash receipts records.

I've tried numerous options incluing these two:

(1)select transtable.*, hfcashr.* from transtable, hfcashr where transtable.acct_no = HFCASHR.ACCT_NO
(results of 252 records, no good

(2)select transtable.*, hfcashr.* from transtable, hfcashr where transtable.acct_no = HFCASHR.ACCT_NO group by transtable.stateno, transtable.seqno
(results of 28 records, no good)

I know that there are 28 records in the transaction table and 12 coesponding records in the cash receipts table of which 8 were 4 partial payments each on two separate transactions and therefore I should be getting a total of 34 records.

Any ideas, help would be appreciated.

Jim Harvey
jharvey@netrax.net