Count Blank Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 150 fields in it. I need to quickly produce a count, by
field name, of the number of records where each field is null or blank. Then
I need to list the fields with their counts.

So, if there are a thousand records, the list might look like:
Field 1: 500
Field 2: 3
Field 3: 0
Field 4: 226 and so on.

I am struggling with finding a query structure. Any suggestions? Is this
problem better solved by using code rather than a query?
 
The first thing to do is to examine the design of your table. With 150
fields, it is unequivocally not normalized.
 
You could try something like the following.

SELECT Sum(IIF(Field1 is Null,1,0)) as Field1Count,
Sum(IIF(Field2 is Null,1,0)) as Field2Count,
....
FROM YourTable

That will return one row with a field for each field and the count for the field.
 
Back
Top