Expression Builder and Count

[Daezle]

Hi All,



I just joined the forum as I'm going to be using quite a lot of new queries over the next few months, but I've become a bit stuck so hopefully someone can point me in the right direction.

I want to count each record in a row only if it is not null. So basically i want to end up with a calculated column that shows me what % of the target other columns are complete (not null).

Unique Order Number	Colour	Paper Size	Cost	% Complete
1	Red	A4		75%
2	Blue	A3	2.75	100%
		A2	3.00	50%
4				25%

I've tried using the count function and still can't manage to get it right. This is the latest attempt to simply count the non-null values (in the expression builder):

Expr1: Sum(IIf(IsNull([table1]![UniqueOrderNumber]),0,1)+IIf(IsNull([table1]![Colour]),0,1)+IIf(IsNull([table1]![PaperSize]),0,1)+IIf(IsNull([table1]![Cost]),0,1))

Any help would be greatly appreciated.

David

[ranman256]

2 queries: 1 counts null rows, 1 counts not null.

[JoeM]

I've tried using the count function and still can't manage to get it right. This is the latest attempt to simply count the non-null values (in the expression builder):

Expr1: Sum(IIf(IsNull([table1]![UniqueOrderNumber]),0,1)+IIf(IsNull([table1]![Colour]),0,1)+IIf(IsNull([table1]![PaperSize]),0,1)+IIf(IsNull([table1]![Cost]),0,1))

Instead of adding those values inside your SUM statement, try multiplying them. Then, it will only return 1 if all are NOT null, but if any are null, it will return zero and won't be included in your count.


EDIT: OP changed the wording of their original question, rendering this reply pretty much worthless, as it was based on the original wording.

[Daezle]

Instead of adding those values inside your SUM statement, try multiplying them. Then, it will only return 1 if all are NOT null, but if any are null, it will return zero and won't be included in your count.

Thanks for your response Joe, that does work for the intended purpose but not quite what i originally wanted. I've edited my post to correct the wording.

[JoeM]

Thanks for your response Joe, that does work for the intended purpose but not quite what i originally wanted. I've edited my post to correct the wording.

In the future, it is probably best to just clarify it in a response, and not go back and change the original wording, as that pretty much can kill the flow of the thread and makes previous responses look out-of-place and confusing.

If you simply want to know for each record the percent that is complete, just add up your IIF statements and divide by 4, i.e.

Expr1: (IIf(IsNull([table1]![UniqueOrderNumber]),0,1)+IIf(IsNull([table1]![Colour]),0,1)+IIf(IsNull([table1]![PaperSize]),0,1)+IIf(IsNull([table1]![Cost]),0,1))/4

Note, you would NOT use SUM on this function. In Access, you only use SUM when aggregating multiple records, not multiple fields within a single record.

[Daezle]

In the future, it is probably best to just clarify it in a response, and not go back and change the original wording, as that pretty much can kill the flow of the thread and makes previous responses look out-of-place and confusing.

If you simply want to know for each record the percent that is complete, just add up your IIF statements and divide by 4, i.e.

Expr1: (IIf(IsNull([table1]![UniqueOrderNumber]),0,1)+IIf(IsNull([table1]![Colour]),0,1)+IIf(IsNull([table1]![PaperSize]),0,1)+IIf(IsNull([table1]![Cost]),0,1))/4

Note, you would NOT use SUM on this function. In Access, you only use SUM when aggregating multiple records, not multiple fields within a single record.

Thanks for clarifying. That is what i was looking for thank you very much JoeM.

[NoellaG]

Hi,

tip: instead the Iff(Isnull(.... construction, try the NZ function

[JoeM]

tip: instead the Iff(Isnull(.... construction, try the NZ function

Since they need to return either a 1 or 0, I am not sure that helps in this scenario.
NZ will return either the value or some other designated value if the value is Null.