Count values

[EllenM]

Hello,
I have a field, [rot-ofas-html-june19-08]![KEEP/REMOVE] , that can have one
of four values, "keep", "remove", "redirect, or blank. I'd like a count of
each in a form.

Thanks,
Ellen

 

[Arvin Meyer [MVP]]

Add 4 columns in a query:

CountKeep: Sum(IIf([rot-ofas-html-june19-08]![KEEP/REMOVE]="keep",1,0))
CountRemove: Sum(IIf([rot-ofas-html-june19-08]![KEEP/REMOVE]="remove",1,0))
CountRedirect:
Sum(IIf([rot-ofas-html-june19-08]![KEEP/REMOVE]="redirect",1,0))
CountBlank: Sum(IIf([rot-ofas-html-june19-08]![KEEP/REMOVE]="",1,0))

 

[EllenM]

Thanks so much, Arvin. Everything worked perfectly except count of the
blanks. Apparently this is quite challenging for Access 2003.

Ellen

 

[Arvin Meyer [MVP]]

The blanks may be nulls instead of empty strings. Try:

CountBlank: Sum(IIf(IsNull([rot-ofas-html-june19-08]![KEEP/REMOVE]),1,0))

 

[EllenM]

Yes!!!! It works perfectly!!
Thanks, Arvin

 

[EllenM]

One more question, Arvin. I have my blanks, keeps, removes, etc in three
tables and have prepared three queries as you described of each table. How
do I a grand total of the blanks, keeps, removes of all three tables?

Thanks,
Ellen

 

[EllenM]

Hi Arvin,
I summed the values in another query for the grand total.

Everything's working. thanks!!