>Table INMATES - 1 rec per inmate. Primary key is cid
>cid cinmate_number
>1 0012345
>2 0023456
>
>Table ACCT_EVENTS - 1 rec per status changing event. Primary key is cid, foreign key into INMATES is cinmates_id. Status changing events are:
>When inmates acct is created, ACCT_EVENTS rec is created showing status = "OPEN"
>When inmates acct is closed, ACCT_EVENTS rec is created showing status = "CLOSED"
>When inmates act is re-opened, ACCT_EVENTS rec is created showing status = "OPEN", etc.
>An account can be opened, closed and opened again, many times.
>
>cid cinmates_id ttxndatetm txnstatus
>X1 1 04/01/03 12:00:00 AM OPEN
>X2 1 04/05/03 04:00:00 AM CLOSED
>X3 1 04/05/03 06:00:00 AM OPEN
>X4 1 04/07/03 12:00:00 AM CLOSED
>X5 2 04/01/03 08:00:00 AM OPEN
>X6 2 04/03/03 04:00:00 AM CLOSED
>X7 2 04/03/03 05:00:00 AM OPEN
>
>I need to select all recs from INMATES where the latest record from ACCT_EVENTS indicates the current status is "OPEN"
>
>In this example, ONLY INMATES rec where inmate number is 0023456 would be selected. I do need to capture INMATES.cInmate_Number and other extraneous fields and from ACCT_EVENTS, the ttxndatetm. The status will be a view parameter. In other words the same SQL stmt will be used to capture OPEN acct's and CLOSED acct's.
>
>How to accomplish this is beyond my comprehension at this point. SORRY for the formatting. When I write this posting, the columns of data are laid out nicely but it squashes it together when posted.
>Thanks,
>John

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.cid ;
 where ttoc(b.ttxndatetm,1) = ;
   (select max(ttoc(ttxndatetm,1)) from ACCT_EVENTS c ;
     where b.cid = c.cid and c.Status= 'OPEN')

Cetin