Report to display total number of empty fields

[Ixtreme]

I have an Access 2000 database with a 7 tables, each containing a 20
to 30 fields. I use this database to automatically import ~ delimited
data. The purpose of the database is to check if the data is ready to
be imported by some other software program. Basically it is used to do
some data analysis to determine the quality of the files. Some of
these fields are required (may not be empty).

What I would like is to create some kind of report displaying per
table all fields that have ampty fields / values.

Table 1
Field 1 is empty: 20 records
Field 2 is empty: no empty records
Field 3 is empty: 4 records

Table 2
Field1 is empty: 24 records
FieldName 2 is empty: 4 records
etc

 

[John Spencer]

Here is a quick example of a way to do this.

SELECT Count(*)- Count(FAQ.fID) AS CountOffID
, Count(*)- Count(FAQ.fPriority) AS CountOffPriority
, Count(*)- Count(FAQ.fSubject) AS CountOffSubject
, Count(*)- Count(FAQ.fText) AS CountOffText
, Count(*)- Count(FAQ.fKeywords) AS CountOffKeywords
, Count(*)- Count(FAQ.fLink) AS CountOffLink
, Count(*)- Count(FAQ.fExtendedText) AS CountOffExtendedText
FROM FAQ;

Or if you want you can just count the fields that DO have data. Your Primary
key field could be the reference mark.

A quick way to build this would be to
-- open a new query
-- add all the fields (Double click the table header to select all the fields
and then drag them en mass into the grid)
-- Select View Totals from the menu
-- Change all the GROUP BY to Count in the Totals line
If you want to show the null (blank) record count
-- Switch to SQL view
-- Enter Count(*) - in front of each field (copy and paste "Count(*) -" to
speed this up.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ixtreme]

John,

Many thanks. It worked fine!

Best regards,

Mark