>>>but im trying to link the otest table an the dup table in such a way that instead of showing one row for Peter an moving to JAmes it will show me the two rows for peter and the three for james so i can do what i need to do with them. Im fully aware i can just go in the Db an manually do it but.dont really wanna do that way. Thanks
>>
>>select T.*, Dup.NumRec from OTest T
>>inner join (select FName, count(*) as NumRec from oTest group by FName having count(*) > 1) Dup
>>on T.Fname = Dup.Fname
>
>also the numrec field is in DUp im getting the error 'Not a character expression'
What is the type of FName field? Is it the field you want to use to identify the duplicates? NumRec is an alias of a computed column in a derived table. This field should not give you any problem.
Can you show what exactly did you write? The query as I posted (even if posted from the top of my head) should have worked just fine.
If it's not broken, fix it until it is.
My Blog