Partial Match in Find Duplicate Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DebraH - 10 Nov 2005 00:37 GMT

I need to find suspected duplicates in a query. I started with the find
duplicates query and made the following modifications:

In (SELECT (Left$([Application], 20)) FROM [Quest_Application_Count] As
Tmp GROUP BY [Application] HAVING Count(*)>1 )

I get zero results. I know there are duplicates:

Application
Abacast Version 1.22
Abacast Version 1.25f1
Abacast Version 1.31
Abacast Version 1.32
2000 TurboTax Deluxe
2000 TurboTax for Windows
2001 TurboTax Deluxe

How do I find the suspected duplicates?

Signature

Debra H

Reply to this Message

John Spencer - 10 Nov 2005 02:33 GMT

You need to group by the same 20 characters and you will need to match that in
the where clause

WHERE Left([Application], 20)
In (SELECT (Left$([Application], 20)) FROM [Quest_Application_Count] As
Tmp GROUP BY Left([Application],20) HAVING Count(*)>1 )

Although 20 characters is too many to give you any match on the data you supplied.

You might try matching on the first word (or the first two words).

WHERE (Left([Application], INSTR(1,[Application]," "))
In (SELECT (Left([Application], INSTR(1,[Application]," ")) FROM
[Quest_Application_Count] As
Tmp GROUP BY (Left([Application], INSTR(1,[Application]," ")) HAVING Count(*)>1 )

> I need to find suspected duplicates in a query. I started with the find
> duplicates query and made the following modifications:
[quoted text clipped - 16 lines]
> --
> Debra H

Reply to this Message