Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    11

    Unanswered: ouput counts in ranges

    I think what i want is a crosstab query type result.....i already have one that outputs the # of enrollees broken up by month and site. Now what i want is for it to give me the number of people who fall into a certain range of scores for a variable. When i tried a cross tab query it gave me the counts for every single score--this is too much. So instead of having 1 person with each score--i want the # of people who had scores 0-9 and 10 or greater. Any ideas?
    Something like this:

    [Month] [Year] [TotalEnrollees] [Scores0-9] [Scores10andGreater]
    12 2004 22 3 15
    01 2005 30 6 10

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    You can do this, you just have to put an IIf statement that groups your score ranges in the Column Heading field of the crosstab query. Paste this SQL into the SQL View of a blank query and replace "tablename" and any fields with the appropriate names. It should do what you want.

    TRANSFORM Sum(tablename.Enrollees) AS SumOfEnrollees
    SELECT tablename.Month, tablename.Year, Sum(tablename.Enrollees) AS TotalEnrollees
    FROM tablename
    GROUP BY tablename.Month, tablename.Year
    PIVOT IIf([Score]<=9,"Scores0-9","Scores10andGreater");

Posting Permissions

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