>Cetin,
>I tried it. I did not get ANY recs so I changed the INNER JOIN to be ON a.cid = b.cinmates_id because I believe that is what you meant. Now, I get ALL the OPEN records from ACCT_EVENTS. Again, (perhaps I am not stating it correctly), I need all recs from INMATES where LASTEST rec in ACCT_EVENTS status = OPEN.
>

>select a.cid, a.cinmate_number, ;
> b.cinmates_id, b.ttxndatetm, b.txnstatus ;
> from INMATES a ;
>   inner join ACCT_EVENTS b ;
>        on a.cid = b.cinmates_id ;
> where ttoc(b.ttxndatetm,1) = ;
>   (select max(ttoc(ttxndatetm,1)) from ACCT_EVENTS c ;
>     where b.cid = c.cid and c.txnStatus= 'OPEN')
>

>Thanks,
>John

Oh I missed that :) You then need to change cid in 2nd where too :

select a.cid, a.cinmate_number, ;
 b.cinmates_id, b.ttxndatetm, b.txnstatus ;
 from INMATES a ;
   inner join ACCT_EVENTS b ;
        on a.cid = b.cinmates_id ;
 where ttoc(b.ttxndatetm,1) = ;
   (select max(ttoc(ttxndatetm,1)) from ACCT_EVENTS c ;
     where b.cinmates_id = c.cinmates_id and c.txnStatus= 'OPEN')

If you mean to return only one record remove 'b.cinmates_id = c.cinmates_id and'
Cetin