count values in report (Access XP)

Topic

New Reply

I’m converting a large, flat table to related tables. To double-check my work, I was going to run a report from the flat table but am having problems. Here’s what I have:

1. One entry per student that could have up to 21 grades
2. I’ve completed a query that sorts by social security number
3. I’ve created a report based on that query and am grouping by social security number
4. There are 21 columns in the detail section of this report
5. I want a count of the number of grades each student has
6. So, I need a way to test to see if the grade (column) is not null, then add it to the count

Any guidance is greatly appreciated!

Kathy

Reply | Quote

Replies

Since you are grouping by social security number, you can have a group footer for this field – set the group footer property to Yes in the Sorting and Grouping window. Put a text box in the group footer and set the Control Source property to
=Count([Grade])
where Grade should be replaced by the actual name of the grade field. The Count function only counts non-null values; when placed in a group footer, it counts the non-null values within the group.

Note: Count(*) counts *all* records (within a group).

Reply | Quote

It works! Not what I’d call an elegant formula since I have 21 grade fields but it works. It didn’t cross my mind that Count would ignore nulls… I’ll have to watch my assumptions. Very nice to know about Count(*).

Thanks so much,
Kathy

Reply | Quote