Conditional count

[Access9001]

I want to return everything having count(table.somefield="Some criteria") = 0 but I don't know the syntax. Thanks!

 

[Brianwarnock]

Ithink it will have to be like this

Field Countofsomefield: sum(iif(somefield=criteria,1,0)

Criteria =0

Brian

 

[MSAccessRookie]

I want to return everything having count(table.somefield="Some criteria") = 0 but I don't know the syntax. Thanks!

Do you mean something like the following?

SELECT YourTable.somefield1, YourTable.somefield2, YourTable.somefield3
FROM YourTable
WHERE YourTable.somefield="Some criteria"
GROUP BY YourTable.somefield
HAVING COUNT(*)=0

 

[jay123]

Could you also use DCount?

=DCount("Fieldname","QueryName","CriteriaField=something")

so for example:

=DCount("RecordID","OrdersT","CompanyName=ABC")

this would count all records in the orders table for the company called ABC.

 

[Access9001]

I wound up using two queries -- it was a huge headache trying to get it to work right with one. Thanks!