Duplicates in Query

[NotVeryBright]

Hello,
The query below

SELECT DISTINCT [MyTable1].[ID] , [MyTable1].[Forename],
[MyTable1].[Surname],MyTable.ID,MyTable.forename,MyTable.surname
FROM MyTable INNER JOIN MyTable AS MyTable1 ON
INT([MyTable].[ID])=INT([MyTable1].[ID])
WHERE ([MyTable].[surname])=("goodier") AND MyTable1.surname="vickers"

Produces these results

00282.05 Mrs Margaret VICKERS 00282.01 John GOODIER
00282.05 Mrs Margaret VICKERS 00282.02 Leonard GOODIER
00282.05 Mrs Margaret VICKERS 00282.03 Fleety GOODIER
00282.05 Mrs Margaret VICKERS 00282.04 Mary GOODIER
00282.05 Mrs Margaret VICKERS 00282.07 Leonard GOODIER
00282.05 Mrs Margaret VICKERS 00282.08 Beatrice GOODIER
00282.06 Mr Simeon VICKERS 00282.01 John GOODIER
00282.06 Mr Simeon VICKERS 00282.02 Leonard GOODIER
00282.06 Mr Simeon VICKERS 00282.03 Fleety GOODIER
00282.06 Mr Simeon VICKERS 00282.04 Mary GOODIER
00282.06 Mr Simeon VICKERS 00282.07 Leonard GOODIER
00282.06 Mr Simeon VICKERS 00282.08 Beatrice GOODIER

Any one give me a hint on how to remove the duplicates?

Thanks and best regards

 

[Crystal]

Hello,

There aren't any duplicates if you compare EACH field -- if
you only want the names, then don't output the IDs -- then
DISTINCT will not repeat names

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[NotVeryBright]

Hello Crystal, Thanks very much for your reply
I will try it out.
Best Regards

 

[NotVeryBright]

Hello Crystal,
I'm not quite sure what you mean.
Is EACH a keyword and where do I insert it?
Is compare a keyword?

I do need the integer part of the ID for both Vickers and Goodier because
whilst the integer part of the ID is the same in the example
there may be another instance where the query reveals two or three different
Integer IDs
Best Regards

 

[Crystal]

I emphasized "each" because I assumed you did not notice
that your IDs are different. No, "each" is not being used
as a keyword. DISTINCT will not return rows with duplicate
values when you compare each field.

"Any one give me a hint on how to remove the duplicates?"

--> There were none.


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com