CODEWITHSUNDEEP
sqldatabasems-access
economistdolly
economistdolly

Reputation: 395

Show unique (differentiating) data in a duplicate search in Access?

I'm trying to examine the unique fields of duplicate entries (the entries are duplicate in, say, 2 fields but unique in the other 4) - I have an access query to find duplicates but I can't for the life of me figure out how to show only records that are duplicated in the given fields while still showing me the unique information about these records.

Ideas?

Update: here's my query. It's getting me a count of the repeated data, I want somehow to add in the unique data, fields like [AllEmail Name].[phone] amongst others.

SELECT 
      First([AllEmail Name].[email]) AS [email Field],
      First([AllEmail Name].[firstname]) AS [firstname Field],
      First([AllEmail Name].[lastname]) AS [lastname Field],
      Count([AllEmail Name].[email]) AS NumberOfDups,
      Count([AllEmail Name].allEmail.id) AS CountOfallEmail_id
FROM [AllEmail Name]
GROUP BY [AllEmail Name].[email], [AllEmail Name].[firstname], [AllEmail Name].[lastname]
HAVING (((Count([AllEmail Name].[email]))>1));

And another update, sample data:

Name   Email   Phone
John   [email protected]  2345
John   [email protected]  6789
John   [email protected] 2134
John   [email protected] 4444

would either return something like 

Name   Email  Dupes  Phone
John   [email protected] 2     2345
                     6789

or

John  [email protected] 2     2314
John  [email protected] 2     4444

Upvotes: 0

Views: 140

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78591

Try to group it:

select t1.col1, t1.col2,
       t1.col3, t1.col4,
       count(*)                    -- number of identical rows based on cols
from   tbl t1
inner join tbl t2 on t2.col1 = t1.col1 -- same col1
                 and t2.col2 = t1.col2 -- same col2
                 and t2.id <> t1.id    -- but not the same pkey
group by t1.col1, t1.col2,
         t1.col3, t1.col4          -- merge when col3 and col4 are the same

Upvotes: 0

Related Questions