Query to figure out null cells.

[chrisfl]

Another team in my organization wants to see where null spots are in their database. The table that this information would come from has a number of fields which have been filled in sporadically - they want to know how many unknowns are out there. Is there a way to design a query to figure this out? I would like to tell my team something like:

Field X is null in 30 out of 100 total records, or 30%

Field Y is null in 20 out of 100 total records, or 20%

Field Z is null in 50 out of 100 total records, or 50%



Thanks!

[pbaldy]

This type of thing should give you the raw numbers:

SELECT Sum(IIf(FieldX Is Null, 1, 0)) AS NullFieldX, Sum(IIf(FieldY Is Null, 1, 0)) AS NullFieldY, Count(*) AS TotalRecords
FROM TableName

I'd do the division in a report.