Need advice on an Update Query (Data cleansing) (1 Viewer)

jonathanchye

Registered User.
Local time
Today, 08:26
Joined
Mar 8, 2011
Messages
448
Basically I have to data cleanse a field of Company names from a table. I currently have 2 tables. Table 1 contains the field I want to data cleanse. Table 2 contains the list of proper Company names.

So for example in Table 1 I have variations like aCme company, ACME LTD, ame limited, ACME, Acmelimited. In Table 2 I have the proper name which is "Acme Limited".

Is there an easy way to create a custom query to search through Table 1 and change it to the proper name in Table 2?

Current I have an Update Query to search for names in Table 1 using "Like *". This works for one company but I have over 100s of company names in the list and it would be too tedious to construct 100 queries to do this task(!).

I tried to fit multiple names in one query but I have an error saying "Duplicate destination field).

Hopefully someone could advise.
 

jonathanchye

Registered User.
Local time
Today, 08:26
Joined
Mar 8, 2011
Messages
448
I created a simple function but it doesn't seem to work right. I guess I must construct something that breaks the name down to individual letters first?

Even with the wildcards is it not returning any results at all...

Below is my code :

Public Function GetCustomerName(CustName As String) As String
Dim temp As String
MsgBox CustName
'temp = Nz(DLookup("Customer_Name", "tblCustomers1", "[Customer_Name] Like " & """" & "*CustName*" & """"), "Error")
temp = Nz(DLookup("Customer_Name", "tblCustomers1", "[Customer_Name] Like '*CustName*'"), "Error")
GetCustomerName = temp
End Function
 

plog

Banishment Pending
Local time
Today, 03:26
Joined
May 11, 2011
Messages
11,611
The simplest way I know is to create a mapping table. Here's how you do it.

1. Create a query that makes a table of unique Company Names in Table 1. Call the resulting table 'MapCompany', which looks like this:

CompanyName
ACme
ACME LTD.
Acme Limited
Acme Lmt.

2. Add a text field to 'MapCompany' called CorrectCompany which will hold the correct name from Table 2 for each company.

3. For every record in MapCompany type in the correct value from Table 2. MapCompany will now look like this:

CompanyName, CorrectCompany
ACme, Acme Limited
ACME LTD., Acme Limited
Acme Limited, Acme Limited
Acme Lmt., Acme Limited

4. Run an update query on Table 1 which links MapCompany to Table 1 by the CompanyName in each table. Update CompanyName in Table 1 to CorrectCompany from MapCompany.

5. Find out how this data's being entered and see if you can change it so that the data gets entered correctly.
 
Last edited:

jonathanchye

Registered User.
Local time
Today, 08:26
Joined
Mar 8, 2011
Messages
448
The simplest way I know is to create a mapping table. Here's how you do it.

1. Create a query that makes a table of unique Company Names in Table 1. Call the resulting table 'MapCompany', which looks like this:

CompanyName
ACme
ACME LTD.
Acme Limited
Acme Lmt.

2. Add a text field to 'MapCompany' called CorrectCompany which will hold the correct name from Table 2 for each company.

3. For every record in MapCompany type in the correct value from Table 2. MapCompany will now look like this:

CompanyName, CorrectCompany
ACme, Acme Limited
ACME LTD., Acme Limited
Acme Limited, Acme Limited
Acme Lmt., Acme Limited

4. Run an update query on Table 1 which links MapCompany to Table 1 by the CompanyName in each table. Update CompanyName in Table 1 to CorrectCompany from MapCompany.

Thanks for the reply! Yes, I see what you are trying to do there. I guess there's no fully automated way of doing this bar manually typing in.

Question about Step 1 though. Won't the query show all names instead of just Acme for example like :

ACme
ACME LTD.
Acme Limited
Acme Lmt.
Ball Corp
BAl cop
Ball Corparation
Country LTD

etc? THis is turn would mean you would need to manually type in the correct company name for each and every record?

Just wondering what is the difference of then manually going into the table and changing it one by one?
 

jonathanchye

Registered User.
Local time
Today, 08:26
Joined
Mar 8, 2011
Messages
448
Alright, found an elegant-ish solution thanks to suggestion by plog.

Here is what I did :

1) Make table query to group all customers in one field and create additional field called "New Name"
2) Create a form that uses this new table as the recordset. Form is set to Continous Form mode.
3) New Name field in the form is set to a combobox. The combobox is set to lookup the table containing proper company names.
4) User updates all the names to proper names.
5) A button on the Form Footer which invokes an Update Query. This query updates the Customer values on the original table with the fields from "New Name" and tables are linked via their "corrupted" names

Job done! Not fully automated but interface is as user friendly as possible so far and results are guaranteed to be correct bar human error! Well human error should be minimal as I've restricted the combobox only to pick values from the proper table and no free type.
 

Users who are viewing this thread

Top Bottom