LAB 1 | Complete Solution

Large Data Sets

To complete this assignment you must submit an electronic copy to BlackBoard by the due date. Download the starter files. To download the files from Google Drive, click the download button -- do not open the files in Google. Please follow the naming convention for naming your CS1100.LastName.E11 and your ACCESS file CS1100.LastName.A11where LastName is your last name.

Knowledge Needed

This lab requires the following Excel and Access techniques:

• Load data into Access
• Connect Access to Excel
• Create Pivot Table in Excel from Access data

Analytical Research has obtained data for Amazon affiliate sales and wants to analyze the data. However, the data is too unwieldy for Excel and the queries are too cumbersome to express in Excel. So, a decision has been made to store the data in Access but to do the analysis in Excel. Here are your tasks:

1. Download the Excel file "Amazon Referrals" from Blackboard
2. Start Microsoft Access and create a "Blank desktop database". Name the database "AmazonDB". Press the "Create" button to create the database.
3. Select the "External Data" tab and import the downloaded Excel workbook "Amazon Referrals".
4. Create a "Link to the data source…" so that the database will be automatically updated whenever the Excel data changes.
5. Use the table name "Data" (the default name) for the link table that is created.
6. Double-click on the table "Data" to ensure that the data has been loaded.
7. Use the names listed in the figure below and rename for ACCESS field names for your table “Data”.
8. Create a query that finds all sales where the Seller was "Third Party". To do that go to the "Create" tab and select "Query Design", Here's what the query looks like in the Query Builder.

9.                  Name the query "Third Party Data". To do that, close the query by right-clicking on the query's tab and selecting "Close". Access will then prompt to save.

10.              Run the query and observe the result. Note that the database and result is automatically saved.

11.              After exiting Access, open the "Amazon Referrals" workbook in Excel.

12.              Go to the "Data" tab and select "From Access" and connect to the "AmazonDB" database you just created by selecting its file.

13.              View the data as a "PivotTable Report" and add it to a "New worksheet".

14.              Create the PivotTable below to analyze the data.

Hints:

• Close the Access database before connecting it to Excel

GRADING RUBRIC

This rubric is intended to guide graders in their evaluation of the students’ submissions.

• Formatting