Suppose you have one table where you need to show all of the rows that have duplicate (more than one) values in a particular column. For example you may have a column called UniqueNumber in your table. You want to know if any number in that column exists more than once. You can use a query to do that. You can optionally show any number of other columns in that query as well.
Have a look at our History table. Some of the data may have changed form other blog posts using this data. Are there any duplicates in the UniqueNumber column? It’s easy to spot those when the file is small like this. Both Frank and Bob have the same UniqueNumber (third column) as 23456. Following the History table listing is the query that you can use to find duplicates in a single column in a single table.
John 12 12987 Frank 121 23456 Sally 647 11211 Bob 24 23456 Sally 7876 22222
SELECT History.[FirstName], History.[Amount], History.[UniqueNumber] FROM History WHERE (((History.[UniqueNumber]) In (SELECT [UniqueNumber] FROM [History] As Tmp GROUP BY [UniqueNumber] HAVING Count(*)>1 ))) ORDER BY History.[UniqueNumber];