Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/Week 3 - Lab 1: Crosstab queries and Custom Calculations

Notes

• Skills covered in this module:

1. Create Crosstab Queries
2. Create Custom calculations

• A step by step simple example of how to create Crosstab Queries , Custom calculations.

Background Resources

• • Lab 10 of Fluency Computer Skills Workbook

Crosstab Queries

Crosstab queries are queries that display information in a compact spreadsheet-like format. You will learn how to create some basic crosstab queries from tables. NOTE: Microsoft Access uses two additional keywords: TRANSFORM and PIVOT to create a Crosstab (look at a crosstab query in SQL view to see syntax).

One: Give Me Crosstabs

Follow along with the file named "W08_BookExampleWQueries_20080228.mdb" First go to queries, and create a new query using the Crosstab Query Wizard. Click on the OK button.

Select a previously created query or table (in this case qry4_selFromPublisherAndTitles) to display information from. Click on the next button to continue.

Select from the Available Fields list the fields you wish to display by adding them to the Selected Fields list. For example, the "pub_name" field. Click on the next button to continue.

Specify a field to display values for the column heading, you should see a preview of the crosstab being generated in the preview. For example, the "advance" field. Click on the next button to continue.

Specify the type of calculation to perform and which field. For example, use the COUNT function to count the number of book titles in the field "title_name".Click on the next button to continue.

Click on the Finish button. You should now see your crosstab query that you just created. Execute the query by clicking on the execute query button and the results should be similar to the picture you see below.

Your final results should be similar to the picture below.

Custom Calculations

You will learn how to create an if statement similar to the type of if statement used in Microsoft Excel. An If Statement is used to do certain actions only if something is true. For example, you might want to print out the message "We are losing money" if total sales for the quarter are below some amount. Otherwise, you'd just want to print out "We're making money!"

IF Statement

The IF function lets you do these kinds of value based decisions.

Create a new query similar to the pictured below: Here you will create a query that will check to see if the the book's author received an advance for greater than or equal to $20,000. If so then print out "Received an Advance", otherwise we print out in the column that this particular author "Did not receive an Advance".

IF (greater than or equal to 20000)
  print "Received an Advance"
ELSE
  print "Did not receive an Advance"

Advance: IIf( [advance]>=20000,"Received an Advance","Did not receive an Advance")

Here is the result you should get.

//INCLUDE SCREEENSHOT OF RESULTS

Nested IF Statement

With nested IF statements, you will solve the problem of dealing with more than one possible outcome. Create a query similar to the previous example, but instead you will check in varying amounts how much of an advance was given. For example, if the book's author received an advance for greater than or equal to $10,000, $20,000, or more. If the amount was less than or equal to $10,000, then print out "Low Advance Payout". If the amount was less than $20,000, then print out "Middle Advance Payout". If the amount was greater than or equal to $20,000, then print out "High Advance Payout". If the amount was less than $10,000, then print out "No Advance Payout".

This is how you would create the first condition, to create the second condition of if greater than $20,000 we place that condition into the "else" part of the first logical if condition.

IF (less than or equal to 10000)
    print "Low Advance Payout"
ELSE
    IF (less than 20000)
        print "Middle Advance Payout"
    ELSE
        IF (greater than or equal to 20000)
            print "High Advance Payout"
        ELSE
            print "No Advance Payout"

Advance: IIf([advance]<=10000,"Low Advance Payout",IIf([advance]<20000,"Middle Advance Payout",IIf([advance]>=20000,"High Advance Payout","No Advance Payout")))

Here is the result you should get.