Finding similar entries&duplicates

[Anca Floria]

Hi there!

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

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.

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 :

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.