Courses/Computer Science/CPSC 203/CPSC 203 Template/Fall 2009 Teaching Assistants/F09 Thomas Burt/Lab 2.5

Quiz Review

Creating Tables, Fields, and Records

'Records' are just rows in the table / the actual data

These skills are covered here here

• Creating a table
  • Naming it correctly
• Creating fields
  • Naming them correctly
  • Setting the Data Type
  • Setting the Default Value (not sure if we covered this, just in case here's a link showing how
• Adding Data
  • You won't need to import from a spreadsheet

Creating Relationships

You may have to specify a one-to-one or many-to-one relationship. Practice this first so you don't get hung up on quiz day.

Important: To pull data from more than one table into a single query, you have to create relationships in the relationships tab

• You could also use SQL view, but you won't just be able to copy from the examples since the table names and fields are different

Information on how to create relationships is available here.

For the quiz, don't use "Lookup" fields unless directed to

Creating Queries

For the most part, this is outlined in last weeks lab

Complex Calculations

You will need to know how to use functions and expressions within the query. The concept is similar to Excel, but the syntax is a bit different.

I added some data to our previous lab which explains how to do this.

Crosstab Queries

You may have to make a "Crosstab Query" or two. If asked, use the wizard for this.

• Try to get familiar with how each option affects the result. Run the wizard a few times and compare the output.

Query Analysis

Basic Notion: The output of one query is the input for the next query. An analysis is composed of a sequential set of queries.

Goal: We want to calculate which websites were most popular, giving added weight to higher rankings.

Import The Spreadsheet Data

Download last year's Survey Data. You will import the data into Access for further analysis.

Open up Access 2007 and create a blank database

Got 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 this:

Create holding table

Call it Website Rankings. Keep this table empty for now. Define the following fields:

Create Queries

We're going to create a set of queries to select each website by rank.

Select Conditionally

SELECT 
Data.Student, 
Data.Class, 
Data.Website1 AS Website,
1 AS WebsiteRank
FROM Data;

Run the query

You should get a result like this:

Make Append Query

From design view, click the "Append!" Icon:

Select Target Table

Select the table that you want the result of this query to be inserted into. Use the holding table we made earlier:

If you named the fields in the query the exact same as those in the holding table, they should automatically show up below:

If this doesn't happen, check the field names and try again

Append into holding table

Click Run!

• A dialog should pop up asking whether you really want to insert all the data.

And repeat...

Switch to SQL mode. You should see something like this:

INSERT INTO [Website Rankings]
  ( Student, Class, Website, WebsiteRank )
SELECT 
  Data.Student, 
  Data.Class, 
  Data.Website1 AS Website, 
  1 AS WebsiteRank
FROM Data;

1. Change "Website1" to "Website2"
2. Change "1 AS WebsiteRank" to "2 AS WebsiteRank"
3. Click Run!

Repeat for 3, 4, and 5.

Your Website Rankings table should now have a few hundred rows.

Analyze

Create a new query that ranks websites based upon the sum of their score. Remember that a rank of one should count for more than a rank of five.

You can make an intermediate query to reflect this if you wish:

SELECT 
*,
(6 - [WebsiteRank]) as [Weighted Rank]

FROM [Website Rankings]

Save this query and use it to generate a new query that groups by the website name and sum's the weighted rank. Or, just use the following:

select
wb.website,
sum(wb.[Weighted Rank]) as [Weighted Rank Sum]
from
( 
SELECT 
*,
(6 - [WebsiteRank]) as [Weighted Rank]

FROM [Website Rankings]

) as wb

group by wb.[Website]

Result

My results looked like this:

Compare that to the sites you've been visiting during lab to see if the results make sense.