Advanced Query Calculations for Book Sales Analysis

1. CPSC 203 Tutorial Xin Oct 25, 2010

2. Calculations in queries • Title: expression • Exercise • Create a new field in Query1 with • SaleAmount: price*sales • Create a new field in Query1 with • Profit: ((price*sales –advance) * (1 –royalty) • Create a new field in Query3 with • [au_fname] & “ “ & [au_lname]

3. Conditional Expression • IIF • Create a new field in Query3, showing “has a contract” if bktblTitles = 1, “no contract” otherwise • Nested IIF • Create a new field in Query1, showing • “very profitable” if profit >= 1,000,000 • “profitable” if profit >= 100,000 • “OK” if profit >= 10,000 • “not enough” otherwise.

4. Aggregate functions • Calculate over a column • Sum, Avg, Min, Max, ... • Exercise • Create Query4 based on Query1 • Add profit to Query4 • Compute the SUM of profits • Create Query5 and count the # of books • Modify Query5 to show the # of books each publisher has published • Modify Query5 to show the # of biography OR history books each publisher has published • Add a criterion that royalty rate is greater than 0.06

5. One more exercise • Create Query6 showing the # of authors each book has

6. Crosstab queries • Use Query Wizard to ease the task of creating a crosstab • Exercise • Create a crosstab to show the # of books each author published with each publisher • Create a crosstab to show the SUM of sales by each advance payment (column) by each author (row). • Based on Query2