Report/Query Counting Null and Non_Null fields in a Group

[SealM]

I think this should be pretty easy, but it's eluding me. I'm creating a report that runs off of one table. The data comes from the "create e-mail" and polling feature in Access. I've been working on this database for a long time, and now I'm trying to make a silly little report to check the STATUS of replies as they are processed by Access.

The jist of the report needs to show how many records have something in them, vs. how many do not, for each person. Here's an example table and report:



The report for this table should be as follows:
john has 3 of 4
mary has 1 of 2
fred has 0 of 1

There may have to be multiple queries, but I can't get a COUNT query to work that will count NULLS, or even a COUNT query that will count both NULL and NON-NULL fields. Thanks in advance for the help!

Mike

table: RAW

person	reply
john	yes
john	yes
john	no
john
mary
mary	maybe
fred

[June7]

Don't do it in query. Do calcs in report textboxes. Use report Grouping & Sorting functionality with aggregate calcs in group and report footers. Create a grouping on Person then calcs in group footer:

=Count(*)
=Sum(IIf(IsNull([reply]),1,0))
=Sum(IIf(Not IsNull([reply]),1,0))

[SealM]

Brilliant! Thanks again, June7. You've come through for me once more. It's a simple solution, and I can implement it now, since my skills in Access have been growing since I started my project. Six months ago I wouldn't have a clue what to do with your post. =)

Anyway, I was working with anothe solution, which was just to make a cross-tab query on answers and people. It came up with the same information that I could port right into a report. I think I will go with your solution and see how much flexibility that gives me in formatting the report. Thanks for the quick response!

[June7]

Building report on crosstab is tricky because can be so dynamic. If the crosstab doesn't produce the same fields every time then the bound textboxes will show errors.