Module 15 Assignment: Create Queries for Access Database

In this assignment you’ll import an existing database of past orders from a bakery and use the skills you have learned in this module to create a query in the data table.

To complete this assignment, download the Excel file here (Source: GCFLearnFree.org. Access – Designing a Simple Query. Database sample). Follow the directions, then submit your assignment. If you get stuck on a step, review this module and ask your classmates for help in the discussion forum.

  1. Open a new Access database and save the new Module 14 assignment file to the Rowan folder on your desktop as BA132_LastName_QueryDB.accdb, replacing “LastName” with your own last name. (Example: BA132_Hywater_QueryDB) It is a good idea to save your work periodically.
  2. Import External Data: With the Access new database open import the Excel data file into Access.Two overlapping dialog boxes in Microsoft Access. The Get External Data - Excel Spreadsheet dialog box is mostly hidden by the File Open Dialog box.
  3. Import Wizard: Set up the imported data into a new table, make sure the box with first row with column heading is checked and let Access add the primary key, and name the table.The imported excel data has populated an Access table called "OrdersMenuItems"
  4. Create a Query: Use the Query Wizard and create a query that is a simple query and uses the ‘Product TableProduct Name’ and the ‘Quantity’ sections of the table.Simple Query Wizard dialogue box open with "Product TableProduct Name" and "Quantity" Selected.
  5. Resize Columns of Query: Resize the first column to be able to read the names of all the products.Products Table Product Name column has been resized to show the full name of each product.
  6. Query Design: Now create a second query, but this time from when you run the wizard button choose to ‘Modify the query design’.Simple Query Wizard dialogue box open with radio button "Modify the query design" selected.
  7. Add New Criteria: Now you wish just to see the number of orders that were chocolate chip cookies. Enter that in the criteria field."OredersMenuItems Query1" open with the term '="Chocolate Chip"' entered in the Criteria cell.
  8. Run Query: Run the query, resize the first column and see how many orders there were for chocolate chip cookies.Access query open showing two columns: "Product TableProduct Name" and "Quantity". Two records with the name "Chocolate Chip" appear in the "Products TableProduct Name" column with corresponding Quantities of 2 and 1.
  9. Run Third Query: Now, run another query but this time for “pecan” and when the results are displayed, add in an Autosum total at the bottom and see how many pecan orders have been placed. Save this query.Access database table showing the results of the query for Pecans. There are ten records for "Product TableProduct Name" Pecans with a quantity of 1 each. An autosum row appears at the bottom of the table showing a total of 10.
  10. Rename Queries: Rename the queries to match what is queried to better remember for what they are searching.The name of the pecan query in the Access table shown in the previous image was formerly "Query1" but now is "PecanOrders".
  11. Save: Save the file as an Access database and make sure all the queries have been saved as well.
  12. Submit the database file in your course online.