>I have 2 tables, CLIENT and ADDRESS, both with a unique MNO field which is the client ID number. The ADDRESS table can have any number of records for each client, with a field ADS as the date of address change.
>
>Using Select SQL, I need to extract a list of client names from the CLIENT table and their most recent address record from the ADDRESS table.
>
>I have tried using the MAX(ADS) to extract the most recent record from the ADDRESS table, but it only extracts the actual most recent ADS field itself....the actual address data appears to come from any record in the ADDRESS table.
>
>The code used is:
>
>select max(address.ads), address.ad1, trim(client.sn)+', '+trim(client.fn) as surname , client.mno ;
> from tcdata!address ;
> inner join client ;
> on address.mno=client.mno ;
> where address.can=0 ;
> group by address.mno ;
> order by surname ;
> into cursor boe
>
>Any suggestions would be gratefully received

SELECT MAX(Address.ads) as ads, Address.mno,  ;
       STRTRAN(client.sn - ',,' - client.fn,',',,', ') AS surname, ;
  FROM tcdata!Address ;
 INNER JOIN client ON address.mno = client.mno;
  INTO CURSOR Temp ;
 GROUP BY Address.mno ;
 WHERE Address.can = 0 ;


SELECT Temp.*, ad1;
  FROM Temp ;
 INNER JOIN address ON temp.mno = address.mno AND ;
                       temp.ads = address.ads ;
 ORDER BY surname ;
  INTO CURSOR boe

>
>Regards
>
>Bob