how to avoid duplicities during inserting new entry

[TriFith]

Hello everyone,
I am facing another problem in MS Access...

Part on my db is managing Customers (Name and other info...). I have found there is pretty many cases of duplicies in my tblCustomers.Name field.
I have set a rule with DLookUp function before inserting new entry to db, but users are much more "clever" than I expected.
My duplicities are not exactly duplicities, but more preciselly it´s very similar names. This is ruining my reporting system, because I am using CustomerID as a criteria for other reports and queries.

For example:
"Aura Engineering" vs "Aura Eng."
"Antolin Liban" vs "Gruppo Antolin Liban"


"Blika" vs "Blika s.r.o."

Every user is inserting customer name with different "style" or "format".
So my question is very simple... How can I avoid this kind of duplicities?
I was thinking about some way how to compare string from form with values stored in tblCustomers.Name and get a result of % match (for example).

Does anyone meet something like this?

Thanks
TriFith

[vicsaccess]

personally i use combo buttons for those reasons. if the customer is not in the table then i have a command button that allows the user to enter a new customer.

[orange]

TriFith,
A lot of what you are asking and attempting to do depends on what sort of data management discipline you have. Many organizations identify Customers uniquely with a specific identifier/number. Associated with that unique identifier is the Company legal name, and other attributes such as Physical address, ShipTo address, Mailing address, email address, primary phone number etc. Other related tables may be Alternate address, Alias Names etc.
Other companies, or divisions of companies (especially marketing companies), tend to
have multiple names and addresses for "customers" ( or don't really care at detail level) and will send materials to any and all customers and addresses they have.

If your organization allows users to enter any combination of characters as Company name, I can see why you have, and will continue to have issues. The issues you are experiencing are rooted in your approach to data management, not the computer system, or user per se.

If you consider your Customers as essential to your business, then applying more discipline (standards, procedures..) to those Customer names and addresses is important.

To vet/validate a company you can use some combination of fields and algorithms to reduce duplication. This could include alias name, physical, mailing and/or alternate address, phone number. Some calculations based on these fields could improve the ability to identify a company.

If you have a unique identifier for each Company, you can relate that identifier to a number of keywords in the name (legal name, operating name, and any alias name) and also associate an address (physical, mailing, shipto..) to find unique company identifiers. Al of this to reduce duplication --duplication/redundant names and duplication of effort by people and computers to manage customer info.

You really have to identify the business importance of Customers, and the associated costs to your company for not managing that information efficiently.

Good luck.

[TriFith]

Orange,
I agree with you, there should be something unique for the customer. It´s a bit complicated to explain situation for me. We have two separated system (ERP system based on oracle database used for quotations, invoices and other stuff and simple database for managing service issues, preventive maintenances and other cases)
Unfortunatelly I can´t connect both dbs together (its because of ERP system, there is a restriction to connect to db with 3rd sources aplications etc).
That´s why I created separated db for other sort of people with information what they need to see/use.

This is situation what I am facing now, so I would like to get some unique identifier, but I have no way to reach this.
So my question still remains. How to compare strings and get some usable result.

My idea is to compare new customer name with values in tblCustomers.Name as two strings (or parts of strings) and if they will be for example 75% match, there will be a message thrown with information about found results.
User will decide if the customer is unigue, or he is trying to add duplicite customer.

Does anybody have a tip(s) to implement this?

[vicsaccess]

sounds like a search by keyword textbox. how about something like this
https://www.youtube.com/watch?v=LxFpQEUxD2Q

[orange]

TriFith,
Interesting situation. I went through something similar (sort of). We were reviewing company information processed within a government organization. This included finance, administration, materiel management and approximately 10 divisions responsible for "industry sector" (IT, Railway, Resources, ...) information. The approach was a streamlining of information management on a corporate basis. In this process we found over 50 recognized databases in the sector areas, most in various disoriented states (absolute silos with poor design and management). Information ranged from somewhat current to old, out-of-date, company hasn't existed for years, the contacts have been dead for X years, and was in spreadsheet, word processing, on paper/books format. We created an online (Oracle) web-based, corporate database with appropriate management discipline and authority. Subsets of data representing industry sectors, state/province, company size, products/services.... could be assembled as needed. Data could be downloaded for analysis purposes. We used spreadsheet and database tools for these read only extracts. User groups were established and encouraged to participate in refining the management and technical processes. All that to say it can be done if there is a commitment.

In your situation, you really can not have everyone adding records without some vetting/validation mechanism. You can establish some "standards" for how to enter a name or part of a name, you could check for an address and/or phone or email address to help find an existing company before inserting a new company. Since the issue you are experiencing is affecting your activity and increasing the inefficiency of your efforts, you have to identify the root problem and the related corrective actions.

I have 2 articles in the forums regarding searches.

1) https://www.accessforums.net/showthread.php?t=43055
2) https://www.accessforums.net/showthread.php?t=49804

These may be useful for ideas or approaches. There are other posts, articles and examples (try google).

In general, it seems you should have some method of "loading/reloading" company names to your database from the ERP system. One system has to be seen as the "authority". Its data is "correct". You work from the authoritative system. You compare new, incoming data with the authoritative (already vetted/validated) information. In our case, data was extracted from the authoritative corporate database. That was the first step when giving people access to local copies --it always came from the authoritative system.

You could create tables of company names (even break them into keywords), then compare new names with these keywords. If you get a match on several keywords (and you could include address/city/state and phone and email), you get a refined set of companies from which to identify an existing company or you get more evidence that this is a new company to be added.

However, your ERP system must identify a company by some primary Key/unique identifier. And that is what is necessary to show the association of keywords/fragments to a company.

eg. If your ERP system has International Consolidated Widgets as company 2345678, then
you would associate 2345678 with
International
Consolidated
Widgets
and if this company was located at 2300 Dixie Highway, Sometown, Florida, 33508

then that info would also be associated with 2345678.

The job then is to compare some incoming names/fragments with the "authority" and using some algorithm (you said 75%) to get the "highest/best/acceptable" match. If it meets your criteria, it's probably(acceptably) the same company, so use the 2345678 with that incoming company. If you do not get a 75% or acceptable match, then you have a "probable new company" of interest to your organization. This could be an addition to your system, AND it could be a transaction for insertion into the ERP system. What happens next depends on the vetting/validation process that the ERP system uses. Does it have a new company vetting process? If so, pass the transaction to that system. If not, then add the company to your system, and record it to show it is with your system only. If you get to the point of loading your system from the ERP regularly, then you have to backup and then reinsert your "in your system only" records into your refreshed data.

Hope this helps a little. Readers really need to know more about the procedures you (and/or the users) use to enter data. You really have to review those processes--that you already see as an issue -- and identify steps to make it more consistent and efficient.

I'm sure your boss and his boss didn't start this effort by saying --"..and please make this inefficient, wasteful and expensive....". My guess is they are not aware of the consequences in business terms for this current set up. So identify some steps to a more ideal method and then seek some management support.

Good luck.