BA 1800 Access Exercise

This exercise will build your facility with large database manipulations.

The total of 60 points will be assigned as follows:

10 points---Search and email a description and link to data on the web suitable for analysis in Access..See webpage for specs
20 points---Import and Action queries to transform data from the YRBSS01 dataset screen capture printed from WORD with your name someplace in the screen capture.
30 points---printed reports from at least two crosstabulation queries from the data set of your choice with your name printed in the report.

YRBSS 2001 data

CDC Youth Risk Behavior Surveillance System (YRBSS) database http://www.cdc.gov/nccdphp/dash/yrbs/

Download the 2001 YRBSS data. Import the columns you need into ACCESS.Use the import wizard for fixed length fields. Refer to the YRBSS booklet for data positions. Here is a video made using the 1999 data 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.

You will need to adjust the data in the columns shown for:

age (Q1) (add 11 to the response code)
sex (Q2) (subtract 1 to make this 0=female, 1=male)
grade (Q3) (add 8 to make this the actual grade --13 is "ungraded")
height (Q5) (meters*100/2.54=inches)
weight(Q6) (Kg*2.2=lbs.)

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.

Saving your work:

You can either not import columns you don't need or delete them early. This may make a database small enough to save on an empty floppy disk.The full database is too big to save on a floppy disk. You can save it to your UMSL virtual drive or the webserver space via FTP or else use winZIP to compress the file to save it to a floppy. WinZIP is in the menu on the school system if you can't get it on a right click in Windows explorer. You can download shareware WinZIP from the database webpage or from cnet.com to put it on your own computer if you want. See the discussion on using FTP and Winzip.

Action Queries--video:

Convert height to inches; weight to pounds; grade to actual; the age response to the corresponding ages; the sex response to 0 for female, 1 for male. The conversions can be accomplished by update queries (new form/query in design mode then query type/update) with the appropriate formulas:
[age]+11
[sex]-1
[grade] + 8
[height]*100/2.54
[weight]*2.2
note that you put these expressions, with the square brackets around field names in the "update to" row that appears when you change the query type to "update". These expressions assume you have renamed the columns to age, sex, height and weight.
You can rename fields by selecting columns, right click and rename. If you keep the number as part of the new name, that will ease reference back to the descriptions in the booklet.
You don't have to do this, but if you wished, you could also do things like removing records that have no values (no response) in the fields that interest you. You can most conveniently do this by a delete query on the table with the field criteria =null.
After these updates, put your name someplace in the table view screen and do a screen capture to the clipboard by printscreen, then you can open WORD and paste the capture into a WORD document for printing. Pushing the PrtSc key captures the whole screen. alt/PrtSc captures only the active window.

Crosstab queries:
Produce crosstab queries and print reports of at least two breakdowns to show relationships between two variables in the database you have selected. Be sure the reports include your name and section time as part of the printouts.

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 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. These crosstab query results can be analyzed by the ChiSquared procedure to check for dependence. That procedure is covered in BA250.
(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.)

Back to syllabus page