Query to find similar values in same column

[jrheeder]

Hi guys,

I am currently in a very sticky place, I want to go through 50,000 records and find records in the same column that have a similar value so that I can change them to one universal value, now I can do this manually but it will take we a LONG while to do so is there a quicker way for me to achieve this. Below is an example of what I would like to achieve:

# Product Name
1 Apples
2 Bananas
3 The Apples
4 Oranges
5 The Apple
6 One Banana

Now find simiral values and display them to me so I can pick an approproate similar name.

1 Apples,
3 The Apples,
5 The Apple

4 Bananas,
6 One Banana

If you need more clarification, please let me know.

Hope to hear soon,
J

 

[John Big Booty]

HI and welcome to the forum.

To take your example of Apples and it's various derivations you could create a query and use Like "*Apple*" in the in the criteria, this would select any record that contained the string Apple whether it be The Apple, The apples, Apple, Apples, One Apple or Many Apples. You could then turn your select query into and Update query that would update any of those values into Apples or any other string you chose.

 

[jrheeder]

HI and welcome to the forum.

To take your example of Apples and it's various derivations you could create a query and use Like "*Apple*" in the in the criteria, this would select any record that contained the string Apple whether it be The Apple, The apples, Apple, Apples, One Apple or Many Apples. You could then turn your select query into and Update query that would update any of those values into Apples or any other string you chose.

John,

I fully understand that + appreciate the relpy, what I am looking for though is to go through each record and find if there is another record with similar namings in the data set. Any way of achieving this as I dont want to sit and manully go through each name manually

 

[John Big Booty]

........

If you need more clarification, please let me know.

Hope to hear soon,
J

In that case please explain.

 

[jrheeder]

In that case please explain.

Sure, in simple terms I want to find any reocrds that have similar names and list them. Any conversly exclude any names and have no similar namings.

Example:

1. Apple
2. Orange
3. Banana
4. The Apple
5. The Banana
6. Orange
7. Apples

When I run the requested query (or set of queries) I want it to display

1. Apple
4. The Apple
7. Apples
3. Banana
5. The Banana

Apologies for the confusion caused

 

[John Big Booty]

OK I can see how;

1. Apple
4. The Apple
7. Apples

are all similar but;

3. Banana
5. The Banana

 

[John Big Booty]

How are Apples and Bananas similar??

 

[jrheeder]

How are Apples and Bananas similar??

They are not BUT they have a similar record name within the data set thus listed this is why I am finding this slightly more challenging as there are more than 1 record group with similar namings

 

[John Big Booty]

Well the only thing I can think of is using;

Like "*Apple* Or Like "*Banana*"

In the criteria of a query. Given that there is no apparent relationship between the two it is either that our you go through it manually with the info you've provided thus far I can see no other alternative.

 

[vbaInet]

Have you considered the built-in Find/Replace dialog?

I think what you're trying to do is group the similar values (then sort) in which case you could try using John's suggestion plus create an alias field that would allow you sort by (or group by) those set of possible matches followed by a sort of the field being searched, so something like:

GROUP BY IIF([FieldName] LIKE '*Apple*', 1, 2)
ORDER BY [FieldName];

Or you simply sort by both in this sequence:

ORDER BY IIF([FieldName] LIKE '*Apple*', 1, 2), [FieldName];

Nest some more IIFs depending on how many search criteria you want to include.