Count Blank Fields

[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?

 

[PC Datasheet]

The first thing to do is to examine the design of your table. With 150
fields, it is unequivocally not normalized.

 

[John Spencer (MVP)]

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.