>I am having a problem with a join that I am trying to do. I have three databases and am creating a delinquent accounts report. I need to pull the name and phone number from one database, billing info from another, and payment info from a third. I then need to compare the payment info to see if the bill has been paid. If it hasn't and is over 15 days old I want to categorize it into 15,30,45 ... days old. The code I am using (which only gives one record) follows:
>
>select a.name as rcust, b.cusid as rscat, a.phn as rphone, b.date as rdtbill, b.invnbr as rinv, b.amtblld as ramt, b.duedt as rdue, b.carrid as rcarr, b.tlfee as rtl, b.frtchgs as rfrt, c.invnbr,;
>iif(b.amtblld-sum(c.amount)=.null.,0,b.amtblld-sum(c.amount)) as amtdue, b.paid as pd from nameaddr a, billing b inner join payment c on c.invnbr = b.invnbr;
>where ! b.paid into cursor hold having rdtbill < date() - 16 order by b.date
>
>Any suggestions on this?

Sorry, but I don't see where/how you join NAMEADDR and BILLING. Anyway, some suggestions:

1. I would leave the JOIN with NAMEADDR for a second SELECT; try it, anyway, but it usually works faster that way.

2. You could do OUTER JOIN the tables BILLING and PAYMENT so for every BILLING there is a (maybe NULL) PAYMENT record.

3. To categorize "lateness" you could have a field

IIF(DUEDATE>DATE()-15,1, IIF(DUEDATE>DATE()-15,2, IIF(DUEDATE>DATE()-30,3, IIF(DUEDATE>DATE()-45,4,5)))))

.

HTH!