>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,
Hi John,

Try

SELECT cid, cinmates, ttxndatetm, txnstatus ;
  FROM acct_events ;
  WHERE cid + TTOC(ttxndatetm,1) IN ( ;
               SELECT cid + TTOC(MAX(ttxndatetm),1) ;
               FROM acct_events ;
               GROUP BY cid) ;
    AND txnstatus = "OPEN"  ;
  INTO CURSOR crsLastEven NOFILTER

You can use < pre > tag to preserve formatting.