General information
Title:
Using Select SQL in more than 1 Table
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
Regards
Bob
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only