'Find and Replace' in SQL??

[matpj]

Hi all,

is it possible to write query that will do my data cleaning for me?

I have to go through a table, and Make sure Product Names are all spelt correctly etc.
I use the 'Find and Replace' function when in the table view.
Also, I have to delete certain rows depending on the product name, and the version numbers.

 

[flyboytim]

With respect, I would say that you are doing things backwards, and creating more work for your users, and yourself. If you need to correct product names, they cannot be selecting them from predefined ranges of products. Therefore you will never be able to match a Flurbomatic Dishwrencher from your range of products, even if it was misspelt, because you can't supply one. Let your users drill down by various criteria to your well defined list of products. Look at the online catalog and grocery businesses to see how they do it. Then, there is no doubt what is required and no cleaning. By all means devise a search tool to find the categories they may be seeking.

 

[ITMannie]

You can use Update and Delete queries to do what you're asking for.

To change the incorrect names I would set the criteria on the Update query to return the relevant records then set the correct value in the Update To line of the query.

Delete queries are even easier to use as you just set the criteria to return all the records you want to delete then run the query.

Let me know if you need more specific examples.

Cheers

Iain

 

[matpj]

Thank you both for your replies.

The data is coming from an existing problem tracking system.
I will certainly look at arranging for someone to create a drop down of products when logging a problem.
This will save my time.
The data is coming from a Lotus Notes database, and for some reason the query on version number is not very good!
I query on version number = 9 for example, and records with version number = 10.9.1 are also returned!!
for the time being I will use queries with update and delete.

thanks again,
Matt