Query crazy?

[MaMister]

Hi all,

I have a master database (mdb) with fax numbers but they are not in proper:

some: +65, (065), 065, 65 and some symbols like ><= and so on.

So I created this few query,

1st, Query to remove duplicate then from this query
2nd, Query to remove space then from this query
3rd, Query to remove +65 then from this query
4rd, Query to remove 65-

and it goes on and on with other things I wanted to remove.

Forgive me to ask. Is there a better way?

[JustJim]

Hi all,

I have a master database (mdb) with fax numbers but they are not in proper:

some: +65, (065), 065, 65 and some symbols like ><= and so on.

So I created this few query,

1st, Query to remove duplicate then from this query
2nd, Query to remove space then from this query
3rd, Query to remove +65 then from this query
4rd, Query to remove 65-

and it goes on and on with other things I wanted to remove.

Forgive me to ask. Is there a better way?

Hi,
You have stumbled accross one of the major problems of database design - the stupidity of users! A whole lot of our time is spent in trapping incorrect inputs to ensure the integrity of the data in the database.

The first thing you need is a set of "Business Rules" worked out by concensus from management and the users as to what format each piece of data should be in.

Then there are two things that you have to do.
1. Clean the data that you already have. This involves update queries as you have detailed above, and then probably some manual cleaning as well.
2. Make some data entry formats and rules that only allow data to be entered in the correct format.

Have fun.
Jim

[JustJim]

No, that's alright. It's a pleasure to help.

Jim