spelling list-

[subs]

HOLLAND CO
HOLLAND COLORS
HOLLAND COLOUR
HOLLAND COLURS
HOLLANDCOLORSI

HEUCOTECH
HEUCOTECH
HEUCOTECH LIMI
HEUCOTECH LTD
HEUCOTECH LTD.
HEUCOTECHLTD


PERMAQIUM
PERMAQUIM
PERMAQUIM s
PERMAQUIM LTDA
PERMAQUIM S A
PERMAQUIM S.A.
PERMAQUIMSA
PERMAQUM S.S..


i have a column with customer names but spellings are messed up as you
could see above. i have sample of that data above. therer are
thousonds of records like this. for example the third set of customers
should be under name Permaquim. Is there any way in Access to correct
these names by a query or combining tables. i donot have the correct
list of customers. By looking at the names of customers, i have to
make out what is right. for example you could make out that in the
list of "permaqium" , all those customers should be under one name.

i donot have to know the exact name but when i calculate the sums for
these customers, i am getting multiple sums for each permaqium. All i
need is one sum for permaquim

Any ideas? pls help. any formulas?

Thanks

 

[John W. Vinson]

HOLLAND CO
HOLLAND COLORS
HOLLAND COLOUR
HOLLAND COLURS
HOLLANDCOLORSI

HEUCOTECH
HEUCOTECH
HEUCOTECH LIMI
HEUCOTECH LTD
HEUCOTECH LTD.
HEUCOTECHLTD


PERMAQIUM
PERMAQUIM
PERMAQUIM s
PERMAQUIM LTDA
PERMAQUIM S A
PERMAQUIM S.A.
PERMAQUIMSA
PERMAQUM S.S..


i have a column with customer names but spellings are messed up as you
could see above.

This is a classic case of "garbage in, garbage out", and a great argument for
giving the users a properly normalized database in the first place (where they
could have *selected* a company from a combo box, storing the unique ID,
rather than letting them type (or mistype) freehand.

Unfortunately there's probably NO perfect automated solution. You'll end up
using a USB interface (Using Someone's Brain) to get it done. My best
suggestion would be to run a query grouping by

Left([CompanyName], 6)

to group by the leftmost six letters (play with it, five, eight, etc.), or by

Left([CompanyName], InStr([CompanyName], " ") - 1)

to extract the first "word".

Good luck. It's going to be a chore.

 

[John Spencer]

If you have the time to fix this, the best solution would be to build a
table with two columns-fldCorrectValue and fldExistingValue.

Step 1 would be to create records for all the existing unique values in
tableGarbage (your existing table)

INSERT INTO tblFix(fldExistingValue)
SELECT DISTINCT CompanyName
FROM tableGarbage

Now the hard part, open tblFix and sort the names in order.
Enter the desired name into fldCorrectValue
So for example, for all the variations of Permaquim, you would have
Permaquim entered. Copy and paste can speed this up and prevent errors
in entry (or use the keyboard shortcut Control + quote to enter the
value from the prior record into the current record).

Once you have all the fields filled, a simple update query will fix
tableGarbage.
UPDATE tableGarbage INNER JOIN tblFix
ON tableGarbage.CompanyName = tblFix.fldExistingValue
SET tableGarbage.CompanyName = [tblFix].[fldCorrectValue]

OR you can just join tableGarbage to tblFix in your query and use
fldCorrectValue instead of tableGarbage.CompanyName

Once you have this fixed, you can use a distinct query against
tblFix.fldCorrectValue to populate a combobox to control the entry of
names into the table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

HOLLAND CO
HOLLAND COLORS
HOLLAND COLOUR
HOLLAND COLURS
HOLLANDCOLORSI

HEUCOTECH
HEUCOTECH
HEUCOTECH LIMI
HEUCOTECH LTD
HEUCOTECH LTD.
HEUCOTECHLTD


PERMAQIUM
PERMAQUIM
PERMAQUIM s
PERMAQUIM LTDA
PERMAQUIM S A
PERMAQUIM S.A.
PERMAQUIMSA
PERMAQUM S.S..


i have a column with customer names but spellings are messed up as you
could see above.

This is a classic case of "garbage in, garbage out", and a great argument for
giving the users a properly normalized database in the first place (where they
could have *selected* a company from a combo box, storing the unique ID,
rather than letting them type (or mistype) freehand.

Unfortunately there's probably NO perfect automated solution. You'll end up
using a USB interface (Using Someone's Brain) to get it done. My best
suggestion would be to run a query grouping by

Left([CompanyName], 6)

to group by the leftmost six letters (play with it, five, eight, etc.), or by

Left([CompanyName], InStr([CompanyName], " ") - 1)

to extract the first "word".

Good luck. It's going to be a chore.

 

[subs]

If you have the time to fix this, the best solution would be to build a
table with two columns-fldCorrectValue and fldExistingValue.

Step 1 would be to create records for all the existing unique values in
tableGarbage (your existing table)

INSERT INTO tblFix(fldExistingValue)
SELECT DISTINCT CompanyName
FROM tableGarbage

Now the hard part, open tblFix and sort the names in order.
Enter the desired name into fldCorrectValue
So for example, for all the variations of Permaquim, you would have
Permaquim entered.  Copy and paste can speed this up and prevent errors
in entry (or use the keyboard shortcut Control + quote to enter the
value from the prior record into the current record).

Once you have all the fields filled, a simple update query will fix
tableGarbage.
UPDATE tableGarbage INNER JOIN tblFix
ON tableGarbage.CompanyName = tblFix.fldExistingValue
SET tableGarbage.CompanyName = [tblFix].[fldCorrectValue]

OR you can just join tableGarbage to tblFix in your query and use
fldCorrectValue instead of tableGarbage.CompanyName

Once you have this fixed, you can use a distinct query against
tblFix.fldCorrectValue to populate a combobox to control the entry of
names into the table.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

This is a classic case of "garbage in, garbage out", and a great argument for
giving the users a properly normalized database in the first place (where they
could have *selected* a company from a combo box, storing the unique ID,
rather than letting them type (or mistype) freehand.

Unfortunately there's probably NO perfect automated solution. You'll end up
using a USB interface (Using Someone's Brain) to get it done. My best
suggestion would be to run a query grouping by

Left([CompanyName], 6)

to group by the leftmost six letters (play with it, five, eight, etc.),or by

Left([CompanyName], InStr([CompanyName], " ") - 1)

to extract the first "word".

Good luck. It's going to be a chore.- Hide quoted text -

- Show quoted text

SO WHAT IS THE BEST SOLUTION . WE GET THIS DATA FROM AN OUTSIDE
COMPANY. AS FAR AS I KNOW SOME OF THIS DATA IS MANUAL AND SOME COMES
THROUGH SAP INTO THE ACCESS DATABASE. CAN THEY DO ANYTHING TO MAKE
SURE IT IS CORRECTED AT THE SOURCE. NOW IF THEY HAVE A COMBO BOX
WHEREIN THE DATA ENTRY PERSON CAN SELECT FROM, DO THEY NEED TO HAVE
THE CORRECT LIST OF CUSTOMERS( WITH THEIR SPELLINGS EXACTLY RIGHT?)

CAN THIS BE CORRECTED NOW?

 

[Bob Barrows]

SO WHAT IS THE BEST SOLUTION . WE GET THIS DATA FROM AN OUTSIDE
COMPANY. AS FAR AS I KNOW SOME OF THIS DATA IS MANUAL AND SOME COMES
THROUGH SAP INTO THE ACCESS DATABASE. CAN THEY DO ANYTHING TO MAKE
SURE IT IS CORRECTED AT THE SOURCE. NOW IF THEY HAVE A COMBO BOX
WHEREIN THE DATA ENTRY PERSON CAN SELECT FROM, DO THEY NEED TO HAVE
THE CORRECT LIST OF CUSTOMERS( WITH THEIR SPELLINGS EXACTLY RIGHT?)

CAN THIS BE CORRECTED NOW?

You seem to be having problems using your keyboard. There;s a key called
"Caps Lock" over at the left side of your keyboard directly above the Shift
key.

I suggest reading the previous replies again. There is really nothing that
can be added to them and they do thoroughly answer your questions.

 

[John Spencer]

In my opinion, the best solution is to have the table with the
misspelled names and the correct names.

Then in the future, you can run a query to see what values are new and
add them to your table tblFix

This will add any new values into tblFix

INSERT INTO tblFix (fldExistingValue)
SELECT Distinct CompanyName
FROM tableGarbage LEFT JOIN tblFix
ON tableGarbage.CompanyName = tblFix.fldExisting
WHERE tblFix.fldExisting is Null

Then you will have to populate the null fldCorrectValue fields. And
then you can either fix the bad data in tableGarbage or just link to
tblFix and use the fldCorrectValue.

As far as the outside company being able to fix the data - it is
possible, but you need to talk to them. They should be able to use a
process similar to the above, but perhaps they are not interested.

In a business sense, who benefits? If they benefit, they should either
do the work or pay you to do the work. If you benefit, then I guess you
would need to do the work.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================