In the last chapter you added and modified some tables for Kelly's Boutique. She would now like you to create, run, and print some select, parameter, and action queries. Make the following changes for Kelly, using the ch 10-03_student_ name file you created in Chapter 10. (Add a field Quantity (Data Type = Number) to the Book Table and then enter values as follows:
Book Title Quantity
Angela's Ashes..................................18
Betsy - Tacy......................................4
Blueberries for Sal...............................5
Caddie Woodlawn.......................................4
Deep End of the Ocean..........................2
Divine Secrets of the YaYa Sisterhood 3
Green Eggs and Ham...........................15
Harry Potter and the Chamber of Secrets....10
Harry Potter and the Prisoner of Azkaban....8
Harry Potter and the Sorcerer's Stone.........6
Hop on Pop........................................8
The Cat in the Hat................................4
The Notebook.....................................3
Add a field Markup (Data Type = Number, Field Size = Decimal, Format = Percent, Scale = 2, Decimal Places = 0) to the Dept Table and then enter values as follows:
Dept Markup
Adult.............100%
Children...........50%
a. Create and print a select query that lists the author and book title for all books written by Seuss. Save this query as Ch 11 Kelly Case a before you print it.
b. Create and print a select query that lists the book title and author for all books purchased and supervised by Barbara Manchester, sorted in ascending order by book title. Save this query as Ch 11 Kelly Case b before you print it.
c. Create and print a select query that lists the book title and list price for all book titles that start with Harry. Save this query as Ch 11 Kelly Case c before you print it.
d. Edit the query you just created. Add fields for author and for publisher and delete the list price field. Change the criteria from "starting with Harry" to "containing the word Secrets". Save this query as Ch 11 Kelly Case d before you print it.
e. Create and print a select query that lists the book title and the publisher contact person and phone number for all books with a list price greater than $17. Save this query as Ch 11 Kelly Case e before you print it.
f. Create and print a select query that lists the ISBN, book title, and list price for all books with a list price greater than $20 or less than $10, sorted in ascending order by list price. Save this query as Ch 11 Kelly Case f before you print it.
g. Create and print a select query that lists the book title, list price, quantity, and retail value (a computed field equal to list price multiplied by quantity), sorted in descending order by retail value. Be sure to format the field as Currency. Save this query as Ch 11 Kelly Case g before you print it.
h. Create and print a select query that lists book title, list price, markup, unit cost, quantity, and cost. Unit cost is a computed field (list price divided by 1 plus markup). Cost is another computed field (unit cost time's quantity). Be sure to format both computed fields as Currency and to sort the query alphabetically by book title. Save this query as Ch 11
Kelly Case h before you prints it.
i. Create and print a select query that sums the total cost of the book inventory. (Hint: Use the query just created in part lh as your source for this new query and use the currency format.) Save this query as Ch 11 Kelly Case i before you print it.
j. Modify the query created in part lh so that it includes the Department field. Save the query as Ch 10 Kelly Case h 1, and then use that modified query as the source for a query that sums the cost of inventory by department. Save this latter query as Ch 11 Kelly Case j before you print it.
k. Create and run an action query that increases all books' list price by 5 percent. Be sure to back up your file first! Save this query as Ch 10 Kelly Case k. Create another query that lists the book title and list price for all books, sorted alphabetically by book title. Save this query as Ch 11 Kelly Case k 1 and then print it.
1. Create a parameter query that lists books from a particular publisher. The query should ask "Enter publisher's name:" and then list the publisher, book title, and quantity for that publisher. Save this query as Ch 11 Kelly Case 1. Run and print the query after entering "Harper Collins" as the publisher.
m. Create and run a delete query that deletes all products with a quantity of 0. Save this query as Ch 11 Kelly Case m.
n. Create a query listing the book title and quantity for all the remaining books in inventory, sorted by quantity in descending order. Save this query as Ch 11 Kelly Case n and then print it. Make sure you keep a copy of this file for use in the next chapter.