>>>>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.
can you tell me about this inner join thou?
inner join (select FName, count(*) as NumRec from oTest group by FName having count(*) > 1) Dup
It takes all the names from Otest and Dup whose count is more than 1?