Thank-you for the replies. Yes A&E number is Accident & Emergency. The data extract is fixed and the index is A&E number.  First visit does have a null previous A&E...

Thank-you Steve . Kind of you to reply I suppose there is not always a quick solution. Kind regards Denise

thanks Phill

Thanks Robbac for your effort. It was very much appreciated. Jeremy's solution worked but I've learnt from both of you

thank-you both very very much

Thanks Jermey I can sort out any syntax I just could find a way to approach the problem