Access and Excel project, only need to do the access part of the project. 34 hours to finish the access part of the project. Some tutors migt have already done this work before!
Unformatted Attachment Preview
CS1100: Project (Assignment 18)
Capstone Project Using Excel & Access
To complete this assignment you must submit an electronic copy to BlackBoard by the due date
of both your Access database and your Excel spreadsheet. Download the starter file and save it
under the name CS1100.LastName.Project
and where LastName is your last name.
To download the files from Google Drive,
click the download button — do not open the
files in Google.
Bird strikes on aircraft are a relatively common occurrence and in most instances those strikes do
not impact the flight. The file “BirdStrikes.xslx” contains a worksheet with data relevant to
analyzing bird strike patterns. Your task is to prepare the data for analysis using both Excel and
Access and then carry out some analysis and data visualization. Some tasks are better done in
Access and some are more suitable for Excel, so you’ll use both. The use of multiple tools in
very common in data analysis and business analytics, so you get a chance to practice this in the
Save the queries using our usual WAFE naming scheme. There are 6 queries (not counting the
many subqueries) you need for the project.
You may wish to build a small database to test and debug your queries. The database you
construct from Excel is too big for testing. You can copy and paste queries from one database to
the other using CTRL-C to copy and CTRL-V to paste. Build the test database so that it has the
same queries but only add a few rows manually.
Open the BirdStrikes.xslx file and inspect the columns to familiarize yourself with the data
contained in the file.
Open Microsoft Access and create a new blank database named CS1100.LastName.Project.
Import the BirdStrikes.xslx Excel file into Access. During the prompts of the import wizard,
do not import the “Wildlife Num” field and choose “Record ID” as the primary key. Use All
Bird Strike Data as the table into which the data is imported. Check the option “I would like
a wizard to analyze my table after importing the data.” Note that the import may take a while
due to the large amount of data in the Excel file. Do NOT use the third option to link to the
table. Use the first choice labeled “Import the source data into a new table in the current
database.” While this will not reflect any changes to the spreadsheet that may occur in the
future, it is quicker and more efficient. Of course, if the data in the spreadsheet changes and
1 of 5
you want to analyze the new data, you’ll have to re-import the data.
Use the Table Analyzer to help design the database table. That wizard will appear
automatically after the data has been imported assuming you checked the option “I would like
a wizard to analyze my table after importing the data.” Click through the wizard — do not
change any table names, primary keys, or corrected values for now. We will fix it later. Once
the table analyzer is done, it will ask you to create a query — select, “Yes, create the query” to
create a query that joins all of the new tables to “reconstruct” the original data. Select “Finish”
and the tables are created. Inspect the tables and then proceed to the next step to rename them
and clean them up. Do not save the import steps.
Update the table names as follows. Be sure to close all tables before renaming. Look at the
data of each table first before you rename it.
? Table with all information as “Bird Strikes”
? Table with Airport as “Airports”
? Table with “Aircraft Model” as “Aircrafts”
? Table with “Aircraft Type” as “Aircraft Types”
? Table with “Airline” as “Airlines”
? Table with “Flight Phase” as “Flight Phases”
? Table with “Impact” as “Impacts”
? Table with “Wildlife Size” as “Wildlife Sizes”
? Table with “Effect/Damage” as “Effects”
You can delete the table Bird Strikes_OLD once you’ve renamed your tables. That table
contains all of the original imported data from Excel before you split the data into separate
tables. It is no longer useful.
Open the design of the “Bird Strikes” table and rename the foreign keys in the Bird Strikes
and Aircrafts tables so that the table matches the relationship view below.
2 of 5
When using the table analyzer, Access uses an embedded lookup for all linked tables. This
can cause issues when creating queries. So, the next step is to remove those lookups. View the
table design for the Bird Strikes and Aircrafts tables and for each field, change the Lookup
from Combobox to Text Box. See the screen shot below for an example.
The next step is to clean up the data. Some records contain both a time and a date for “Report
Date” in the Bird Strikes table. Build an update action query that removes the time part and
only leaves the date part. However, update queries are irreversible, so use a make-table action
query first to copy all of the data in the Bird Strikes table to a new table called “Bird Strikes
Cleaned”. Call your action queries QLastNameCreateTable and QLastNameForCleaning.
Test your query on the copy Bird Strikes Cleaned and only once you are convinced that it
works, make the same query to your actual Bird Strikes table. If your update query did not
work, delete the Bird Strikes Cleaned table, make another copy, and try again. Of course,
once you are satisfied that your update query is correct, then apply it to the Bird Strikes table
and then delete the Bird Strikes Cleaned table since you won’t need it any longer and it just
uses up space in the database.
The function Int([Report Date]) extracts just the date. Use that function in the
Update To field to make the update of the “Report Date” field.
Now that the data is cleaned, we are ready to do some analysis. Initially, we’ll create some
queries to help explore the data and answer questions that are most easily done in Access
rather than Excel. Be sure to use the new tables you created. Do NOT use the imported data -we want you to do joins. Save the queries using our usual WAFE naming scheme:
1. How many bird strike incidents occurred to each Airplanes and Helicopters. Note
that the data also contains some blank aircraft types as well as C. Your result should
have two rows, one for Airplanes and one for Helicopters.
3 of 5
Which airline had the most bird strike incidents? Exclude “UNKNOWN” and
MILITARY from your analysis, i.e., find the airline other than UNKNOWN and
MILITARY. Display the airline name only.
Count the number of airlines which had bird strike incidents during either the
Approach flight phase or the Climb flight phase but not both. Hint: Use this as
your criteria for the field Flight Phase: =Approach OR Climb as your first
subquery. Afterwards it is easiest to use a set-complement query where you subtract
the airlines that had both kinds of incidents..
How many airlines had a bird strike incident during the Approach flight phase but
not during the Climb flight phase. Some airlines may have had incidents during
various phases of flight. In this query, we only want to count those airlines that had
incidents during “Approach” and no incidents during “Climb”. Of course, if they had
incidents during “Approach” and “Landing” it would count as long as it did not also
have an incident during “Climb”. Be sure not to double count any airlines.
Visualization is best done in Excel, so let’s visualize the proportion of bird strikes across
aircraft type in an Excel pie chart. In the “Data” tab in Excel choose “From Access” and then
link the data from Query 1. Build a pie chart as shown below in the “Bird Strikes” Excel file
you downloaded. Put the chart in a new worksheet tab. Name the tab “Visual Analysis”.
Using the original Excel data, create a Pivot Table that produces the following analysis. Place
the Pivot Table into new worksheet called “Pivot Analysis”, a portion of which is shown
below. Use the Flight Date.
4 of 5
Build a query (query 5) with may consist of subqueries that calculates the number of
incidents for each flight phase. Note that you need to update the table Flight Phases as its
ID=1 contains a blank label — manually change that to the value “Unknown”. Do that by
opening the table and entering the data. The result should contain the flight phase and the
number of incidents. The number of incidents are listed below.
BONUS Question: Reports are best suited for Access. Create the report below.
5 of 5
Purchase answer to see full
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more