Duplicates in Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to create a query of mainly names and dates. I want it to show
me any duplicate names that may have been entered on the same date. I want
duplicates to be allowed, and I don't want them hidden or anything. Would be
great if it would just highlight any names that are posted twice. If anyone
has any idea how to do this, please let me know. Thanks so much.

-Jenny
 
When you create a new query, one of the options is to create a Find
Duplicates query. It will walk you through this.
 
SELECT TheName, TheDate
FROM TheTable
WHERE TheName & TheDate in
(SELECT TheName & TheDate
FROM TheTable
GROUP BY TheName & TheDate
Having Count(*) > 1)

Or as an lalternative, Create a query like the following and save it.
SELECT TheName, TheDate
FROM TheTable
GROUP BY TheName, TheDate
HAVING Count(*) >1

Now join that query back to the original table

SELECT T.TheName, T.TheDate
FROM TheTable as T INNER JOIN TheQuery as Q
ON T.TheName =Q.TheName AND
T.TheDate = Q.TheDate

This query will not be updatable, the first solution should be updatable.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top