Detect Duplication 1

[robdunfey]

I have a table with several fields and a 'primary key'. But the 'primary key' has some duplication. When I use the term primary key I refer to the field I wish to be the primary key, but to do this I need a list of the records that are duplicated. In oracle I would do a select * query minus a select distinct query, but Access doesnt allow set operaters?! Any advice would be much appreciated.

Rob

 

[r937]

select socalledpk, count(*)
from yourtable
group by socalledpk
having count(*) > 1

rudy

http://r937.com/

SQL Consulting

 

[robdunfey]

Rudy thanks, it worked a treat...but...
how does this technique work if the disired primary key is a concatenated key eg.

TB1

Field1 Field2
1 a
1 b
1 c
2 a
2 b
2 c
1 c


The last pair of values in the example are duplicate. How do you detect this?

Any help, much appreciated,

Rob

 

[r937]

select Field1, Field2, count(*)
from yourtable
group by Field1, Field2
having count(*) > 1

rudy

http://r937.com/

SQL Consulting

 

[famehrie]

I am still novice at this whole Access thing but I tried to see what I could find out. I enetred your date into a table as given without a primary key to confuse me and I than used the Access "Find Duplicates Query Wizard". My results show the data combinations that are duplicated and how many times. I also added an additional duplicate record to further test it.
TB1

Field1 Field2
1 a
1 b
1 c
2 a
2 b
2 c
1 c<------Duplicate
2 b<------Duplicate

Query Results
Field 1 Field 2 NumberOfDups
1 c 2
2 b 2

Just for spite here is the SQL for this query
SELECT First(Table1.[Field 1]) AS [Field 1 Field], First(Table1.[Field 2]) AS [Field 2 Field], Count(Table1.[Field 1]) AS NumberOfDups
FROM Table1
GROUP BY Table1.[Field 1], Table1.[Field 2]
HAVING (((Count(Table1.[Field 1]))>1) AND ((Count(Table1.[Field 2]))>1));

I am not sure if this is what you are looking to do still I hope it proves usefull.

Kevin