Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count values in report (Access XP)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count values in report (Access XP)

    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).

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count values in report (Access XP)

    It works! Not what I'd call an elegant formula since I have 21 grade fields <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •