In the last chapter, you modified some tables for Roseys Roses. Now the company would like you
Question:
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.
Step by Step Answer:
Using Microsoft Excel and Access 2016 for Accounting
ISBN: 978-1337109048
5th edition
Authors: Glenn Owen