467,920 Members | 1,250 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,920 developers. It's quick & easy.

Finding similar entries&duplicates

Hi there!

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Investor.Company, Investor.ID, Investor.Country
  2. FROM Investor
  3. WHERE ((( Investor.Company) In (
  4.   SELECT [Company]
  5.   FROM [Investor] As Tmp GROUP BY Left([Company],3)
  6.   HAVING  Count(*)>1
  7.      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!
Feb 5 '07 #1
  • viewed: 1587
Share:
6 Replies
NeoPa
Expert Mod 16PB
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)
Feb 5 '07 #2
NeoPa
Expert Mod 16PB
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
  1. SELECT [Company], [ID], [Country]
  2. FROM Investor
  3. WHERE Left([Company],3) In((
  4.   SELECT DISTINCT Left([Company],3)
  5.   FROM [Investor]
  6.   WHERE [Company] Like 'Some Value*'))
Feb 5 '07 #3
NeoPa
Expert Mod 16PB
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
  1. SELECT [Company], [ID], [Country]
  2. FROM Investor
  3. WHERE Left([Company],3) In((
  4.   SELECT Left([Company],3)
  5.   FROM [Investor]
  6.   GROUP BY Left([Company],3)
  7.   HAVING Count(*)>1))
Feb 5 '07 #4
Thank you so much for your help! I really appreciate it! I will try to see what I get.
Cheers :)
Feb 5 '07 #5
It works perfect! Thanks a million! I O U :D
Feb 5 '07 #6
NeoPa
Expert Mod 16PB
I'm very pleased that's done it for you.
Thanks for posting saying it's fixed - that's always good to see.
Feb 5 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.