By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Query crazy?

P: 9
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?
May 24 '08 #1
Share this Question
Share on Google+
2 Replies


JustJim
Expert 100+
P: 407
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
May 26 '08 #2

JustJim
Expert 100+
P: 407
No, that's alright. It's a pleasure to help.

Jim
Jun 18 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.