Access - count(iif based on values in 2 fields?

[shooshed]

I’m having trouble with a report field.

Need it to count records in a table only where BOTH:
1. Field1 – date is less than 1/1/2013; and
2. Field2 – is null

In the table, Fields 1 and 2 are date fields and either contain a date, or are left empty.

I’ve tried the following:

Expression: Count(IIf(([tbl].[field1]<#1/01/2013#) And ([tbl].[field2] Is Null),1,0))

but... not working. Any help would be appreciated!

[jimatqsi]

Your expression says <#1/01/2013# but your description of the problem says IS 1/1/2013. Is the code wrong or the description?

Jim

[shooshed]

The description is wrong - will edit now

[Rabbit]

Count doesn't care what it counts. Put your criteria in the where clause instead of using iif.

[shooshed]

Rabbit, I initially tried to put the criteria in the where clause but Access error message "cannot have aggregate function in where clause" arose.

I did some google searching and came across people using count(iif but none where the iif includes 2 variables (as above).

[twinnyfo]

shooshed,

Have you tried using the DCount Function:

DCount("*", "tbl", "[Field1] < #1/1/2013# AND [Field2] Is Null)

This has works with my tables....

Hope this hepps!

[Rabbit]

You don't put the aggregate function in where clause. You only put the filter criteria in the where clause. You leave the aggregate function in the select.

Those examples you saw using count(iif()) are wrong. It doesn't work. If anything, you saw sum(iif()). But while that technically works, it is very inefficient if you don't need to return all the rows. It's better to use the algorithm I laid out in the first paragraph.

[NeoPa]

@Twinny.
Domain Aggregate functions (DCount()) would be relatively inefficient as they have to enquire of the data separately, whereas Aggregate functions (Count(); Sum(); etc) perform within the context of the existing query so are more efficient. Also bear in mind that any use of a Domain Aggregate function would also need to reflect the current grouping in the filter. Your posted suggestion would count all of the records in the whole table rather than just those appropriate within the current GROUP BY group.

@Rabbit.

Rabbit:
Those examples you saw using count(iif()) are wrong. It doesn't work.

That way they won't, but they can be used if the zero value is replaced with Null.

EG :

Expression: Count(IIf(([tbl].[field1]<#1/1/2013#) And ([tbl].[field2] Is Null),1,Null))

Of course, the Sum() version will work and may be easier to grasp :

Expression: Sum(IIf(([tbl].[field1]<#1/1/2013#) And ([tbl].[field2] Is Null),1,0))

[twinnyfo]

@NeoPa,

This may be slightly off-topic, but I think it is related. You said:

Domain Aggregate functions (DCount()) would be relatively inefficient as they have to enquire of the data separately, whereas Aggregate functions (Count(); Sum(); etc) perform within the context of the existing query so are more efficient. Also bear in mind that any use of a Domain Aggregate function would also need to reflect the current grouping in the filter. Your posted suggestion would count all of the records in the whole table rather than just those appropriate within the current GROUP BY group.

Please explain, so that I understand, because it appears that the DCount, Sum and Count all appear (in this case) to look at all records and evaluate according to the same criteria.

The reason I ask is obviously because I wish to have a more efficient DB, and I use DCount numerous times in finding the number of records that meet certain criteria. If I have better, more efficient ways to do this, then, obviously I wish to do that. However, I also wish to understand why one way is more efficient, when, on the surface, they appear identical but use different language.

Thanks!

[Rabbit]

Domain aggregate functions such as DCount and DSum have extra overhead because they call code for each row (assuming the function call references a field in the recordset).

Whereas Count() and Sum() work with the set of data just the one time.

For example:

SELECT country, count(*)
FROM tblPopulation
GROUP BY country

SELECT country, DCOUNT("*", "tblPopulation", "country = '" country & "'")
FROM tblPopulation
GROUP BY country

Assuming there are 10 distinct countries, the DCount version calls the code 10 times. Each time it does a separate count of the data. Basically, it's running 10 separate queries to provide the counts.

The Count version pulls the data once and does the counts.

However, if the domain aggregate function does not reference a field in the recordset, the overhead is negligible because the optimizer runs the function once.

For example:

SELECT a.country, b.allCount
FROM tblPopulation a, (
   SELECT COUNT(*) AS allCount
   FROM tblPopulation
) b

SELECT a.country, DCount("*", "tblPopulation")
FROM tblPopulation

There will be almost no performance difference between the first version and the second. However, I still recommend the first version because of consistency and portability. The first version can be ported more easily if the database is moved to another DBMS.

[twinnyfo]

Rabbit,

As usual, you explained well to a young Jedi. Some day I may fully understand all the secrets of the dark side of the Force.

Thanks for the excellent explanation!

[Rabbit]

No problem twinny, glad to be of help.

[NeoPa]

Rabbit has provided illustrations that range from the most, to the absolute least, effect of using Domain Aggregate instead of Aggregate functions, and rightly indicates that even in such a rare case as when aggregating across the full domain for a domain which isn't otherwised reference within the SQL, it is still preferable to use the latter instead of the former.

His first example illustrates that the Domain Aggregate function is not equivalent to an Aggregate one where a GROUP BY is used (He has criteria in the DCount() that specifies which group of data within the domain to process through). Even if no GROUP BY is used (IE A single output record is required) then DCount() still ensures the domain is processed through twice instead of only once, and because it comes via a completely separate function call it cannot be included in any optimisations determined by pre-processing the SQL (Such as which indexes to use etc).

PS Twinnyfo - we prefer to think of ourselves as on the light side of The Force if you don't mind ;-) We'll leave the secrets of the dark side to others.