Partial Match in Find Duplicate Query

D

DebraH

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?
 
J

John Spencer

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 )
 
Top