>Dear All,
>
>
>I would like to select the client records like so:
>
>1. Name
>2. Address_1
>3. Address_2
>4. Address_3
>5. Address_4
>6. same_address (if sameaddress -> 1; if not -> 0)
>
>Previously, I used to do so, by 2 SQL statement
>

>SELECT client_no,address1,address2,address3,address4, ;
>ext_recall,0 as sameAddress FROM client_detail ;
>WHERE next_recall = ?cYM ORDER BY 1 into cursor recall1
>
>update recall1 set recall1.sameaddress="1" from recall1 ;
>inner join ;
>(Select address1,address2,address3,address4,Count(*) as nTimes ;
>from recall1 group By address1,address2,address3,address4 ;
>Having Count(*) > 1) b on recall1.address1=b.address1 ;
>AND recall1.address2=b.address2 AND ;
>recall1.address3=b.address3 AND ;
>recall1.address4=b.address4
>

>
>I wonder is there anyway to combine 2 steps into 1
>
>Thanks in advance!

Try (not tested)

SELECT cl.client_no,cl.address1,cl.address2,cl.address3,cl.address4, ;
cl.ext_recall,iif(addr.nTimes >1,1,0) as sameAddress FROM client_detail cl ;
inner join (select client_no, address1, address2, ;
address3, address4, count(*) as nTimes ;
group by 1,2,3,4 from client_detail ;
where next_recall = ?cYM ORDER) addr ;
on cl.client_no = addr.client_no and cl.address1 = addr.address1 ... 
WHERE cl.next_recall = ?cYM ORDER BY 1 into cursor recall1