Exercise 5: Use of ACCESS queries to "predigest" data for
analysis in EXCEL--and Chi2 analysis
Download YRBS2001.dat. (You can change the extension to .txt at this point
if you want) Since this is almost 2 MB, you won't be able to put it on
a floppy disk. The file can be zipped with Winzip to make it smaller if
you want to save it on a floppy disk for some reason.
CDC Youth Risk Behavior Surveillance System (YRBSS) database http://www.cdc.gov/nccdphp/dash/yrbs/
CDC Home http://www.cdc.gov
If you didn't change the file extension during the download, use Windows
Explorer to change the name of the file to YRBS2001.dat to YRBS2001.txt.
This name change is necessary because ACCESS doesn't recognize the .dat
Open ACCESS and create a new database file on the hard drive. You can
name this anything you want though it's safer to use a name with 8 characters
or less. ACCESS will automatically add the extension .mdb.
Import the YRBSS data by file/ get external data / import. Be careful
to import, not just link! The resulting .mdb file will be almost 7 MB after
the data is imported.
To find the txt file to import, change file type to text so you can
see the yrbs2001.txt.
When you double click on the yrbs2001.txt file, the import wizard will
open. This is almost exactly like the one in EXCEL. Parse the data as fixed
All of the datafields are one character except for fields:
Here is a video on importing and parsing
part 1 and parsing
part 2 The procedures are the same for the 2001 data but the
questions are slightly different, so you should refer to the screen
captures for parsing the 2001 data.
5 height 5-12
6 weight 13-20
96 statistical weight factor 110-117
97 PSU 118-123
98 Stratum 124-126
Let the data be placed in a new table. You can rename fields and change
data specifications at this stage, but it may be just as well to continue
without doing this and do it later if you want.
The next step is to allow ACCESS to set up a primary key field so that
each record has a unique identifier. Allow the name to be set to yrbs2001
and click finish.
In earlier versions of ACCESS (to access 2000), there will be a series
of import errors reported for records that have decimal places but no numbers
in fields 5 and 6 (missing data) You can safely ignore the error messages
and the table that lists them. The offending records are deleted.
In Access XP, the fields are imported but fields 5, 6 and 8 are brought
in as text format. Before doing any calculations on those, they must be
changed to number format -- single
precision is adequate. Be careful not to accept the default of integer
or your values will be rounded and everybody will be the same height!
Here is a video on changing
the format of data fields.
You can view the imported data by double clicking on the table yrbs2001.
Right clicking at the top of the columns will reveal a number of changes
you can make such as sorting, renaming fields. see the video on ACCESS
If you want, you can change height, weight, age and sex variables to
be more understandable using the update
query procedure described at the BA103 website. Here is a video
on update queries.
Crosstab Query wizard:
Click on queries / new query and select the crosstab query wizard. Select
the yrbs2001 table as input data. Click next and select the variable you
want in the rows. Use the booklet as a guide.
For the MJHappy study, this will be field 47, "During the last 30 days,
how many times did you use marijuana?" click the single arrow to the right
to put this variable in the selected fields.
Next, select the variable to be broken down in columns. In this case,
select field 23, in which the respondent revealed incidents of debilitating
sadness. This is a classic measure of clinical depression.
In the next screen you indicate the function to be calculated at intersections.
In this case, we'll just use count.
You can uncheck the row sums if you want as we will be calculating
these anyway after we paste the data into EXCEL.
It's a good idea to put a more descriptive name on the query in the
next screen, such as "sadbymj".
Click finish and view the query. The results will appear as a table.
see the video on crosstab.exe
This video includes its own viewer, so must be downloaded, then played
by executing the file. Note this video was produced with the 1999 data,
so the fields shown will be slightly different, but the procedures are
Clicking in the upper left corner of the table margins will select
the whole table. Copy the table, open EXCEL and paste it into EXCEL to
analyze by the ChiSquared procedure.
(There is an export function in ACCESS to export data into EXCEL, but
it doesn't work right. Empty cells can result in data shifts so that data
ends up in the wrong cells. thus you are better off to just copy and paste.)
Saving the database for later exploration:
There are a few things to consider in working with ACCESS files. ACCESS
won't allow you to do anything with a file as long as it is in use so you
have to close the database before making a copy. ALL the changes in the
database, including queries, reports and all tables are all saved in one
file, the .MDB file. Changes in the data are all saved as they are made,
so there is no undo or opportunity to recover if you do things like accidentally
delete something you need.
Close the database, open Windows Explorer to see the size of the file.
Note the size is about 7 MB.
You can save this file to your network drive, or you can compress it
with Winzip to fit on a floppy disk.
With Windows Explorer open to the file location, open Winzip. set the explorer
and the winzip windows to partial screen so you can drag the file into
winzip from the explorer window.
When you do that, Winzip allows you to specify a new zip file. Click
on new, enter a name for the archive file, click okay, then click add.
The new zipped archive will be created in the indicated folder.
In Windows Explorer, you will see the new .zip file, with a size around
You can save this on a floppy disk by using Explorer to copy it. Don't
try to put it on a floppy disk by dragging it out of Winzip. Dragging out
of Winzip to a disk location is how you extract the original file, and
dragging out of Winzip to A: would result in attempting to write an 8MB
file to the 1.44MB floppy disk. This is called a "blivet".
It doesn't work.
Regenerating the database:
Later, if you want to study other relationships in these data, you can
regeenerate the database from the zip file. Copy the zip file over to a
hard drive so there is more room, open Windows Explorer to show the zip
file. Open Winzip and drag the file into the Winzip Window. Winzip will
reveal data about the archive.
drag the file back out of Winzip to the location where you want to
put the extracted database file.
You can then open ACCESS, open the database file and conduct new queries
Other things you might want to look at:
Other crosstab queries that might be interesting:
seat belt use (10) and smoking(3); smoking(35) and Geographical Region
(99), age (1) and grade(3).
You can also do simple queries to find averages for one characteristic
as a function of another. For example, you might want to find average weight
as a function of smoking. To do a simple query, select new query/ simple
query wizard. Select the table to use, then select the field you want to
use for classification, and the field result you want to report. Click
the arrow right to put the variables in the selection list. On the next
screen you can click summary and summary options. Select the type of summary
you want such as average, and check count records so you know how many
values were in each category to go into each average. Click finish and
the resulting table will give averages of one variable calculated for each
class of the other. This isn't as useful as a t-test but may give you a
quick idea where to look.
Analysis of the MJHappy crosstab results in EXCEL:
Once the data is in EXCEL, you will want to relabel rows and columns to
be more descriptive, and delete the row and column that correspond to "no
response". Those records are only a few percent of the total and for our
purposes, we can tolerate a little inaccuracy to spare the complexity of
Chi Square analysis-- Does smoking Marijuana make you
happy? Contingency table on Marijuana per month and debilitating depression.
These are real count data from the yrbs2001, from crosstabulating questions
47 (amount of Marijuana in the last 30 days) and 23 (sadness to the point
of neglecting normal activities). Nonrespondents (less than 5%) were left
out to simplify the table.
Replace the last digit in each of the first four rows in the Yes column
with the last 4 digits of your student number.
Calculate totals for rows and columns of the observed values. Set up
tables next to the observed table and next to the calculated expected frequency
table in which you show the percents of counts related to total count for
each column. I think this will clarify what we are doing in the test and
help in the practical interpretation. Calculate Chi²
, Critical Chi² and Pvalue for the observed
result. If you use a sheet designed with flexible formulas for copying
as we did in class for the pregant women smoking and drinking data, this
will be about a 5-minute job. Use 5% alpha to test independence of amount
of Marijuana smoking and sadness. Compare the EXCEL Chitest result to the
Chidist result for the calculated observed Chi²
value (these should be the same and so is a check on your calculations).
What is the null hypothesis?
Compare Chi² obs to Chi2crit and Pvalue
to alpha (5%) .What do you conclude about the relationship? How confident
are you in your conclusions?
From this, can you conclude Marijuana makes people happy? Can you conclude
Marijuana makes people sad?