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 extension.
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 width.
All of the datafields are one character except for fields:

 Here is a video on importing and parsing 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.
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 data formats.

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 the same.
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.

Using Winzip:

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 1MB.
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 as above.

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

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

  1. What is the null hypothesis?
  2. Compare Chi²  obs to Chi2crit and Pvalue to alpha (5%) .What do you conclude about the relationship? How confident are you in your conclusions?
  3. From this, can you conclude Marijuana makes people happy? Can you conclude Marijuana makes people sad?