Massachusetts Institute of Technology
Department of Urban Studies and Planning

11.220 Quantitative Reasoning and Statistical Methods for Planning

MS-Access

HOME   |   TEST-OUT   |   BRUSH-UP   |   MATERIALS

MS-Access > Manipulation

1. We will now create a dummy variable for gender using the 'iif' function. This will create a new column called "male" whose value is 1 if the person is male or 0 otherwise.

With "qr_sample_p" still open and while in "Query Design View" scroll to the right of the data table and right-click in the first empty column. Click the "Build" tool, then type "male: IIf([sex]=1,1,0)" in the Expression Builder window. Don't include any quotes. You can even copy and paste the statement from this document. When you are finished click "OK."

2. Check the result. You see the iif statement in the column heading. Now right-click on the title bar of the query data table window and switch to "Datasheet View." Notice that the column heading is "male."

3. Now we'll create a "educ_yr" variable using the "switch" function. The "educ" variable contains data about educational attainment using the following coding system.

0 . Not in universe (Under 3 years)
1 . No schooling completed
2 . Nursery school to 4th grade
3 . 5th grade or 6th grade
4 . 7th grade or 8th grade
5 . 9th grade
6 . 10th grade
7 . 11th grade
8 . 12th grade, no diploma
9 . High school graduate
10 . Some college, but less than 1 year
11 . One or more years of college, no degree
12 . Associate degree
13 . Bachelor’s degree
14 . Master’s degree
15 . Professional degree
16 . Doctorate degree

We'd like to re-code this data as years of education. Again, while in Query Design view, right-click in the first blank column and choose "Build." Type in the following expression in the Expression Builder (actually, it would be better to copy and paste this one!).

educ_yr: IIf([educ]>3,Val(Switch([educ]=4,7,[educ]=5,8,[educ]=6,9,[educ]=7,10,[educ]=8,11,[educ]=9,12,[educ]=10,13,[educ]=11,14,[educ]=12,15,[educ]=13,16,[educ]=14,18,[educ]=15,19,[educ]=16,21)),Null)

4. Go to Datasheet View and check the result.

Can you see how the "Switch" commands works? In English the commands says, "For every record where educ is greater than 3, recode all 4s as 7s, all 5s as 8s, etc." So for people who only completed the eleventh grade, how many years of schooling is recorded in the new "years of education" field?

5. Create one more field for that will recode income into hierarchical categories. The "Null" value means leave the cell blank if the incws field for that record was originally blank.

incws_c: IIf([incws]>0,Val(Switch([incws]<20000,1,[incws]<40000,2,[incws]>=40000,3)),Null)

6. Close the "qr_sample_p" query (click "X" in the upper right-hand corner of the query window) and say "Yes" to save it.

Created by Myounggu Kang on January 25, 2004. Edited by Rhonda Ryznar on January 19, 2005.