Mystery Database

Solving a Mystery/Creating a Database

Objectives

  • Students will be able to create a database using Microsoft Access.

  • Students will be able to solve a mystery using a database.

  • Students will be able to perform a query using a database.

  • Students will be able to understand database terminology.

I can create a database and run a query on given data entered.

METS

  • TC.3- Perform queries on an existing database.

  • TC.4- Know how to create and use various functions available in a database.

  • TC.6- Use accurate technology terminology.

  • CT.1- Use databases to make predictions and to assist with problem-solving.

  • RI.1- Use a variety of digital resources to locate information.

  • CI.2- Create an original project to present information.

Introduction

You are a detective assigned to the robbery division. You will use Microsoft Access to help solve 2 mysteries. You will be given a collection of information about suspects that you will include in your database and it is your job to search the database to find out who the criminal really is. The same database will be used for both mysteries.

First, you must set up the database. To do this, follow the directions below.

Directions

  • Open Microsoft Access (Go to Windows-> All Apps->Microsoft Access 2013->Access 2013.

  • Select Blank Desktop Database.

  • Click on File-> Save As and save your Mystery Database to your Documents Folder.

  • Select Create (on the top) and click Table Design.

  • Type in the information that you see in the table below (Only the bold text)

You now have your table set up.

Save your Table as Mystery Database following the directions below:

  • Click File on the Menu Toolbar

  • Select Save

  • Name the file Mystery Database in the Save As Table Name text box

When prompted to assign a primary key, choose YES because this will help you keep track of your records.

  • After saving it, close the table.

Designing a Form

You will now design a Form for entering information into the table.

  • In the left column, select your Mystery table

  • You will now create records for each suspect. Enter the information in the appropriate fields. **Be Sure to Save your database & save it often!

Adding Your Database

to Your Electronic Portfolio

  • Log into your Electronic Portfolio and Create a New Page. Title this page Mystery Database.Click Create.

  • Type Table of Suspects on the top of the page **(hit the Enter key twice and type Mystery 1 hit Enter twice and type Mystery 2)

  • Copy your entire Table of Suspects (CTRL+A) and paste it (Ctrl +V) onto your Mystery Database web page.

  • Click Save

Adding Your Mystery Database to the

Sidebar on Your Electronic Portfolio

Follow the directions below to do this:

  • Click Save (top right of your page)

  • Click on the More Actions (Honeycomb) button (top right) and select Edit Site Layout

  • Click My Links on your Sidebar on the left side of your website.

  • Select Add Page

  • Locate your Mystery Database page and select it.

  • Click OK, OK, Close

Queries

You will now generate a query in order to determine who the actual thief is for two different mysteries. A query is a set of criteria you specify to retrieve (sort or filter) certain data or information from a database.

First, you will need to determine the fields you will need to use. Read Mystery 1 below and then follow the directions for running a query. You will use the same Database Table to Solve both mysteries.

    • Mystery 1

    • It was a dark and stormy night. You had just fallen asleep when the phone rang and roused you back to the real world. On the other end of the phone was Chief Maurus. The Chief ordered you back to the station. A burglary had just been committed at Mrs. Lustig's house and the Chief knew that it would take your talents to solve the mystery.

    • When you arrived at Mrs. Lustig's house, you began putting the clues together. Being a master of Microsoft Access, you decided to use a database to narrow your search for the criminal. You interviewed Mrs. Lustig and her servants and found the following information that you will use to create a query, in order to find out who the thief is quickly:

      1. Mrs. Lustig said, "The thief must have worn glasses. I found a bottle of lens cleaner near the safe."

      2. The maid, Ms. Benavides stated, "I found some brown hair on the window sill where the thief entered. Does that help?"

      3. The gardener, Mrs. Moran asked, "Could it have been that short person I saw running towards the gate?"

      4. "I'm sure it was a woman," piped in Mrs. Lefrvre, the butler. "And I also noticed she was short like the gardener said."

      5. The cook, Mrs. Perttula added, "I'll never forget those cold, green eyes! She was very scary."

    • With that, you took these clues back to the office where you opened up your database that contained a table of suspects. With your skills of running queries, run a query for Mystery 1 and then paste this query, listing who the thief is for Mystery 1, onto your electronic portfolio.

      • Creating Queries

      • To create a query, complete the following steps:

      • Open your Mystery Database Table of Suspects in Microsoft Access

  • Click on Create->Query Design

  • Show Table: Select Mystery Database and click Add and then close the pop-up table.

  • Choose the field from the drop-down in each column at the bottom that you want to find information about.

  • In the first column, you need to choose Name because we need to have the name of the thief revealed. **You MUST choose NAME for BOTH Query's so that you know the name of the thief.

  • In the next column, you will then choose glasses, because the first clue was that the thief wore glasses.

  • The next clue was that the thief had brown hair. In the third column, select hair from the field drop down. In the criteria cell, type the word brown.

  • Next, we were told that the thief was short. In the fourth column, select height from the drop down, and then type short into the criteria cell.

  • The next clue given was that the sex of the thief was female. Select sex in the fifth column and then in the fifth criteria cell, and type female.

  • The last clue was that the thief had green eyes. Select eyes as the field in the sixth column and then type green in the criteria cell.

  • Running A Query

    • To run the query, click the RED, run query button on the top, left side of your toolbar. You should have only one suspect listed. This person is the thief for Mystery 1.

    • Make sure you SAVE your query.

Adding Query 1 to Your Electronic Portfolio

Click on CTRL+A (to SelectAll Records) and Copy Query 1 (CTRL+C). Log into your Mystery Database on your Electronic Portfolio and click on Edit (at the top right of the page) and Paste (CTRL+V) Query 2 underneath your Mystery 2 heading. Click Save.

** You now are the expert on running queries. Solve the second mystery below using the same Mystery Database Table.

Mystery 2

As soon as you solved the first case, another burglary was called in. You were ordered by Chief Maurus to return to the station again. A burglary had just been committed at Mrs. Rozycki's house, and the Chief knew that you could solve this crime using the same Microsoft Access database you created containing the list of suspects. When you arrived at Mrs. Rozycki's house, you began putting the clues together. You interviewed Mrs. Rozycki and her servants and found the following:

  • Mrs. Rozycki said, "The thief must have been short. There was a chair under the wall safe with muddy footprints on it."

  • The maid, Mr. Burleigh stated, "I found some blonde hair on the green rug where the thief entered. Does that help?"

  • The gardener, Mr. White asked, "Could it have been that large person I saw running toward the gate?"

  • "I'm sure it was a man," piped in Mr. Mollett the butler. "And I noticed he was large like the gardener said."

  • The cook, Mrs. Portscheller added, "I'll never forget those cold blue eyes, but I only saw them briefly before he put on his glasses."

With that, you rose and excused yourself saying, "Thank you, you have all been very helpful. I'll go back to the station and check these clues against the list of suspects in the police files. I'm sure that I will solve this crime in no time."

Run your query to determine who the thief for Mystery 2 is.

Make sure you SAVE your query.

Adding Query 2 to Your Electronic Portfolio

Click on CTRL+A to Select All Records and Copy Query 2. Log into your Mystery Database and click on Edit (at the top right of the page) and Paste (CTRL+V) Query 2 underneath your Mystery 2 heading. Click Save.