Instructions are included as a file. Microsoft Access 2016 or higher is needed to complete.
A. Lab BSBA BIS245A-4 B. Lab 4 of 7: Completing Queries (40 Points) C. Lab Overview--Scenario/Summary COs: 6. Given a physical database containing tables and relationships and business requirements, create the necessary queries. Scenario/Summary We can collect all the data in the world; however, if we can’t access it and use it, it is probably useless. Gaining knowledge from stored data can be very beneficial when it comes to making important business decisions. This lab will give the student practice in querying or questioning data. The lab begins with a simple example of query development using Access, then evolves to more complex queries which the student should perform after completing the first exercise. The student can create a query with the wizard or with query design view. Upon completing this lab, you should be able to 1. create a query by following lab instruction; 2. create a query by using either query designer, or query wizard; and 3. interpret and analyze the results of queries. D. Deliverables Submit the MS Access database file YourName_Lab4.accdb that contains the queries created in this lab. Step Deliverable Points Part A Steps 1-3 3 queries: Supplier Contacts High Value Products Product Recalls 24 Part B Steps 1-2 2 queries: Extended Order Prices Order Fulfillment Time 16 E. Lab Steps Preparation 1. Download the file Lab4.accdb from the link provided on the Lab Overview page-> Lab Files. 2. If you are using Citrix for MS Visio or MS Access, follow the login instructions located in the Lab area in the Introduction and Resources, and upload the Lab4.accdb file to your working folder on the Citrix server. (You can see a video about how to do this by clicking the link Saving From Citrix under Citrix Tutorials from Course Resources link listed at the bottom of the Modules page.) Lab Part A: Create Single-table and Multitable Queries Step 1: Create a single-table query In this exercise, you will create the supplier contacts query, which lists all suppliers, the name and title of the contact at each supplier, and the contact’s phone number. a. Open Lab4.accdb found on the Lab Overview page -> Lab Files. Click the File area, click save database as, and then type YourName_Lab4 and click save. b. Click the Create area, and then click query wizard in the queries group to launch the new query wizard. The new query wizard dialog box opens. Simple query wizard is selected by default. c.Click OK. d.In the tables/queries dropdown, select table: suppliers. e.Select SupID from the available fields list, and then click >. Repeat the process with SupCompanyName, SupContactName, SupContactTitle, SupPhone, and SupFax. The six fields should now appear in the selected fields list box. Click next. f.Enter supplier contacts as the query name, and verify that the open the query to view information option is selected. Click finish. This query name describes the data in the query results. Your query should have six fields: SupID, SupCompanyName, SupContactName, SupContactTitle, SupPhone, and SupFax. g. Right-click the supplier contacts area and select close to close the query. Step 2: Create a multi-table query In this exercise, you will create the high value products query that displays all products with a unit price greater than $50 and the supplier of each product. a.Click the create tab, and then click query design. b.In the show table dialog that appears, select the products table and click add; then select the suppliers table and click add. The suppliers and products tables are added to the table area of the query design view. c.Click close to close the show table dialog. d.In the products table, double-click the fields ProdID, ProdName, and ProdUnitPrice to add them to the query design grid in the lower part of the screen. Then, in the suppliers table, double-click the fields SupID and SupCOmpanyName to add them to the grid. (You can also drag and drop each field onto the query design grid.) Your query design grid should now look like the following. e.Click in the sort row in the ProdUnitPrice column, and set the drop down to descending. (This will sort products in descending order by price, from highest price to lowest price.) f.Click in the criteria row in the ProdUnitPrice column, and enter the criterion >50. (This will limit your results to products with a price greater than $50.) Your query design grid should now look like the following. g.Click the save button (disk icon) or press Ctrl+s to save the query. Enter the name high value products and click OK. h.Click run in the results group. i.In the column headings at the top of the query results, click and drag or double-click on the column boundaries to widen the columns as needed, as you would in Excel. Your query results should look like the following. j.Right click on the high value products tab and select close to close the query. If you are prompted to save changes to the layout, click yes. Step 3: Create a query with multiple criteria In this exercise, supplier Lorem Ut Inc. has recalled all products sold on or after 6/1/2011, and supplier Montes Nascetur Inc. has recalled all products sold on or after 8/15/2011. You will create a product recalls query that lists all orders for products subject to these recalls, with the customer contact information for each order. a.Click the create tab, and then click query design. b.In the show table dialog that appears, select and add all five tables in the following order: suppliers, products, OrderLine, orders, and customers. Then click close to close the show table dialog. c.Drag the bottom and right edges of each table box to expand the box to show all the field names. If necessary, drag the boxes by their titles at the top to rearrange them so that the table area in the top half of the screen looks similar to this. d.Add the following fields to the query design grid in the lower half of the screen, either by double clicking on the field name or by dragging and dropping onto the grid. Suppliers table: SupID, SupCompanyName Products table: ProdID, ProdName Orders table: OrderID, OrderDate Customers table: CustID, CustLastName, CustFirstName, CustPhone (Notice that no fields from the OrderLine table are included in the query results; however, OrderLine must be included in the query because it serves as a junction table to relate products to orders.) At this point, your query design grid should look like the following. e.In the criteria row, in the column for SupCompanyName, enter Lorem Ut Inc. (including the period). In the same row, in the column for OrderDate, enter >=6/1/2011. (Notice that when you click away from each cell, Access adds quotation marks around the text entry and pound or number signs (#) around the date entry. This is automatic for text and date criteria.) f.In the or row (immediately below the criteria row), in the SupCompanyName column, enter Montes Nascetur Inc. (including the period). In the same row, in the column for OrderDate, enter >=8/15/2011. (Notice that Access again adds quotation marks around the text criterion and pound or number signs around the date criterion.) Click away from the cell containing 8/15/2011 into any other cell. Your query design grid should now look like the following. g.Click the save button (disk icon) or press Ctrl+s to save the query. Enter the name product recalls and click OK. h.Click run in the results group. i.In the column headings at the top of the query results, click and drag or double click on the column boundaries to widen the columns as needed, as you would in Excel. Your query results should look like the following. j.Right click on the product recalls tab and select close to close the query. If you are prompted to save changes to the layout, click yes. Part B: Create single-table and multitable queries Step 1: Create queries with calculated fields You create the extended order prices query in this exercise. a.Click the create tab, and then click query design in the queries group to start a new query. The show table dialog box opens so you can specify the table(s) and queries to include in the query design. b.Select the orders table, and then click add. Select the OrderLine table, and then click add. Close the show table dialog box. c.Double click the OrderID and OrderDate fields in the orders table to add them to the design grid. d.Double click the ProdID, OrderLineUnitPrice, OrderLineQuantity, and OrderLineDiscount fields in the OrderLine table to add them to the query design grid. Your query design view screen should now look similar to this. e.Right click in the first empty cell in the top row of the query design grid to the right of OrderLineDiscount, and click zoom. f.In the zoom dialog, enter the formula ExtendedPrice: [OrderLineUnitPrice]*[OrderLineQuantity] Click OK to close the zoom dialog. The formula you entered appears in the query design grid. g.Right click in the next empty cell to the right in the top row of the grid, select zoom, and enter the formula DiscountAmount: [ExtendedPrice]*[OrderLineDiscount] Click OK to add this formula to the design grid. h.Right click in the next empty cell to the right in the top row of the grid, select zoom, and enter the formula DiscountedPrice: [ExtendedPrice]-[DiscountAmount] Click OK to add this formula to the design grid. Your query design grid should now look like the following. i.Save the query with the name extended order prices. j.Click run in the results group to view the query results. Adjust the column widths as needed. Your query results should look similar to the following. k.Click the home tab, and then select totals in the records group to add a total row to your query. l.In the Total row at the bottom of the query results screen, click under the DiscountedPrice column. A drop-down arrow will appear on the left side of the cell. Click this drop-down arrow and select Sum from the list that appears. You should now see the total discounted price of all orders in the total row. m.Right click on the Extended Order Price tab and select Close to close the query. If prompted to save your layout changes, click Yes.