In the last chapter, you modified some tables for Rosey€™s Roses. Now the company would like you to help it create some queries to extract information from the database. Make the following changes using the ch10-06_student_name file you created in Chapter 10. Open your ch10-06_student_name file and then save it as ch11-06_student_name before making the indicated changes.

a. Create a select query using the Grower ID field from the Grower table, the Description field from the Product table, and the Cost/Unit for all products from Grower ID 200. Do not show the Grower ID on the resulting query. Save this query as Query A and then print it.
b. Create a select query that lists the Grower Name, product Description, and Quantity for all products where the Description field contains the text €˜€˜5€™€™. Save this query as Query B and then print it.
c. Create a select query that lists the product Description, Grower Name, Contact, Phone Number, and Cost/Unit for all products with a Cost/Unit less than $15. Save this query as Query C and then
print it.
d. Create a select query that lists the product Description, Grower Name, Contact, Phone Number, and Cost/Unit for all products with a Cost/Unit greater than $15 but less than $40. Save this query as Query D and then print it.
e. Create and print a new table called Customer as follows:

f. Add a number field €˜€˜Markup€™€™ to the Grower table. Set the field size of this new field to Single and the Format to Percent. Set the Markup to 150% for Grower ID 100 and 300 and then set the markup to 200% for Grower ID 200 and 400. Print the Growertable.

g. Create and print a new table called Quote as follows

h. Establish a relationship between the Quote table and the Product table (ID) and the Customer table and the Quote table (Customer Number) enforcing referential integrity. Print the Relationships report.
i. Create a select query that contains the Quote Number, Customer Name, Product Table Description, Quantity Ordered, Sales Price (a new computed field: Cost/Unit (1 þMarkup) formatted as currency, and Quote Amount (a new computed field: Quantity OrderedSales Price) also formatted as currency. Save as Query E and then print.
j. Modify the query you created in (i) above to include a new field called Cost (a computed field: Quantity OrderedCost/Unit) and a new field called Gross Profit (a computed field: Quote Amount €“ Cost). Format both as currency. Save as Query F and then print.
k. Create and run an update query that increases the Cost/Unit of all products by 15%. (Remember to back up your file first.) Save this query as Query G. Create a new select query that lists all products showing the ID, Description, and Cost/Unit fields. Save this query as Query H and then print it.
l. Create a parameter query that lists the product Description and Grower Name. The query should state €˜€˜For which type of rose?€™€™
Save this query as Query I. Run the query for type Creeper and then print it.
m. Create a select query that sums the total cost of all roses currently in inventory, formatted as currency. Save this query as Query J €“ 1, run it, and then print it. Then create the summation query, save it as Query J €“ 2, run it, and then print it.
n. Create a select query that sums the cost of all products by category (in currency format), sorted by type. Your new query should use Query K €“ 1 as its source and have two fields: Type and Cost. Save the new query as Query K €“ 2, run it, and then print it.

Transcribed Image Text:

Customer Table Structure: Data Type Field Size/Format Primary Key? Field Name Customer Short Text Yes Number Short Text Customer Name 30 Discount Number Single/Percent Customer Table Data: Customer Number Customer Name Discount 10 Jan Muller 20% 11 Robert Frost 15% 10% 12 Juliet Inch 2. Quote Table Structure: Field Name Data Type Field Size Primary Key? Quote Number Number Yes Customer Short Text Number ID (from the Product table) Quantity Ordered Number Number Quote Table Data: Quote Number Customer Number ID Quantity Ordered 100 10 23 101 11 11 17 102 12 16 35