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

 

[Clifford Bass]

Hi,

Try:

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

To prevent this problem in the future, I would suggest getting rid of
the RecNo in the Components table and making the combination of the FileRec
and RefDes the primary key.

Clifford Bass