Duplicates

[the_grove_man]

I have a Table called 'Files' which is joined to 'Components' [One file can
have many components]. I'll condense it for brevity of my question.

Files Table:
1.) RecNo [Primary Key]
2.) FileName
3.) ......n/a

Components:
1.) RecNo [Primary Key]
2.) FileRec [foriegn key to RecNo in Files Table]
3.) RefDes

I am trying to write a query that will find duplicate of refdes within a
filename. Some of our users inadvertanly adding stuff without asking me. Joy
joy.

So far I have:

SELECT RefDes, COUNT(RefDes) AS NumOccurrences
FROM Components
GROUP BY RefDes
HAVING (COUNT(RefDes) > 1 )


This shows the duplicates globally, which is a start. But I want to see the
duplicates within a FileName

What I wanted to show was the dupes in this fashion

FileName RefDes
12.edit A1
12.edit A1
19.edit C8
19.edit C8

 

[the_grove_man]

Sorry for the duplicate post, I usually use MSDN forums, wasn't used to this.
A moderstaor can delete the duplicate, sorry!

 

[Ken Sheridan]

Join the tables and group by filename then refdes:

SELECT FileName, RefDes,
COUNT(*) AS NumOccurrences
FROM Files INNER JOIN Components
ON Components.FileRec = Files.RecNo
GROUP BY FileName, RefDes
HAVING (COUNT(*) > 1 );

Ken Sheridan
Stafford, England

 

[the_grove_man]

Aweseom, thank you!!!