SQL searching similar names mechanism (data mining)

[piotrek.bach]

Hello Everyone,
I need do fix a process of searching duplicates. Generally, I would like to create a mechanism, which will find similar account’s names in whole account’s database. But first I have to know if it is literally possible to do in MS Access and eventually which SQL functions will be needed. Below you can find the list of operations, which I would like to encode and example in Excel file.


1. Import table with special signs (TAB 1)
2. Import table with list of accounts for which I would like to find similar account’s names (ID, Account Name, Country, Account Manager)(TAB 2)
a. From field Account name remove special signs.
3. Import table with whole accounts exist (ID, Group name, Account Name, Branch Name, Sector Name, Country, Account Manager).
a. From fields Group name, Account Name, Branch Name, Sector Name remove special signs.
4. Filter table with whole accounts exist by Country (choose only one Country) and by Account Manager (choose all Account Managers except one).
5. Find in fields Group Name, Account Name, Branch Name, Sector Name (table with whole accounts exist (TAB 3)) string of words from field Account Name (table with list of accounts for which I would like to find similar account’s names (TAB 2))
6. Create new table with found strings of words. This table will has fields from both tables. (TAB 4)


7. Export table with found strings of words to the pivot table in the Excel (TAB 5)


Best regards
Piotr

[orange]

What have you tried?
Since this is your first post on the forum, welcome!
Do you have database experience?
Do you have Access experience?

[piotrek.bach]

Thanks a lot for your quick answer.
I use Access often but base only on queries built by my mates. I know how it works and can edit queries codes if needed, but I have never built something from 0 level. What is more- this case is different because it is data mining and my experience is connected with combining tables and calculations. Firstly I have to know if the mechanism which I described is possible to build.

[Bulzie]

Yes that all is possible. Once you import the 2 tables, one way is to do Union query to get them all in 1 table. Then use Replace function on specific fields to remove the special characters from the fields. Then do a Make table query and use GroupBy/Totals to get rid of dups and can also put in criteria for Country and Manager there. There are a few other ways to do maybe by doing left join and such so yes it should be possible.

[orange]

I agree with Bulzie -it's possible. As one of my colleagues used to say -it's SMOP. A small matter of programming.
You seem to have done considerable analysis on the business side. You might want to watch some database concepts videos/tutorials then work with a subset of your issue with Access to get/develop some familiarity with the product. I find working through a few sample situations then moving to the real problem is a good use of your time, less frustrating and better product.
Good luck.