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

Weekday
Weekend
Total
Time of Day
Number
Percent
Number
Percent
Number
Percent
12:00-2:59 a.m.
188

450



3:00-5:59 a.m.
142

211



6:00-8:59 a.m.
346

63



9:00-11:59 a.m.
281

96



12:00-2:59 p.m.
382

98



3:00-5:59 p.m.
601

159



6:00-8:59 p.m.
694

665



9:00-11:59 p.m.
470

592



Total












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

State
Population
Area
Union
State Bird
Region
Alaska
479000
586412
5/12/1959
Ptarmigan
Northwest
Arizona
2963000
113909
3/26/1912
Cactus Wren
West
California
25174000
158693
1/21/1848
Quail
West
Florida
10680000
58560
2/3/1850
Mockingbird
South
Hawaii
1023000
6450
5/12/1959
Goose
West
Idaho
989000
83557
3/4/1890
Bluebird
West
Kansas
2425000
82264
2/3/1850
Meadowlark
Midwest
Maine
1146000
33215
12/24/1820
Chickadee
Northwest
Nebraska
1597000
77227
2/9/1867
Meadowlark
Midwest
New York
17667000
49576
11/22/1788
Bluebird
Northwest
Ohio
10746000
41222
12/7/1803
Cardinal
Midwest
Washington
4300000
68192
3/3/1889
Goldfinch
West
Wisconsin
4751000
56154
1/21/1848
Robin
Midwest








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