Hello,

Below is a select I created to pull data from three tables for use in a report of stallion service fees not paid in full.

If a mare has 4 owners, and the fee is $20,000, each owners gets billed $5,000. If two owners have paid and two have not I want the two records that have not been paid to be included in the report.

1. hftrns.dbf
Contains records of charges billed to a horse.

2. ownpct.dbf
Many horses are owned by more than one person so we use this table to track the number of owners a horse has and bill each owner his share.

3. accounts.dbf
This is our customer table with name of customer and address info,

This line seems "Okay", but was wondering if anyone had an opinion as to how valid it is, or is there a better way to write this select.

"a.comp_no + a.acct_no = b.comp_no + b.acct_no"


select ;
a.comp_no, ;
a.acct_no, ;
a.desc, ;
a.billdate, ;
a.datepaid, ;
a.amount, ;
a.amtpaid, ;
a.refer, ;
a.amount - a.amtpaid as amtdue, ;
b.name, ;
iif(c.fname= " ",trim(c.lname_comp),trim(c.fname) + " " + trim(c.lname_comp)) as acctname ;
from ;
hftrns a, ;
ownpct b, ;
accounts c ;
where ;
a.hcd = "SFTP2" ;
and ;
a.amount - a.amtpaid > 0 ;
and ;
a.comp_no + a.acct_no = b.comp_no + b.acct_no ;
and ;
a.acct_no = c.acct_no ;
order by ;
a.billdate, ;
a.trandate ;
into cursor transunpaid noconsole

Thanks,

Jim Harvey