Results 1 to 4 of 4
  1. #1
    carletonw
    Guest

    Counting Criteria in Report (Access 2000/SR1)

    I am trying to count groups of test score data in a report. I have four ranges of data I want counted. Numbers from 0-25, 26-50, 51-75, 76-99. I have a test year field I am grouping on. I have tried using < Less than or > Greater than in various count functions in the un-bound text field. The latest I've used is: =IIf([Sat9English]>75<100,Count([Sat9English]),0) for the last range 76-100. I cant' get it to work. Any help would be great.

    Thanks
    Carleton W

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Criteria in Report (Access 2000/SR1)

    Use two queries.

    In the first query create an expression along the lines of Abs([Sat9English]>75 and [Sat9English]<100). That will give you a 1 or zero value for each record. Create a similar expression for each grouping you wanted to count.

    Then create a second query based on the first and make the second one a group by query. Now you can use the Sum aggregate function to total each expression, and that will give you the count.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Criteria in Report (Access 2000/SR1)

    Or, check out my new favorite hidden function: Partition(). You can use it to group your scores into ranges that you define, then count the number of occurences in those ranges. It's a great function, check it out in Help.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    carletonw
    Guest

    Re: Counting Criteria in Report (Access 2000/SR1)

    Thanks Charlotte it worked perfectly. It was so simple it was brilliant....

    Carleton

Posting Permissions

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