Counting active fields

[Pwyd]

How could i programmatically count the number of fields which aren't null,
adjusting that count every time i change which record i'm looking at?

Could i count only whether some specific fields are null?

I was thinking of making a query that counted the records that met the
criteria, except that by including each new field, i'm narrowing rather than
enlarging the field of records it will include. Is there a way to make an
"or" without having to directly edit the sql of the query?

 

[Allen Browne]

I take it you hae a bunch of similar fields in the table, and in your query
you want a count of how many of those fields in that recrod are Null?

You could do this by typing an expression like this into the Field row:
-([F1] Is Null) - ([F2] Is Null) - ([F3] Is Null) - ([F4] Is Null) - ...
substituting your field names for F1, F2, F3, F4, etc.

This works because the test for Is Null returns True (if it is null) or
False (if it isn't.) Access uses -1 for True, and 0 for False. Therefore
summing Is Null expressions gives the negative of the number of null fields.

However, the table you have here might not be the right way to design a
database. Repeating the fields across the table like that is very common in
a spreadsheet, but in a relational database these values should be many
*records* in a related table, rather than many fields in this table.

Search on "normalization" if you want to know more about that. Here's a
starting point for some reading:
http://allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

To answer your final question, you can use the OR rows in query design
(below the Criteria), but you may need to repeat some criteria on several
rows.

 

[Pwyd]

This works perfectly, thank you. Tell me, why does Access choose to use -1
(null) as its "true" value instead of the standard 0 or 1 used in most
programming languages?

I take it you hae a bunch of similar fields in the table, and in your query
you want a count of how many of those fields in that recrod are Null?

You could do this by typing an expression like this into the Field row:
-([F1] Is Null) - ([F2] Is Null) - ([F3] Is Null) - ([F4] Is Null) - ...
substituting your field names for F1, F2, F3, F4, etc.

This works because the test for Is Null returns True (if it is null) or
False (if it isn't.) Access uses -1 for True, and 0 for False. Therefore
summing Is Null expressions gives the negative of the number of null fields.

However, the table you have here might not be the right way to design a
database. Repeating the fields across the table like that is very common in
a spreadsheet, but in a relational database these values should be many
*records* in a related table, rather than many fields in this table.

Search on "normalization" if you want to know more about that. Here's a
starting point for some reading:
http://allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

To answer your final question, you can use the OR rows in query design
(below the Criteria), but you may need to repeat some criteria on several
rows.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How could i programmatically count the number of fields which aren't null,
adjusting that count every time i change which record i'm looking at?

Could i count only whether some specific fields are null?

I was thinking of making a query that counted the records that met the
criteria, except that by including each new field, i'm narrowing rather
than enlarging the field of records it will include. Is there a way to
make
an "or" without having to directly edit the sql of the query?

 

[Allen Browne]

-1 (as an integer or long) in represents in binary as all bits turned on.
0 is all bits turned off.
Somebody thought that was logical.

0 and 1 make sense if you think purely as a bit. Somebody else thought that
was logical.

Who sets the standards?

 

[Pwyd]

I suppose that depends who you ask. If you ask bell labs, they set the
standards eons ago. The people redesigning c++ on the other hand think
they're setting the standards. Just the same. I was curious as to whether
or not it had any coding value, not whether or not they were following
syntactual tradition.