>>>Hi All -
>>>
>>>I am experiencing a bit of brain deadness this morning. I set out to write what I thought would be a rather simple SQL select statement but I just can't seem to get my head around it. Thus my plea for help. There are 2 tables CUSTOMERS and ORDERS. Common field is custID. I want to select all customers and the _date and amount of their most recent order (one record per customer). Can someone help me figure out how to do this?
>>>
>>>Thanks
>>
>>What DB you use?
>>VFP - what version?
>>SQL Server - what version?
>>Other?
>
>
>Hi Borislav - It's VFP 9.

SELECT Customers.*,;
       Tbl1.Date,;
       Tbl1.Amount;
FROM Customers;
INNER JOIN (SELECT Orders.CustId,;
                   Orders.Date,;
                   SUM(Orders.Amount) AS Amount;
            FROM Orders;
            INNER JOIN (SELECT CustId, MAX(Date) AS Date;
                               FROM Orders;
                        GROUP BY CustId) TblMaxdate;
            ON Orders.CustId = TblMaxdate.CustId AND;
               Orders.Date   = TblMaxdate.Date;
            GROUP BY Orders.CustId, Orders.Date) Tbl1
    ON Customers.CustId = Tbl1.CustId

Not tested!