Fix name field so all names are entered the same

[Zonie32]

I have a database with 4000 records. There is one field called AgentName that was originally manually entered by several people. So, there are many mismatched names for the same person. Depending on who entered the name, some people entered the full name, some entered a middle initial, some did not. Some put a period after the middle initial, etc.

Is there a way to quickly clean up the names in this field that are mismatched so they are all uniform? There is also another field called AgentID which is linked to the agentname, but the name linked to that number is not always spelled the right way. Any help is appreciated.

 

[jksmi]

Hi, is the AgentID unique? if so then you can use this to normalise your agentnames. A query like this

SELECT YOUR_TABLE.AgentID, First(YOUR_TABLE.AgentName) AS FirstOfAgentName
FROM YOUR_TABLE
GROUP BY YOUR_TABLE.AgentID;

will give you a single name for each id. Personally I'd recommend normalising your database so that you create a new table, LU_AGENT_DETAILS or something which holds all of the details relating to your agents inc the id and your other table only holds the id. However, since this may involve drastically redesigning your database you could also use the above query to update your table...

HTH, Jamie