>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.
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