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