access assignment

Access - Assignment #5

20 Points Possible Name: _____________________________ Points:____ / 20 For each query created – unless otherwise specified, name the query P5-## - where the ## represents the question number on this sheet. Open the Access Data Project5.mdb database and review the table structures, data and relationships. Then, create a query to answer each of the following (one query per ##) problems. Each query is worth 1 points, unless otherwise noted. 1. Import Data. Download the shippers.txt file from Blackboard and import the data into the Shippers Table. 2. List the 10 most expensive products from the product table (top 10 values when sorted Descending). 3. Identify all the different countries our customers are from. List the countries alphabetically and only list each country once (unique). 4. Find customers that do business in London or Madrid. 5. Find the customers with a contact that is in a “Manager” role. 6. List the customers that have a blank fax number (is null). 7. (2 points) Create a parameter query that prompts for a city and then lists the customers for that city. 8. How many customers do we have in London? 9. Find how many customers there are in each city (count). 10.(2 points) Create a query that lists the order details: OrderId, Product Name, Unit Price and OrderQty. Also calculate a new field called AmtDue. AmtDue is calculated by multiplying the unit price by the OrderQty. Order the data by Product Name. 11. List any customers in the table that have not placed an order (unmatched). 12. (2 points) Create a query from the Order Details that counts the number of orders and totals the quantities on order by product. List the Product Name, the Supplier Name, the Quantity on Hand, the count of orders and the total of the Qty ordered. 13. Find if there are any duplicates in the Supplier table. Duplicates would be identified as two records that have the same Supplier Name and Phone Number. If those values match, include the rows in the query. List all fields in the query output. 14. (2 points) Create a query that displays the total, average, minimum, and maximum Freight Costs (in Order table) by ShipperID. List the ShipperID, the ShipperCompanyName, the Shipper Phone Number and the calculated fields. 15. List all orders placed in the 4th quarter of 2014 (orders placed Between 10/1/2014 to 12/31/14). 16. Add a new currency field to the Shipper’s table called FlatRate. Create an Update Query to set the value of FlatRate to $15.00 for all shipper’s with ID > 3. 17. Save and post your finalized document to the Blackboard. There are no print-outs required.