Reporting on nulls within a Database

[Chris]

Hi Group.

I have a very big job ahead of me and I would appreciate
some advice before I begin.
I have inherited an applications Database in work, which
contains about 100 tables and approx 5000 records.
The master table in my Database is called tblApplication
and each table is linked by primary key field
ApplicationID.
In order to clean up the Database to allow for better
reporting Management have asked me to produce, by
applicant (ApplicationID), a list of fields that contain
Nulls, like so...

ApplicationID: 123456
Address1 (Table1) }
Approved (Table2) } Illustration only
Amount (Table3) }

The idea being that once the blank fields have been
identified staff will be asked to update these fields with
required data (data that should have been added in the
first place).

Does anyone know how I could go about this - I can produce
a Nulls query for a single field but how do I expand this
to include all fields and tables within the Database?

Regards

Chris

 

[Guest]

Hi Chris,

It seems like your best bet may be to use VB code to loop through the tables
and fields values and log all of the field names with null values a table
with the associated ApplicationID's. But, are you sure all 100 tables link
to the primary table? And, if so, do all use the same field name
"ApplicationID"? If they do, you should be able to write code to loop
through all ApplicationID's. If they don't, you still may be able to - but
it will likely require some extra thinking and programming steps.

You may want to post in the modulesdaovba group with a little more detail on
whether you have any programming experience, whether all linked tables use
the same field name, whether you want to treat a zero length string as a
null, whether linked tables are 1:1, 1:many (if 1:many, do you want to log
all instances of nulls, or ignore as long as one record has a non-null
value), etc, so that you can get help with the code.

HTH, Ted Allen

 

[Chris]

Ted,

Thank you for your detailed, informative and helpful reply.
I will do as you suggested and will post the question,
amended as necessary, to the modulesdaovba Group.

Thanks for your help

Chris