Fatalities Analysis Solution
Q 1. Using Microsoft Excel, you are requested to create a table to look like the sample below and save it as Student First and Last name.
The table below contains data for the number of pedestrians that were killed in the United States during 1994 in motor vehicle crashes. Perform the following procedures in Excel.
1. Calculate the total number of pedestrian fatalities that occurred during weekdays. Calculate the percent of all weekday fatalities that occurred during each of the given times of day.
2. Calculate the total number of pedestrian fatalities that occurred during weekends. Calculate the percent of all weekend fatalities that occurred during each of the given times of day.
3. Calculate the total number of fatalities that occurred during each time of day (i.e., add weekday and weekend fatalities for each time of day). Calculate the percent of all pedestrian fatalities that occur during each time of day (weekdays and weekends combined).
4. Format your percentage data so that percentage signs and only one decimal place is shown.
5. Create a bar graph for the percent of all fatalities by the time of day.
6. Create a graph to compare between the percent of all weekday fatalities and the percent of all weekends fatalities that occurred during each of the given times of day.
Pedestrians Killed by Time of Day and Day of Week
Day of Week
Time of Day
Q 2. Using Microsoft Access, you are requested to define a United State database consisting of one table called Student first and last name.
In this question you are requested to do the following:
A. Design the table structure and enter the information below:
Your table has the following fields with the following properties:
· State - data type is Text, field size is 20. It is also the primary key.
· Population is Number
· Area is Number
· Union is Date, Short date
· State Bird is Text, field size is 30
· Region is Text. field size is 15
B. Create queries
· List State and Union fields of the states that entered the union between1/1/1850-1/1/1920, sorting by union date.
· List State, Area and Population fields of the states with areas less than 60,000 square miles that also have more than 10,000,000 people, sorting by Population.
· List State and State Bird fields of the states that their State bird is bluebird or meadowlark.
C. Create reports:
· The first report prints States, Area, Population and Region. (Grouping by Region, and sorting by Area).
· The Second report prints States, Union, and Region. (Grouping by Union, and sorting by Region).
You'll get a 42.0KB .ZIP file.