>>>Naomi - Your version errors with a message that says queries of this type are not supported.
>>>
>>>After thinking about this some more I realized I don't really need anything from the parent table. Everything I need can be had from the child table. Just 3 fields CUSTID, _YEAR, and AMOUNT, where I select only the most recent year. This should simplify it a bit. It should look something like:
>>>

>>>select custid, max(_year), amount ;
>>>from orders ;
>>>group by custid 
>>>

>>>
>>>This seems to work with the first 2 fields but fails when I add the third even when I tweak the GROUP BY clause.
>>
>>The TOP 1 version doesn't work in VFP, unfortunately.
>>
>>This works fine:
>>
>>

>>* Test data
>>CREATE CURSOR Customers (CustNo int, CustomerName C(100))
>>INSERT INTO Customers VALUES (1,'Cust1')
>>INSERT INTO Customers VALUES (2,'Cust2')
>>
>>CREATE CURSOR Orders (OrderID int , CustNo int, Amount N(10,2), OrderDate date)
>>INSERT INTO Orders VALUES (1, 1, 10, DATE() - 10)
>>INSERT INTO Orders VALUES (2, 1, 100, DATE())
>>INSERT INTO Orders VALUES (3, 2, 100, DATE() - 5)
>>INSERT INTO Orders VALUES (4, 2, 1020, DATE())
>>
>>* Subquery solution
>>select Cust.*, Ord.Amount, Ord.OrderDate from Customers Cust INNER JOIN Orders Ord on Cust.CustNo = Ord.CustNo ;
>>where Ord.OrderDate >= (select MAX(OrderDate) from Orders O where O.CustNo = Cust.CustNo)
>>
>>* Packed value solution
>>select Cust.*, LO.LastDate, cast(RIGHT(LO.DateAmount,100) as N(12,4)) as Amount from Customers Cust ;
>>INNER JOIN (select CustNo, max(OrderDate) as LastDate, ;
>>max(cast(Orderdate as C(10)) + cast(Amount as C(100))) as DateAmount ;
>>from Orders group by CustNo) LO on Cust.CustNo = LO.CustNo
>>
>>* derived tables solution
>>select Cust.*, LO.LastDate, O.Amount from Customers Cust ;
>>INNER JOIN (select CustNo, max(OrderDate) as LastDate ;
>>from Orders O group by CustNo) LO on Cust.CustNo = LO.CustNo ;
>>inner join Orders O on LO.CustNo = O.CustNo and LO.LastDate = O.OrderDate
>>
>>

>
>
>Thanks Naomi. I like the subquery solution. It seems to work fine plus its short and sweet. Any reason to consider either of the other two versions?

Packed value solution usually performs better. If your tables are huge, test performance in each of the cases.