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
YRBSS 2001 data
CDC Youth Risk Behavior Surveillance
System (YRBSS) database http://www.cdc.gov/nccdphp/dash/yrbs/
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.
CDC Home http://www.cdc.gov
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
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
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)
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
[grade] + 8
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.
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