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:
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
GROUP BY tablename.Month, tablename.Year