Loop thru all fields in all tables to replace bogus data

[fambaus]

Hello Gurus -

I would like to create a function that will loop through all the fields in a
table, exmaine the value of the field and replace the value if needed based
on the field type of course.

Call it a data cleansing exercise.

I've been away from VBA too, too long..

Thank you all,
Mike

 

[fredg]

Hello Gurus -

I would like to create a function that will loop through all the fields in a
table, exmaine the value of the field and replace the value if needed based
on the field type of course.

Call it a data cleansing exercise.

I've been away from VBA too, too long..

Thank you all,
Mike

You have asked a "How to" question but have given no "if needed"
specifics. A generalized reply to your generalized question would most
likely be to run an Update query.

 

[fambaus]

Hi Fred -

I appreciate your time with my issue.
I will certainly provide more specifics.

The situation is this:
I have importred a great deal of data into Access.
The sourcce of the database was an extremely large Oracle db.
The data was exported from Oracle in CSV format and imported into Access
with the use of import specification for each file/table.
BTW - Linking the tables into Access was not an option either as the Oracle
instance contains thousands of tables. Set up was tedious at best for about
ten tables.

Turns out that altough some of the fields have the Not Null provision in
Oracle, Access has converted blank fields to Null. This is causing me problem
with my Access queries since as we know Null is not equal Null.

I could conceivably created an update query on each table and convert each
occurance of Null to NA etc. I did this with two table and found it
cumbersome.

Thus my posting to this forum. About ten years ago I probably wrote a
function something similar to what I need. Unfortunately things have advanced
and I have fallen behind.

Thank you once again,
Mike

 

[Douglas J. Steele]

While it's possible, I question whether it's really necessary. You can
always use WHERE Nz(Table1.Field1, "NA") = Nz(Table2.Field1, "NA") rather
than WHERE Table1.Field1 = Table2.Field1

Fred's right, though, that an Update query (or, more accurately, a series of
Update queries) is the correct way to go. It's almost always more efficient
to use SQL than VBA to accomplish the same task. Your Update queries would
be something like:

UPDATE Table1 SET Field1 = "NA" WHERE Field1 IS NULL
UPDATE Table1 SET Field3 = "NA" WHERE Field3 IS NULL

Now, you can certainly use VBA to loop through the tables to generate the
queries. Obviously you only need to worry about fields that are Text or
Memo, so code like the following should work:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
If fldCurr.Type = dbText Or _
fldCurr.Type = dbMemo Then

strSQL = "UPDATE [" & tdfCurr.Name & "] " & _
"SET [" & fldCurr.Name & "] = 'NA' " & _
"WHERE [" & fldCurr.Name & "] IS NULL"
dbCurr.Execute strSQL, dbFailOnError
End If
Next fldCurr
End If
Next tdfCurr
Set dbCurr = Nothing

 

[fambaus]

HI Douglas -
Great feedback.. Thank you very much.

As to the why am I undertaking such an exercise..
Well I can not get an accurate unmatched record query working.
I thought the Null fields are the main culprit.

I have two tables which are have the exact same structure.

The tables have a primary key that is made up of seven fields.

I can get a count of the number of records in table A and table B.
The Left Join such doesn't seem to work.

Thank you for your time,
Mike

While it's possible, I question whether it's really necessary. You can
always use WHERE Nz(Table1.Field1, "NA") = Nz(Table2.Field1, "NA") rather
than WHERE Table1.Field1 = Table2.Field1

Fred's right, though, that an Update query (or, more accurately, a series of
Update queries) is the correct way to go. It's almost always more efficient
to use SQL than VBA to accomplish the same task. Your Update queries would
be something like:

UPDATE Table1 SET Field1 = "NA" WHERE Field1 IS NULL
UPDATE Table1 SET Field3 = "NA" WHERE Field3 IS NULL

Now, you can certainly use VBA to loop through the tables to generate the
queries. Obviously you only need to worry about fields that are Text or
Memo, so code like the following should work:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
If fldCurr.Type = dbText Or _
fldCurr.Type = dbMemo Then

strSQL = "UPDATE [" & tdfCurr.Name & "] " & _
"SET [" & fldCurr.Name & "] = 'NA' " & _
"WHERE [" & fldCurr.Name & "] IS NULL"
dbCurr.Execute strSQL, dbFailOnError
End If
Next fldCurr
End If
Next tdfCurr
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Fred -

I appreciate your time with my issue.
I will certainly provide more specifics.

The situation is this:
I have importred a great deal of data into Access.
The sourcce of the database was an extremely large Oracle db.
The data was exported from Oracle in CSV format and imported into Access
with the use of import specification for each file/table.
BTW - Linking the tables into Access was not an option either as the
Oracle
instance contains thousands of tables. Set up was tedious at best for
about
ten tables.

Turns out that altough some of the fields have the Not Null provision in
Oracle, Access has converted blank fields to Null. This is causing me
problem
with my Access queries since as we know Null is not equal Null.

I could conceivably created an update query on each table and convert each
occurance of Null to NA etc. I did this with two table and found it
cumbersome.

Thus my posting to this forum. About ten years ago I probably wrote a
function something similar to what I need. Unfortunately things have
advanced
and I have fallen behind.

Thank you once again,
Mike

 

[John W. Vinson]

I have importred a great deal of data into Access.
The sourcce of the database was an extremely large Oracle db.
The data was exported from Oracle in CSV format and imported into Access
with the use of import specification for each file/table.
BTW - Linking the tables into Access was not an option either as the Oracle
instance contains thousands of tables. Set up was tedious at best for about
ten tables.

Turns out that altough some of the fields have the Not Null provision in
Oracle, Access has converted blank fields to Null. This is causing me problem
with my Access queries since as we know Null is not equal Null.

One possibilty might be to define the tables with these fields Required, Allow
Zero Length = Yes. The import should then put in a ZLS (which *does* work in
joins and searches) rather than a NULL.

 

[Douglas J. Steele]

As I said, you should be able to use

FROM Table1 LEFT JOIN Table2
ON Nz(Table1.Field1, "NA") = Nz(Table2.Field1, "NA")
AND Nz(Table1.Field2, "NA") = Nz(Table2.Field2, "NA")
AND Nz(Table1.Field3, "NA") = Nz(Table2.Field3, "NA")
....
AND Nz(Table1.Field7, "NA") = Nz(Table2.Field7, "NA")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI Douglas -
Great feedback.. Thank you very much.

As to the why am I undertaking such an exercise..
Well I can not get an accurate unmatched record query working.
I thought the Null fields are the main culprit.

I have two tables which are have the exact same structure.

The tables have a primary key that is made up of seven fields.

I can get a count of the number of records in table A and table B.
The Left Join such doesn't seem to work.

Thank you for your time,
Mike

While it's possible, I question whether it's really necessary. You can
always use WHERE Nz(Table1.Field1, "NA") = Nz(Table2.Field1, "NA") rather
than WHERE Table1.Field1 = Table2.Field1

Fred's right, though, that an Update query (or, more accurately, a series
of
Update queries) is the correct way to go. It's almost always more
efficient
to use SQL than VBA to accomplish the same task. Your Update queries
would
be something like:

UPDATE Table1 SET Field1 = "NA" WHERE Field1 IS NULL
UPDATE Table1 SET Field3 = "NA" WHERE Field3 IS NULL

Now, you can certainly use VBA to loop through the tables to generate the
queries. Obviously you only need to worry about fields that are Text or
Memo, so code like the following should work:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strSQL As String

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
If fldCurr.Type = dbText Or _
fldCurr.Type = dbMemo Then

strSQL = "UPDATE [" & tdfCurr.Name & "] " & _
"SET [" & fldCurr.Name & "] = 'NA' " & _
"WHERE [" & fldCurr.Name & "] IS NULL"
dbCurr.Execute strSQL, dbFailOnError
End If
Next fldCurr
End If
Next tdfCurr
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Fred -

I appreciate your time with my issue.
I will certainly provide more specifics.

The situation is this:
I have importred a great deal of data into Access.
The sourcce of the database was an extremely large Oracle db.
The data was exported from Oracle in CSV format and imported into
Access
with the use of import specification for each file/table.
BTW - Linking the tables into Access was not an option either as the
Oracle
instance contains thousands of tables. Set up was tedious at best for
about
ten tables.

Turns out that altough some of the fields have the Not Null provision
in
Oracle, Access has converted blank fields to Null. This is causing me
problem
with my Access queries since as we know Null is not equal Null.

I could conceivably created an update query on each table and convert
each
occurance of Null to NA etc. I did this with two table and found it
cumbersome.

Thus my posting to this forum. About ten years ago I probably wrote a
function something similar to what I need. Unfortunately things have
advanced
and I have fallen behind.

Thank you once again,
Mike

:

On Thu, 11 Sep 2008 13:30:00 -0700, fambaus wrote:

Hello Gurus -

I would like to create a function that will loop through all the
fields
in a
table, exmaine the value of the field and replace the value if
needed
based
on the field type of course.

Call it a data cleansing exercise.

I've been away from VBA too, too long..

Thank you all,
Mike

You have asked a "How to" question but have given no "if needed"
specifics. A generalized reply to your generalized question would most
likely be to run an Update query.

 

[fambaus]

Hi John and Doug -
I do appreciate your time on this.

What I decide to do was clean up the data on the extract process.
Any field that contained a Null was change to 'NA".

I re-imported the data into the two tables. Recreated the primary keys on
both tables.

Tried the Left Join for missing records and stil not getting the correct
number of records.

Hmm..
Well, Thanks once again,
Mike