multiple betweens in one query

[kkmcnett]

All -
I have a table that contains a field with numbers. I am trying to write a query that will provide a counts of these numbers from 1-9, 10-19, 20-29, etc. I would like these totals to appear in successive columns within one query.

Do I need a crosstab query for this?? I have tried using a select query with no luck. Any help would be appreciated!!

Kurt M.
Access newbie

 

[rac2]

There may be several ways to meet this requirement. Here are some ideas.

Add calculated columns to your table or make a new view with calculated columns using IFF(expression, value for true, value for false).

Name the new view, myNewView, and include the column that is the thing being measured, say the name of that column in the basic table is theThingMeasured.

Here are the expressions for the calculated columns.

Ones: IIF(theColumnName BETWEEN 1 AND 9, 1, 0)
Teens: IIF(theColumnName BETWEEN 10 AND 19, 1, 0)
Twenties: IIF(theColumnName BETWEEN 20 AND 29, 1, 0)
OrMore: IIF(theColumnName > 29, 1, 0)


Then make a query to summarize the caculated columns using SUM().

SELECT theThingMeasured,
       SUM(Ones)     AS "Ones",
       SUM(Teens)    AS "Teens",
       SUM(Twenties) AS "Twenties",
       SUM(OrMore)   AS "More"
FROM myNewView
GROUP BY theThingMeasured

 

[Mikey2000]

You can also try this if you only want one query:

Click the Totals button (funny looking 'E')

Then for column one type:
1-9: IIf([fieldname] between 1 and 19,1,0)

Column two:
10-19: IIF([fieldname] between 10 and 19,1,0)

Column three:
20-29: IIf([fieldname] between 20 and 29,1,0)

Finally, select "Sum" for the Totals option for each field.

If you have no idea what I'm talking about, you can also try pasting this code in the SQL view in an empty query:

SELECT Sum(IIf([fieldname] Between 1 And 9,1,0)) AS [1-9], Sum(IIf([fieldname] Between 10 And 19,1,0)) AS [10-19], Sum(IIf([fieldname] Between 20 And 29,1,0)) AS [20-29];