using the count function to count number of fields that are not null

[Roncc]

I am working on a database for a local food bank. I have a table that lists the ages of the members of a household that is receiving assistance. I have created a form to keep track of the items a household receives on each visit and on that form, I want to display the number of members in the household. I have created a text box and am trying to place a formula in it that tells me how many of the fields that hold the ages of the household members are not null. Short example: A household has 3 people in it ages 25,24, and 3. The fields names are d1age, d2age, and d3age. The field values are then 25, 24, and 3 respectively and the count of not null values is therefore 3. For the formula in the text box I have tried count([d1age]<>null, [d2age]<>null, [d3age]<>null), I have tried various additions of commas, quotation marks, etc. and nothing works. After some research I tried =Sum(IIf([d1age] Is Not Null And [d2age] Is Not Null And [d3age] Is Not Null,1,0)) using the "Sum" function with the IIF addition but, that did not work either. It is important that (age)fields with null values (empty fields) not be counted so I end up with an accurate count of the number of people in the household. I have a total of 10 age fields in the table. Thank you any assistance you can provide.

[Micron]

The fields names are d1age, d2age, and d3age. The field values are then 25, 24, and 3 respectively

Then your tables are not normalized and you will always struggle with this design. That is how a spreadsheet is, not tables in a relational database.
Suggest you read up on db normalization
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

EDIT - Is Null is for queries, IsNull is for vba

[ArviLaanemets]

An example how a normalized DB can be:
You have tables:
tblHouseholds: HouseholdID, ...;
tblHouseholdMembers: HouseholdMemberID, HouseholdID, Forename, LastName, DOB, ...;
You have (a single) form fHouseholds;
You have a countinuous form fHouseholdMembers, with textbox linked to HouseholdID hidden;
You add fHouseholdMembers as subform sfHouseholdMembers to fHouseholds, linked by HouseholdID.

Now when you open a form fHouseholds, and select there any household, in subform are displayed all members of this household. When you add a new household member into subform, it will be automatically linked with household selected in main form (active HouseholdID value in main form is stored in hidden textbox in subform).

Instead of current age of household members, you store DOB instead. Otherwise the age of any household member will be wrong after some time (max 365 days). Ages of household members at current date can be calculated as Datediff("yyyy", DOB, Date) - (when birthday is earlier than today, the 1, otherwise 0).

Now you simply summarize current ages of all household members for given household.

[June7]

Aggregate functions aggregate records not fields. You have to "roll your own". Cannot compare anything to NULL, not even another NULL, because there is nothing to compare.

If you want a count for each record, expression in query:

IIf(d1age IS NULL, 0, 1) + IIf(d2age IS NULL, 0, 1) + IIf(d3age IS NULL, 0, 1)

in textbox:

=IIf(IsNull(d1age), 0, 1) + IIf(IsNull(d2age), 0, 1) + IIf(IsNull(d3age), 0, 1)

If you just want a grand total with a query.

SELECT Nz(Sum(d1age),0) + Nz(Sum(d2age),0) + Nz(Sum(d3age),0) AS Cnt FROM table;


Otherwise, normalize data structure. Some day you will encounter a family with more than 10 members.

[xps35]

By the way: it is not a good idea to store ages. They can change every day. Better store date of birth.

[Roncc]

Aggregate functions aggregate records not fields. You have to "roll your own". Cannot compare anything to NULL, not even another NULL, because there is nothing to compare.



If you want a count for each record, expression in query:

IIf(d1age IS NULL, 0, 1) + IIf(d2age IS NULL, 0, 1) + IIf(d3age IS NULL, 0, 1)

in textbox:

=IIf(IsNull(d1age), 0, 1) + IIf(IsNull(d2age), 0, 1) + IIf(IsNull(d3age), 0, 1)

If you just want a grand total with a query.

SELECT Nz(Sum(d1age),0) + Nz(Sum(d2age),0) + Nz(Sum(d3age),0) AS Cnt FROM table;


Otherwise, normalize data structure. Some day you will encounter a family with more than 10 members.

I used your statement for the textbox and it worked like a charm. By the way, I actually do use the dates of birth to calculate the ages so they stay current. Thanks again.

[Roncc]

That's what I do. Thank you for taking the time to reply.

[Roncc]

Thank you for taking the time to reply. The database I have created is not a relational database, there are only a few tables and I find it easier (and less confusing) to simply place all the needed fields in one table. This is actually the first time I have run into this type of problem. Thank you for again responding.

[Roncc]

I used the statement you suggested for the text box and it worked like a charm. Thank you so much for taking the time to help me out.

[June7]

Might want to become familiar with UNION query which can rearrange fields to normalized structure. I have a db that does not fully normalize and I needed UNION to accomplish some graphing.

SELECT ID, d1age AS Age, 1 AS FamSeqNo FROM table
UNION SELECT ID, d2age, 2 FROM table
UNION SELECT ID, d3age, 3 FROM table;

[Welshgasman]

By the way: it is not a good idea to store ages. They can change every day. Better store date of birth.

You say that, but I know of one Microsoft Partner that does just that.