Solving a Mystery/Creating an Access Database
Created by Patricia Janann Nicholson
Lesson Plan
Grading Rubric

You are a detective assigned to the robbery division. You will use the Microsoft Access to help solve two mysteries. You will be given a collection of information about suspects that you will include in the 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.

Directions:

  • Open Access (Go to start, choose programs, and then Microsoft Access). 
  • When the dialog box opens, choose blank access database.

  • Click OK. 
  • Another dialog box will open.  On the file name line, type Mysterydb (replacing db1). This is a very critical step. This is where you are actually saving the file to your folder!!!

  • In the File name box, type mystery.
  • Choose your folder to save it to, then click create.  You will then be given several options to choose from.  
  • Choose Create table in Design View.

  • If prompted to assign a  primary key, choose yes because this will help you keep track of your records.
  • Type the following information in the Table under its appropriate column.  To set your field size, click on data type and box will appear at the bottom of your field box.  The field size is automatically set at 50.  You can leave the field size at 50, or adjust the field size accordingly.

 

Now you have your table set up. Save your file as Mystery Database following the directions below:

  • Click File on the Menu Toolbar
  • Select Save As
  • Navigate to your student folder
  • Name the file Mystery Database in the Save As text box

Close your table.  Your primary key will be number of the record you are entering.  It is automatically named as your first field entry name. After saving it, close the table.

Designing a Form:

Now you will design a Form for entering information into the table. 

  • In the Database window, click Forms in the Objects Column.
  • Double click on Create Form Using Wizard.
  • Select all of the fields by clicking the button with two arrows, circled in blue below.  You will see that all the fields automatically appear in the right column. 

  • Click next.
  • Select the column layout
  • Click next, then select standard.
  • Click next.
  • Title your form Suspect Database.
  • Click modify the forms design radio button
  • Then click finish. The form will appear in design view.

Make any other changes you would like to the form in Design view, either by selecting and dragging objects to display fields words or by dragging objects to different locations. To drag objects, you must select them (you will see a small hand appear which when clicked on will move the object on the page).

Save then close the database. Now you can create records for each suspect. Double click on your form tab titled Suspects Database, then begin entering your information in the appropriate fields.

Name
Sex
Hair
Eyes
Height
Build
Glasses
Anne Ville Female Black Brown Short Medium No
Kitty Litter Female Black Brown Short Small No
Chris Ko Female Black Brown Tall Small No
Ethyl Gass Female Black Green Average Medium Yes
Bea Gone Female Blond Blue Short Small No
Cora Gated Female Blond Blue Short Small Yes
Starr Lett Female Blond Blue Tall Small No
Carrie Meback Female Blond Blue Tall Small Yes
Terry Cloth Female Brown Blue Short Large Yes
Penny Loafer Female Brown Brown Average Medium No
Mary Mee Female Brown Brown Average Medium Yes
Patty Cakes Female Brown Brown Short Small Yes
Candy Graham Female Brown Green Average Small No
Sherry Wine Female Brown Green Short Small Yew
Val Entine Female Red Blue Average Medium No
Patty O'Furnature Female Red Green Short Small Yes
Peri Winkle Female Red Green Tall Small No
Kelly Green Female Red Green Tall Small Yes
Bob Forapples Male Black Blue Tall Large Yes
Allen Wrench Male Black Brown Short Small No
Jerry Mander Male Black Brown Tall Small Yes
Bob Sledd Male Black Green Average Large Yes
Dan Druff Male Black Green Short Large No
Phil O'Dendron Male Black Green Tall Medium No
Tim Burr Male Black Green Tall Large No
Harry Knuckles Male Blond Blue Short Large Yes
Steve Adore Male Blond Blue Short Small No
Bill Payer Male Blond Blue Short Small Yes
Owen Money Male Blond Blue Short Large No
Barry Medeep Male Blond Brown Average Medium Yes
Oscar Award Male Blond Green Average Medium Yes
Jack Enjill Male Blond Green Short Medium Yes
Jay Bird Male Brown Brown Average Medium Yes
Pete Moss Male Brown Brown Short Large Yes
Rich Relative Male Brown Brown Short Small No
Ty Ping Male Brown Brown Short Small Yes
Mark Papers Male Brown Brown Tall Medium No
Paur Bearer Male Red Brown Tall Medium No
Frank Furter Male Red Green Average Large Yes
Tom Katt Male Red Green Tall Large Yes

You will now generate queries in order to determine who the actual thief is. A query is a set of criteria you specify to retrieve certain data from a database.

First, you will need to determine the fields you will need to use. Read Mystery Number 1 below, then follow the directions for completing a query below. You will then use the same Database to Solve the Second Mystery.

Mystery 1:

It was a dark and stormy night. You had just gotten to sleep when the phone roused you back to the real world. At the other end was Chief Ketchem. The chief ordered you back to the station. A burglary had just been committed at Mrs. Rich's house and the chief knew that it would take your talents to solve the mystery.

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

  1. Mrs. Rich said, "The thief must have worn glasses. I found a bottle of lens cleaner near the safe.
  2. The maid stated, "I found some brown hair on the windowsill where the thief entered. Does that help?"
  3. The gardener asked, "Could it have been that short person I saw running toward the gate?"
  4. . "I'm sure it was a woman," piped in Jeeves the butler. "And I noticed she was short like the gardener said."
  5. The cook 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 entered opened your database that contained the lists of suspects. With your skills of running quarries, list the thief below:

Creating Queries:

To create a query, complete the following steps:

Open your Mystery Database table.
Drop down the new object menu and choose new query.

  • Choose design view, then click OK.
  • Choose the field you wish to find information about (you can double click the fields and it will automatically fill in the field for you). In the following queries you will choose glasses, because the first clue was that the thief wore glasses. After selecting the field, you will need to determine the criteria. Click in the criteria cell and put a check mark in the checkbox to indicate that yes, the thief wore glasses.
  • The second clue was that the thief had brown hair. Click in the second field box and select hair. In the criteria cell, type the word brown.
  • The third clue was that thief was short. In the third field box, choose height, and then typed short into the criteria cell.
  • The forth clue given was that the thief was a woman. Click in the fourth criteria cell and type woman.
  • The last clue was that the thief had green eyes. Select eyes as the field, then type green in the criteria cell. Your query dialog box should look like the one below:

  • To run the query, click the run query button on the formatting toolbar. You should have only one suspect listed. Write the answer to mystery 1 on a separate piece of paper to turn into your teacher.

You now are the expert on running quarries. Solve the second mystery below using the same Mystery Database and include your answer on the same piece of paper that you used for the mystery above.

Mystery Database: Case 2

No longer had you solved that case, another burglary case was called in. You were ordered by Chief Ketchum to return to the station. A burglary had just been committed at Mrs. Elite's house, and the chief knew that you could solve this crime using the Microsoft Access database you created which contained a list of suspects. When you arrived at Mrs. Elite's house, you began putting the clues together. You interviewed Mrs. Elite and her servants and found the following:

  1. Mrs. Elite said, "The thief must have been short. There was a chair under the wall safe with muddy footprints on it."
  2. The maid stated, "I found some blond hair on the green rug where the thief entered. Does that help?"
  3. The gardener asked, "Could it have been that large person I saw running toward the gate?"
  4. "I'm sure it was a man," piped in Alfred the butler. "And I noticed he was large like the gardener said."
  5. The cook 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."

Check your database and determine who the thief is. Write the name below:

Nicholson's Lesson Plan Blues