whats going wrong

a.mlw.walker

Registered User.
Local time
Today, 13:13
Joined
Jun 20, 2007
Messages
36
I am trying to do a partial duplication query. this is the code from the SQL view in access
Code:
SELECT filtered1.Company_Name

FROM filtered1

WHERE (((filtered1.Company_Name) In (SELECT [Company_Name] FROM [filtered1] As Tmp GROUP BY [Company_Name], Left([Company_Name],7)  

HAVING Count(*)>1 And  Left([Company_Name],7) = Left([filtered1].[Company_Name],7))))

ORDER BY filtered1.Company_Name;

the table filtered1 has 1300 records, under the field Company_Name.
the 7's apparently should get it to compare the first 7 letters, and if the same show me them.

A whats going wrong, its not showing me any, asthough there are none, (which i know there are) and
B. How would i get it to compare the first 7 letters across tables, with fields of the same name?

thanks
alex
 
Last edited:
Your query is coounting the company names that have more than one entry. What you need to do is count the company names that match your where clause. Probably creating a calculated field with just the first 7 letters and counting that will do the trick, say:
Code:
SELECT Left([Company_Name],7) AS ShortName

FROM filtered1

WHERE (((filtered1.Company_Name) In (SELECT [Company_Name] FROM [filtered1] As Tmp GROUP BY [Company_Name], Left([Company_Name],7)  

HAVING Count(*)>1 And  Left([Company_Name],7) = Left([filtered1].[Company_Name],7))))

ORDER BY Left([Company_Name],7);
Not tested, mind!
 
Hi Thanks for your reply, it still doesnt show me anything. I'm slightly confused as to whats its doing.

Alex
 
Let's start again.

First you need to strip out the first 7 letters of your company name. Then you need to count how many instances of those 7 letters you have. The mistake you have made is to include the full company name in your query.
 

Users who are viewing this thread

Back
Top Bottom