Finding similar entries&duplicates

Anca Floria

Hi there!

I am trying to find out what is wrong with the following code:

Expand|Select|Wrap|Line Numbers

 SELECT Investor.Company, Investor.ID, Investor.Country

FROM Investor

WHERE ((( Investor.Company) In (

  SELECT [Company]

  FROM [Investor] As Tmp GROUP BY Left([Company],3)

  HAVING  Count(*)>1

     And Left([Company],3) =Left([Investor].[Company],3) )));

Its purpose is to find similar entries (that have 3 or more characters common in the begining of their name). egg: if I have ABC234 and ABC345 I want to be given both entries, not only if I have perfect matches, meaning having entered twice ABC234.

The message I keep getting is:

You tried to execute a query that does not include the specified expression "Company" as part of an aggregate function.

Does anyone know what I am doing wrong and what would be the solution?
Thank you!

NeoPa

Firstly, what is wrong.
In your last line of the GROUPed subquery you refer to the [Company] field. Within GROUPing, you can only refer to fields directly if they are GROUPed. Other fields can only be used if within some sort of aggregate function (First; Max; Sum; Count; etc)

NeoPa

You also don't specify what three letter code you want to select on.
Your code seems to want to make it select whatever the current record is (therefore no selection at all).
Something similar to the code below should be what you're looking for but you need to decide exactly what you want before you can find out how best to get it.

Expand|Select|Wrap|Line Numbers

 SELECT [Company], [ID], [Country]

FROM Investor

WHERE Left([Company],3) In((

  SELECT DISTINCT Left([Company],3)

  FROM [Investor]

  WHERE [Company] Like 'Some Value*'))

NeoPa

I reread your post and realised you'd explained it but I'd missed it. What you ask does make sense.
Try this instead :

Expand|Select|Wrap|Line Numbers

 SELECT [Company], [ID], [Country]

FROM Investor

WHERE Left([Company],3) In((

  SELECT Left([Company],3)

  FROM [Investor]

  GROUP BY Left([Company],3)

  HAVING Count(*)>1))

Anca Floria

Thank you so much for your help! I really appreciate it! I will try to see what I get.
Cheers :)

Anca Floria

It works perfect! Thanks a million! I O U :D

NeoPa

I'm very pleased that's done it for you.
Thanks for posting saying it's fixed - that's always good to see.

Finding similar entries&duplicates

Post your reply

Similar topics