Thread: Count Empty Fields In A Table

1. Registered User
Join Date
Dec 2010
Posts
134

Unanswered: Count Empty Fields In A Table

HI

does anyone know the Access 2010 equivalent of the Excel function which count empty cells?

=COUNTBLANK(range will tell me where I don't have data in Excel. But I need to calculate for an Access 2010 table and then ultimately to count the populated fields, to tell me the quality of the data in there.

Any clues on this?

2. Registered User
Join Date
Dec 2010
Posts
134
HI .. not a great response to this one...

Is that becuase it is too simple to bother with, or?...

Any clue would be very much appreciated.

3. Registered User
Join Date
Jul 2004
Location
South Dakota
Posts
267
Try searching the help for IsNull.

C

4. Moderator
Join Date
Mar 2009
Posts
5,442
Can you please precise if you mean:
a) Counting columns in a row having Null values (although Null is not a value)
b) Counting whole rows in a table (or query) that only have Null values (see above)
c) Counting Null values in any column of any row in a table (or query), i.e. count of Null (values) is a data set?

5. Registered User
Join Date
Dec 2010
Posts
134
S

thanks so much for coming back to me. I mean counting ALL the Nulls in the whole table.

Howwever, counting the number of nulls per row would also be extremely handy.

Pointers?

and,Thank you again.

6. Registered User
Join Date
Dec 2010
Posts
134
Canpus .. i will take a look for IsNull. thanks for the steer!!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
what you could do is something similar to

Query1: to set a value of 1 if a column is NULL
Code:
```SELECT MyTable.ID,
IIf(IsNull(ATextColumn),1,0) AS Col1,
IIf(IsNull(AnIntegerColumn),1,0) AS Col2,
FROM MyTable;```
Query2: sums the number of rows AND all NULL values
Code:
```SELECT
sum(Query1.Col1) AS NullsinCol1,
sum(Query1.Col2) AS NullsinCol2,
sum(Query1.Col3) AS NullsinCol3,
sum(Query1.Col1) + sum(Query1.Col2) + sum(Query1.Col3) AS ColsWithNullValues
FROM Query1;```
you could reverse the IIF logic and assign a value of 1 to columns with a non null value

8. Registered User
Join Date
Dec 2010
Posts
134
HI

Thanks for this. This is what I put in but it is saying syntax error. My table name is _Assessment_Data and the first three columns are named spreadsheet, county and city. I guess I would enter this for ever column, which could be the challenge in the case that there are 200+ OR that they change now and then.

However for now.. can you spot my mistake?

SELECT _Assessment_data.id,
IIf(IsNull(Country),1,0) AS Col2,
IIf(IsNull(City),1,0) AS Col3,
FROM _Assessment_data;

A final hint?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
additional comma after col3, you don't need a separator BEFORE the from statement

10. Registered User
Join Date
Dec 2010
Posts
134

SELECT _Assessment_data.id,
IIf(IsNull(Country),1,0) AS Col2,
IIf(IsNull(City),1,0) AS Col3
FROM _Assessment_data

same... syntax error ...

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
do you have a column called ID in table _Assessment_data ?

the SQL I posted works so its down to the changes you made

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
HI

Thanks for this. This is what I put in but it is saying syntax error. My table name is _Assessment_Data and the first three columns are named spreadsheet, county and city. I guess I would enter this for ever column, which could be the challenge in the case that there are 200+ OR that they change now and then
I have only ever come across one 200+ column design and that was a frightmare, which wasnt' really well designed

If you find you are adding or removing columns on the fly AND that number of columns in a table then I'd be very very suspicious that your design is not normalised

13. Registered User
Join Date
Dec 2010
Posts
134
OK thanks

Table is called _Assessment_data

The columns are "spreadsheet", "country", "city". Dont' have one call ID.

What 'is' ID - should be the first column or something? What is it there for / doing?

Thanks do much again....

14. Registered User
Join Date
Dec 2010
Posts
134

thx

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
The order of columns in a select query is irrelevant.

However if you use an order by clause then the order in that order by clause IS important, but the order the columns are SELECTED is irrelevant, although it makes more sense visually to order the columns in the select to match the order by.

eg
SELECT COL3, COL1, COL87, Col2 from mytable
is functionally the same as
SELECT COL1, COL3, COL2, Col87 from mytable

SELECT COL3, COL1, COL87, Col2 from mytable
ORDER BY Col1,Col3,Col2
is functionally the same as
SELECT COL1, COL3, COL2, Col87 from mytable
ORDER BY Col1,Col3,Col2

but in terms of viewing the results it makes more sense to use last form as the display (the select) is the same sequence as the order by

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•