Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    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. #2
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    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. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Try searching the help for IsNull.

    C

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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?
    Have a nice day!

  5. #5
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    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. #6
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Canpus .. i will take a look for IsNull. thanks for the steer!!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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,
    IIf(IsNull(ADateColumn),1,0) AS Col3
    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
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    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(Spreadsheet),1,0) AS Col1,
    IIf(IsNull(Country),1,0) AS Col2,
    IIf(IsNull(City),1,0) AS Col3,
    FROM _Assessment_data;

    A final hint?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    additional comma after col3, you don't need a separator BEFORE the from statement
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1


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

    same... syntax error ...

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you have a column called ID in table _Assessment_data ?

    check your column names
    check your table name

    the SQL I posted works so its down to the changes you made
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Raddle View Post
    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
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    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. #14
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    ..totally agree with your comment about 200+ columns...

    thx

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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