First row in query blank?

[magster06]

Hello all,

I am running a query that is used to show a count of certain values. For example: The number of different Allegations.

The values would be grouped and counted:

Damage Claim 3
DUI 2

and so on. This works as it should in the query, but I noticed that the query has a blank in the first row.

Is this due to some records not having any values in the Allegation field?

This is what is looks like:

(Blank) 0
Damage Claim 3
DUI 2

It is not really a deal buster for me, I was just curious as to why this happens.

 

[plog]

It displays (Blank) because there are blank values for that field. It displays 0 because when (Blank) values are encountered in the Count() function they are disregarded. So while there might be 712 blank values, the count will always be 0.

If you want (Blank) values to not appear put this underneath the GROUP BY field:

Is Not Null

If you want an accurate count, I would not use the Allegations field to COUNT. I would use another field in the table that will always be populated--possibly an ID field.

 

[magster06]

Thanks for the explanation plog!

I thought it had something to do with the empty values.

 

[plog]

No problem--I edited my post as you were writing yours--look it over again I explained a way to get around this if needed.

 

[magster06]

If you want (Blank) values to not appear put this underneath the GROUP BY field:

Is Not Null

I will try this, thank you!

If you want an accurate count, I would not use the Allegations field to COUNT. I would use another field in the table that will always be populated--possibly an ID field.

I wish I could think of things like this! Good idea.

 

[Pat Hartman]

Unless you specifically want to count null values, it is better (and more efficient) to use Count(*) rather than Count(yourfieldname). If you use Count(*), the query engine may be able to satisfy the query by reading only an index. If you use count(yourfieldname), you force the query engine to read every single row in the table.

 

[magster06]

Thanks for the tip Pat!