Field and Query
Traders Project Description: In this project, you will create several queries, use grouping and sorting criteria, and create expressions to make calculated fields. You will also set numeric and date criteria to produce specific results. Instructions: For the purpose of grading of the project you are required to perform the following tasks: Step| Instructions| Points Possible| 1| Start Access. Open the downloaded Access database named Exploring_a03_Grader_EOC. accdb. | 0| 2| Create a new query in Design view. Add the Customers, Orders, and Employees tables to the query design window.
From the Customers table, add the CompanyName, ContactName, and ContactTitle fields; from the Orders table, add the OrderID, OrderDate, and ShippedDate fields; from the Employees table, add the LastName field. Be sure to add the tables and fields in the order listed. | 7| 3| In the first empty field cell of the design grid, create a new field in the query named DaysToShip that subtracts the OrderDate from the ShippedDate. In the criteria row of the DaysToShip field, enter >20. | 10| 4| Run the query and then save it as Shipping Efficiency. | 5| 5| View the query in Design view.
Add the Order Details table to the query window, and then add the ProductID and Quantity fields (in that order). Sort the query in ascending order by OrderID. | 5| 6| Set the caption property of the LastName field to SalesRep. Run, save, and then close the query. | 6| 7| Create a new query in Design view. Add the following tables in this order: Orders, Order Details, Products, and Customers. From the Orders table, add the fields OrderID and OrderDate (in that order) to the query design grid. | 6| 8| In Design view, add a Total row to the query.
In the first empty field cell of the design grid, create a new field in the query named ExtendedAmount that multiplies Quantity by UnitPrice (in that order). Select the Sum function from the Total row in the new field. | 15| 9| In the next empty field cell of the design grid, create a new field in the query named DiscountAmount that multiplies Quantity by UnitPrice by Discount. Select the Sum function from the Total row. Format the calculated field as Currency. | 13| 10| Run the query and then save it as Order Summary. | 5| 11| View the Order Summary query in Design view.
Set the criteria for the OrderDate field to return dates between 5/1/2012 and 5/31/2012. Run, save, and then close the query. | 8| 12| In the Navigation Pane, copy the Order Summary query and paste it into the database as Order Summary by Country. View the new query in Design view. | 8| 13| Replace the OrderID field with the Country field (from the Customers table). Sort the query by the ExtendedAmount field in Descending order. Run, save, and then close the query. | 12| 14| Close the database and then exit Access. Submit the database as directed. | 0| | Total Points| 100|