>>Cetin and Chad,
>>I get the error:
>>cStatus is not unique and must be qualified in both of your solutions.
>>
>>Perhaps this is because I am using VFP7.0. I am NOT using SQL server. Would that make a difference?
>>Thanks,
>>John
>
>John,
>
>This is what I just test in both VFP7 & VFP8:
>
>

>SELECT 0
>CREATE CURSOR inmates (cid i, cinmate_number c(7))
>INSERT INTO inmates VALUES (1, '0012345')
>INSERT INTO inmates VALUES (2, '0023456')
>SELECT 0
>CREATE CURSOR acct_events (cid c(2), cinmates_id i, ttxndatetm t, txnstatus c(6))
>INSERT INTO acct_events VALUES ('X1', 1, {^2003-04-01,12:00:00A}, 'OPEN')
>INSERT INTO acct_events VALUES ('X2', 1, {^2003-04-05,04:00:00A}, 'CLOSED')
>INSERT INTO acct_events VALUES ('X3', 1, {^2003-04-05,06:00:00A}, 'OPEN')
>INSERT INTO acct_events VALUES ('X4', 1, {^2003-04-07,12:00:00A}, 'CLOSED')
>INSERT INTO acct_events VALUES ('X5', 2, {^2003-04-01,08:00:00A}, 'OPEN')
>INSERT INTO acct_events VALUES ('X6', 2, {^2003-04-03,04:00:00A}, 'CLOSED')
>INSERT INTO acct_events VALUES ('X7', 2, {^2003-04-03,05:00:00A}, 'OPEN')
>
>SELECT cInmate_Number, tTxnDateTm, TxnStatus ;
>  FROM INMATES im ;
>    LEFT JOIN ACCT_EVENTS ae ;
>      ON im.cID = ae.cInmates_ID ;
>  WHERE tTxnDateTm IN (SELECT MAX(tTxnDateTm) FROM ACCT_EVENTS GROUP BY cInmates_ID) ;
>    AND TxnStatus = 'CLOSED'
>
>SELECT cInmate_Number, tTxnDateTm, TxnStatus ;
>  FROM INMATES im ;
>    LEFT JOIN ACCT_EVENTS ae ;
>      ON im.cID = ae.cInmates_ID ;
>  WHERE tTxnDateTm IN (SELECT MAX(tTxnDateTm) FROM ACCT_EVENTS GROUP BY cInmates_ID) ;
>    AND TxnStatus = 'OPEN'
>

>This works for me in both versions of VFP that I tested. How 'bout you give some more details about the table structures and need result fields, since this part works with no errors.
>
>Thanks

Chad,
I'm sorry to say that this works because sample data is set to work. Try with this :

SELECT 0
CREATE CURSOR inmates (cid i, cinmate_number c(7))
INSERT INTO inmates VALUES (1, '0012345')
INSERT INTO inmates VALUES (2, '0023456')
INSERT INTO inmates VALUES (3, '0023458')
SELECT 0
CREATE CURSOR acct_events (cid c(2), cinmates_id i, ttxndatetm t, txnstatus c(6))
INSERT INTO acct_events VALUES ('X1', 1, {^2003-04-01,12:00:00A}, 'OPEN')
INSERT INTO acct_events VALUES ('X2', 1, {^2003-04-05,04:00:00A}, 'CLOSED')
INSERT INTO acct_events VALUES ('X3', 1, {^2003-04-05,06:00:00A}, 'OPEN')
INSERT INTO acct_events VALUES ('X4', 1, {^2003-04-07,12:00:00A}, 'CLOSED')
INSERT INTO acct_events VALUES ('X5', 2, {^2003-04-01,08:00:00A}, 'OPEN')
INSERT INTO acct_events VALUES ('X6', 2, {^2003-04-05,04:00:00A}, 'CLOSED')
INSERT INTO acct_events VALUES ('X7', 2, {^2003-04-05,06:00:00A}, 'CLOSED')
INSERT INTO acct_events VALUES ('X8', 3, {^2003-04-05,06:00:00A}, 'OPEN')

Cetin