Finding duplicates in an msaccess table

[Rustywater]

Hi,

I have a table with members first name and last name. I would like to scan the
table for duplicate names. The table has no indexes (if that means anything). I
can easily order the table by first_name, last_name and work through each row,
but that would be intolerably slow (there are 1500 rows on the table).

Does anyone know a way to quickly find duplicates?

Thanks
Tanya

[paross1]

Someting like this might get you going in the right direction.

SELECT t.first_name, t.last_name
FROM table t
WHERE EXISTS(SELECT 1
FROM table t2
WHERE t.first_name = t2.first_name
AND t.last_name = t2.last_name
HAVING COUNT(*) > 1)

[jonwrob]

Phil's query is absolutely correct. I just wanted to add, since you mentioned Access, that Access has a wizard for exactly this kind of query.

JR

[Rustywater]

The records are on a single table. Translating your suggestion into the code
for cf I get this (does it look right). #dupes.recordcount# returns the same
value as the number of rows on the table (1486). I know for a fact that there
are only 6 duplicates on the table. (btw; uid_user is just the users account
number)

I'm not very good with SQL (obviously), but this did look to make sense that I
got the result that I got... It's like saying if 1=1.. So the penny hasn't
dropped yet. What am I doing wrong?



<cfquery name="dupes" Datasource="#Application.datasource#">
SELECT users.first_name, users.last_name, uid_user
FROM users
WHERE EXISTS(SELECT 1
FROM users
WHERE users.first_name = users.first_name
AND users.last_name = users.last_name
HAVING COUNT(*) > 1)
</cfquery>

[paross1]

You have a problem in your correlated subquery because you are not
differentiating the table name between the outer and inner queries.

Phil


<cfquery name="dupes" Datasource="#Application.datasource#">
SELECT u1.first_name, u1.last_name, u1.uid_user
FROM users u1
WHERE EXISTS(SELECT 1
FROM users u2
WHERE u1.first_name = u2.first_name
AND u1.last_name = u2.last_name
HAVING COUNT(*) > 1)
</cfquery>

[Rustywater]

Thanks so much Phil. this works beautifully.

tanya