>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
First of all, I would suggest to use the table names, and not aliases "a", "b" and "c", to make the statement more readable.
Also, when posting messages on the UT, if you the UT to respect your spaces, use PRE tags (put < pre > at the beginning of your code, and < /pre > at the end - omit the spaces).
HTH,
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)