IIf statement vs Select Case statement

[PaulDenver]

HI, Please help.

I am working on a project with over 200,000 counts. Someone suggests I use
Access but I don’t know much about Access. The data I work on are all the
clinic visits of all the patients during the 18 month period. The patients’
age ranged from birth to >85 yo.

I like to group age into 22 age groups (0, 1, 2, 3, 4, 5-9, 10-14, 15-19, 20-
24, 25-29, 30-34, 35-39, 40-44, 45-49, 50-54, 55-59, 60-64, 65-69, 70-74, 75-
79, 80-84, 85+). I use IIF in the query to group the age but Access doesn’t
let me use 22 IIF statements. How should I do this?


This is the format of the original database that I am working on. which I
can import into Access to create 22 age groups as I mentioned above. My
problem is I can not use more than 7 IIF statements. Someone suggested me to
use Select Case statement but I don’t know how to use it. Because I know
very little Access please help me STEP BY STEP (ie, if I need to use query,
tell me which type – select query, update query,…; or, in a query design
window where should I enter the Select..Case statement – ‘field’ field,
‘criteria’ filed, so on). The Age here ranges from age 0 (less
than1) to age >85.

Count med_rec_nbr sex age
1 178061 F 46
2 178061 F 46
3 178524 M 6
4 249641 M 22



This is the final information I would like to get: (Note:TotVisit# is larger
than TotPatient# since each patient can have more than1 clinic visit in the
18-month period as you can see in the table above)


MALE FEMALE
Agegrp TotPatient# TotVisit# TotPatient# TotVist#
0 532 678 452 564
1 652 852 632 752
2 .. .. .. ..
..
5-9 .. .. .. ..
10-14 .. .. .. ..
..
80-84 .. .. .. ..
85+ .. .. .. ..

Thanks.

 

[Steve Schapel]

Paul,

Make a new table in your database to define the age groups. Something
like this...

Group GroupName StartValue EndValue
1 0 0 0
2 1 1 1
....
6 5-9 5 9
....
21 80-84 80 84
22 85+ 85 120

Let's say you name this table AgeGroups

Then make a query that includes your existing table, plus this new table.

Add the required fields to the query design grid, and in the Criteria of
the Age field, enter...
Between [AgeGroups].[StartValue] And [AgeGroups].[EndValue]

Then you can apply your grouping and totalling to this query.