sorry, yep had already tested it.

thank you...left join it is then.

Thanks Laurie, I'd literally just arrived at that myself too!

Is it possible to mark questions as answered on this forum?

thanks for that...it's good learning new stuff!

Is there no 'mark as answer' option for form posts?

No I'm using 2008.

Anyway, that works guys, so thank you very much!

Just one more question ...what is a 'cte'??

Hi there,

I'm getting incorrect syntax on line 3 (the first insert line)

cheers

IT WORKS!

Thanks very much for your help Tom...pour yourself a Sangrea and kick back for the rest of the day 😉

Hi Tom,

sorry didn't see your last thread before I posted mine..hopefully the new thread will push us closer to Moscow (curious analogy no.162)

Hi,

I'm starting a new thread for this problem with the data for you to use..in case any other chaps want to have a look at it as well...I'm going to...

is it something to do with the left join part of your sql Tom. It doesn't seem to create or populate the email column? ....the phnytpeid is 9 for an...

Hi Tom,

yes there is an entry with the email and type 9

5 Graham Atkinson patientne@test.com 9

cheers tom. I've tried the sql and the email field isn't getting populated though. Both of these have 2 related records each in the Phone table for type 5 (mobile)...

oh fun and games with the table formatting. It appears I can't delete the above post and when I try and edit it there's isn't a repost option? I'm sure...

Hi Tom, thanks for your help...it's almost what I'm hoping for.

When I run my original sql I get ...

idfname lnamephnNum ...

Thanks that works...I'm giving you a virtual pat on the back!