Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/Week 3 - Lab 2: Query Analysis Examples (Union Append queries). Review for Assignment 3

This page is based on the material from here. But, the page has been updated to match the schedule and changes for Fall 2009.

Notes

• Skills covered in this module:

1. Create Query Analysis

• A step by step example of how to properly perform some query analysis on data including some textual analysis.
• Currently, sample screenshots are provided. Once a copy of Office 2007 is obtained, sample databases will be made, along with their corresponding screenshots.

How to import Excel Data into an Access DB

Basic Notion: The output of one query, is the input for the next query. An analysis is composed of a sequential set of queries (i.e. a “path” in our dots-and-edges language).

Using the spreadsheet data for "What are your five top favorite websites?" survey, you will import the data into Access for further analysis.

• Excel data to use: Websites Ranking

Go to the "External Data" tab and then select from the Import section Excel.

Then using the wizard select the spreadsheet file and import into a new table

The result of that table should look similar to the following image.

Create a separate query for every website rank

• Using the imported Access table, create five queries for the five different website rankings. This means to create 5 queries as follows: One query will hold the websites that have the rank "Website1", another query will hold the websites that have the rank "Website2", ... etc.

• Create a query that will organize the information as follows for the websites that have the first rank. In that query, a new field called "WebsiteRank" is added to reflect the rank of the selected websites.

• The results of this query should look similar to the one below.

• By the end of this section, you should have 5 queries for the five different website rankings similar to the figure below.

Create a Union Query

• Using a union query, gather the records from the five separate websites queries into one query.
• First, you will have to select the five website queries as your data source, and specify that you want to use a union query as shown in the figure below.

• For the union query, you need to use an SQL statement as shown below.

The resulting union query will have the different websites of all the five rankings.

Create an Append Query

• The resulting data from the union query needs to be copied to an Access table.

• To do that, we will create an empty table called WebsiteRankings as shown below.

• Next, we will create an append query that would copy data from the union Query to the WebsiteRankings table. Create the append query as shown below.

Step 1

Step 2

• As you run the append query, the WebsiteRankings table will be automatically populated as follows.

Analyze the WebsiteRankings Table

• Extract the website data from the “WebsiteRankings” table, and put into “long” form:

WebsiteRank WebsiteName 1 FaceBook 2 Hotmail 3 …..

… 1 Facebook 2 MSN 3 ….

• You will create a query that will calculate the website ranking use a custom formula like this one:

IIf([WebsiteRank]="Website1",5,IIf([WebsiteRank]="Website2",4,IIf([WebsiteRAnk]="Website3",3,IIf([WebsiteRank]="Website4",2,1))))

• Create another query based off this previous query that will do a summation of all the scores for each website.