Data Cleaning Mess

[Guest]

I am working on creating a PO database in Access 2000 and have a data mess.
The original PO system was an excel spreadsheet with people filling in what
information they throught was needed. The biggest problem I am having is in
my cost and quantity fields. The fields were created as text fields as
opposed to number fields. So I have things like 9.99 plus shipping in cost
or 3pcs. in quantity. Is there a way to query this info and have access
seperate the fields that have numbers ONLY (uswing a create table most
likely) , and using that same methodology run a search and replace to clean
up what I can before just going in and hand cleaning the table?

Any help would be appreciated GREATLY.

 

[Chaim]

As you correctly observe, you have a mess.

The bad news is that even if there were a logical way to parse/identify some
of this data, since you have no constants to work with, you would still end
up having to do things mostly by hand.

I don't think there is good news. I don't envy you.

Good Luck!

 

[Guest]

Maybe I need to clarify this a bit. I am looking for a query method to view
a text box and seperate the records where values in the text box are numbers
only. For example, to seperate all the 1,2,3 from the 1#,2 dozen and 3pcs.
I cannot determine a way to do this.

Rich

 

[George Nicholson]

How about a query with "IsNumeric([Cost]) = False" as a criteria on [Cost],
etc.?

HTH,

 

[John Vinson]

I am working on creating a PO database in Access 2000 and have a data mess.
The original PO system was an excel spreadsheet with people filling in what
information they throught was needed. The biggest problem I am having is in
my cost and quantity fields. The fields were created as text fields as
opposed to number fields. So I have things like 9.99 plus shipping in cost
or 3pcs. in quantity. Is there a way to query this info and have access
seperate the fields that have numbers ONLY (uswing a create table most
likely) , and using that same methodology run a search and replace to clean
up what I can before just going in and hand cleaning the table?

Any help would be appreciated GREATLY.

This can indeed be a major hassle. Manual intervention will almost
certainly be needed; and even there you'll undoubtedly have some
incorrect amounts ($9.99 plus HOW MUCH shipping...!?)

There are some tools you can use which can help. The Val() function
will return the numeric value of a string, if it begins with a
recognizable number: e.g.

Val("9.99 plus shipping")

will return a numeric value 9.99. However it will not find a number
*within* a string such as "Approximately 50 shipped" (for a horrible
example).

I would certainly *keep* this field, in addition to a Currency
datatype field for both the Cost and Quantity fields; use Update
queries to move whatever data can be moved into the numeric fields,
and manually review the data to be sure it's correct.

John W. Vinson[MVP]

 

[Guest]

I found a way to find out if any of the cost fields had bad fields in them:

like *[a-z]* in the query

To clean, I run a search and replace on some of the more common entries,
like + ship or Lot or pcs. It is not a very glamorous method, but that and
some table time should get the bulk of this done quicker then I thought. I
did want to thank those who replied to my initial question. This is the
first time I used this forum and I will keep coming back.

Rich S.