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.

 

[Guest]

The Select Case statement is a VBA construct so does not go in the query, but
in a VBA function which the query calls. Don't confuse it with the CASE
expression of standard SQL, which can be used in queries, but is not
supported by Access.

To create a function called AgeGroup go to the VBA window by pressing Ctrl+G
and from the Insert menu select Module. This will open a new module into
which you can insert your function. With this new module open select
Procedure from the Insert menu. In the dialogue enter AgeGroup (with no
space) as the Name and select the Function and Public options. Click the OK
button.

The new function will now appear with two lines in place:

Public Function AgeGroup()

End Function

The first thing you do is type in an argument for the function in the
parentheses so it accepts the age value. You also set the function's data
type as String:

Public Function AgeGroup(intAge As integer) As String

End Function

Then you can enter the body code of the function using the Select Case
statement so that the return value of the function is set to the correct
group for the age in question:

Public Function AgeGroup(intAge As Integer) As String

Select Case intAge
Case 0
AgeGroup = "0"
Case 1
AgeGroup = "1"
Case 2
AgeGroup = "2"
Case 3
AgeGroup = "3"
Case 4
AgeGroup = "4"
Case 5 To 9
AgeGroup = "5-9"
Case 10 To 14
AgeGroup = "10-14"
Case 15 To 19
AgeGroup = "15-19"
Case 20 To 24
AgeGroup = "20-24"
Case 25 To 29
AgeGroup = "25-29"
Case 30 To 34
AgeGroup = "30-34"
Case 35 To 39
AgeGroup = "35-39"
Case 40 To 44
AgeGroup = "40-44"
Case 45 To 49
AgeGroup = "45-49"
Case 50 To 54
AgeGroup = "50-54"
Case 55 To 59
AgeGroup = "55-59"
Case 60 To 64
AgeGroup = "60-64"
Case 65 To 69
AgeGroup = "65-69"
Case 70 To 74
AgeGroup = "70-74"
Case 75 To 79
AgeGroup = "75-79"
Case 80 To 84
AgeGroup = "80-84"
Case Else
AgeGroup = "85+"
End Select

End Function

Unfortunately you haven't told us what your table and field names are, so
I'm going to assume you have a table patients with fields PatientID, Sex and
Age amongst others and a table Visits with a foreign key field PatientID
For the sake of this example the other fields in these tables are immaterial.
I'm afraid it now gets a little tricky as you can't use a simple aggregating
query which joins both tables as the number of rows returned would be the
total number of visits, so in Access you can't count individual patients this
way (unlike in standard SQL which includes a COUNT DISTINCT operator). You
have to use a query on the Patients table and subqueries on the Visits table
joined to the Patients table. This has to be done in SQL view:

SELECT
AgeGroup(Age) As [Age Group],
SUM(IIF(Sex = "M",1,0)) AS [Male Patient Count],
(SELECT COUNT(*)
FROM Visits INNER JOIN Patients As P2
ON Visits.PatientID = P2.PatientID
WHERE AgeGroup(P2.Age) = AgeGroup(P1.Age)
AND Sex = "M") As [Male Visit Count],
SUM(IIF(Sex = "F",1,0)) AS [Female Patient Count],
(SELECT COUNT(*)
FROM Visits INNER JOIN Patients As P3
ON Visits.PatientID = P3.PatientID
WHERE AgeGroup(P3.Age) = AgeGroup(P1.Age)
AND Sex = "F") As [Female Visit Count]
FROM Patients As P1
GROUP BY AgeGroup(Age);

Another approach, and probably a better one in a relational database would
be to have an AgeGroups table with columns Age and AgeGroup and say 121 rows
with Age values from 0 to 120 and the corresponding AgeGroup values for each
age in the AgeGroup column. You could then JOIN this to patients table on
the Age columns in queries to get the Age Group. I'm afraid this would not
make a query to get the results you want any simpler however.

BTW rather than storing each patient's age in the Patients table it would be
better to store their date of birth. Their age either currently or at the
time of each visit to the clinic could be easily computed from their DoB and
the current date, as returned by the Date function, or a VisitDate field in
the Visits table to get the age at the time of the visit.

Ken Sheridan
Stafford, England

 

[PaulDenver via AccessMonster.com]

Hi Ken

Really thank you for the answer. To make the story more complete, I have only
one database of 200,000+ counts for the 18-month period with the original
format as below:

Count Med rec # Date of birth Gender Clinic
1 12531 12/5/2000 M A
2 24541 9/5/1996 F B
3 42512 6/10/1960 M C
4 42512 6/10/1960 M C

And this is what I want to obtain:

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+ .. .. .. ..

So what I need to do is
-to calculate the age from the DOB which I am able to do it in Access (or
in Excell)
-after age is calculated, I want to group them into 22 age groups.
-then determine TotPatient# and TotVisit# ( made by these patients during
this 18-month period) for Male, and for Female based on the medical record
number. TotVisit# is larger than TotPatient# because one patient can have
more than 1 clinic visit during this period. (this is shown by the same med
record number as you see on the count 3 and 4 above)

What I did so far:
- import the original database into Access as Sheet1 table and made a
column with field name Age,
- do a query to calculate the age from DOB -. Now the Sheet1 table have
Age column with age in decimal.
- create a UniqueRecordTable to use to determine the TotPatient# for each
Agegroup once I figure it out (this is to make sure only one med record
number is counted once).
- do a BuildAgeGrpTable query (using table Sheet1) to try to group age
into 22 age groups…..I tried for 7 IIF functions and it worked fine and a new
table AgeGrpTable is created. But when I enter the rest, Access wouln’d let
me. (I try to do in Excell but again it also only allow 7 IF functions!!....
).
- to overcome the 7 IIF functions, someone suggested me to do an Update query
using the AgeGrpTable and enter the next 7 IIF functions in the ‘update to’
field of the query. And after that do another Update query for the remaining
IIF functions to cover all age groups……But when I ran it, I got a ‘Enter
Parameter Valus’ window with the dialogue box and the word on top of the box
‘Sheet1 !age’ . And when I hit OK without enter anything in the dialogue box,
the query ran but the result was “null†for all of the records (I used “nullâ€
for the False Part of the IIF statement)

So, This is where I am. I hope you could help me. If you want to I can attach
the Access file I am working and email you. so you know how I did.

Thanks

 

[Jamie Collins]

The Select Case statement is a VBA construct so does not go in the query

The Jet SQL equivalent is SWITCH e.g.

SELECT age_value, SWITCH(
age_value BETWEEN 0 AND 4, CSTR(age_value),
age_value BETWEEN 5 AND 84, CSTR(age_value - (age_value MOD 5))
& '-' & CSTR(age_value - (age_value MOD 5) + 4),
age_value >= 85, '85+',
TRUE, '{{ERROR}}') AS age_group
FROM Patients;

Jamie.

--

 

[Guest]

Firstly, you are confusing a database and a table; the former is the
application, and normally contains a number of tables. Your problem is that
the single table is not adequately normalized. It contains redundancies,
e.g. we are told that patient 42512 was born on 6 October 1960 and that he
is Male twice. The table should really be decomposed into Patients and
ClinicVisits tables, and moreover the latter should reference a Clinics table.

Because you have multiple rows per patient you cannot use the COUNT operator
to count them as this would count visits per patient. You can count the
visits by gender by summing the return values of an expression which returns
0 or 1 depending on gender, which is what the first example I sent you did.

To compute the age from the date of birth you can add a simple function like
this to a module:

Public Function GetAge(varDob)

If Not IsNull(varDob) Then
GetAge = DateDiff("yyyy", varDob, Date)
If Date < DateSerial(Year(Date), Month(varDob), Day(varDob)) Then
GetAge = GetAge - 1
End If
End If

End Function

You can call this in a query once you've added it to a module in your
database by using my AgeGroup function with a call to the GetAge function as
its argument, by using the VBA Switch function as described by Jamie, or best
of all, by a set operation using an AgeGroups table as I described in my last
post. Lets stick with my crude and inelegant AgeGroup function for the
moment.

With your current single table the problem you have to overcome is how to
get a distinct count of the patients. There are a variety of ways this could
be done; by creating a sparate query which returns distinct rows per patient
and including this in a subquery in the outer query, by iterating through a
recordset in code and counting the distinct values or, as I've done belw by
using subqueries with the DISTINCT option in subqueries which count the rows
for each age group

SELECT AgeGroup(GetAge([Date of Birth])) AS [Age Group],
(SELECT COUNT(*)
FROM (SELECT DISTINCT [Med Rec #],[Date of Birth]
FROM Patients
WHERE Gender = "M") AS P2
WHERE AgeGroup(GetAge(P2.[Date of Birth]))
=AgeGroup(GetAge(P1.[Date of Birth])))
AS [TotMalePatient#],
SUM(IIF(Gender="M",1,0)) AS [TotMaleVisit#],
(SELECT COUNT(*)
FROM (SELECT DISTINCT [Med Rec #],[Date of Birth]
FROM Patients
WHERE Gender = "F") AS P3
WHERE AgeGroup(GetAge(P3.[Date of Birth]))
=AgeGroup(GetAge(P1.[Date of Birth])))
AS [TotFemalePatient#],
SUM(IIF(Gender="F",1,0)) AS [TotFemaleVisit#]
FROM Patients AS P1
GROUP BY AgeGroup(GetAge([Date of Birth]))
ORDER BY VAL(AgeGroup(GetAge([Date of Birth])));

Just substitute the actual name of your table for Patients in the above and
paste it into a query in SQL view and it should work, but I wouldn't like to
say what performance would be like with a large table. One thing you should
note is that the age groups will all be based on the age of the patients when
you run the query, not at the time of their visits to the clinic as the date
of each visit does not appear in your table. If you had the clinic dates you
could amend the GetAge function to take a second optional parameter so it
returns the age at the clinic date if a second argument is passed into it, or
at the current date if its omitted:

Public Function GetAge(varDob, Optional varDateAt)

If IsMissing(varDateAt) Then varDateAt = Date

If Not IsNull(varDob) Then
GetAge = DateDiff("yyyy", varDob, varDateAt)
If varDateAt < DateSerial(Year(varDateAt), Month(varDob),
Day(varDob)) Then
GetAge = GetAge - 1
End If
End If

End Function

and pass the clinic date into it each time in the query, e.g.

GetAge([Date of Birth], [Clinic Date])

Forget about update queries. Using the Switch function as Jamie described
would get round thecproblem with the IIf function. The real problem is in
the inadequately normalized data, but you can overcome that in the query as
I've shown above. But be aware that this is a workaround, not a substitute
for the normalization of the table by decomposing it. Also the creation of
an AgeGroups table, which would allow you to get the age groups by joining
the tables would be a far better approach than hard coding the computation of
the age groups, whether in VBA or SQL. Either of those ways in fact
contravenes one of the fundamental principles of the database relational
model, which requires data to be stored as values at column positions in
tables and in no other way. The age groups are data, so should be stored in
an AgeGroups table to satisfy this requirement of the model.

Good luck,

Ken Sheridan
Stafford, England