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

Access 2007 Instructions

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.

Open Access (Go to start, choose programs, and then Microsoft Access). 

At the Getting Started with Microsoft Office Access dialog box select Blank Database.

New Blank Database

Next you will 1. name your database, 2. browse to your student folder where you will save it, then 3. click the create button to begin creating your database.

Save Database

Click the Datasheet tab.

Datasheet Tab

In the Ribbon, click View. When the menu appears, click Design View.

Design View

When the Save As window appears, type: Mystery.

Mystery Table

Then click the OK button to close the Save As dialog box.

Click in the second box beneath Field Name.

First Field

Type: Name of Suspect. We will not use just the text name because that is a reserved word used in Access and it could generate errors when generating our queries later on.

Press the ENTER key on your keyboard. The table should look like this:

Name of Suspect

Select text. as the Data Type and type Name of Suspect in Description:

Access Field

Press the Enter key and type the word sex and select text as the Data Type. For description type Sex of the Suspect.

Press the Enter key and type the word hair and select text as the Data Type. For description type Hair Color of the Suspect.

Press the Enter key and type the word eyes and select text as the Data Type. For description type Eye Color of the Suspect.

Press the Enter key and type the word height and select text as the Data Type. For description type Height of the Suspect.

Press the Enter key and type the word build and select text as the Data Type. For description type Build of the Suspect.

Press the Enter key and type the word glasses and select Yes/No as the Data Type. For description type Whether the Suspect Wore Glasses/Place a check for yes.

Data Type

Your Access table should now look like this:

Access Table

Create Records

In the Ribbon, click View, then Datasheet View.

Database View

When the alert window appears, click the Yes button.

Save Table

Click in the box under the Name of Suspect column header.

Suspect Name

Type:

Anne Ville

Press the TAB key on your keyboard to go to the next field which is Sex.

Type:

Female

Press the TAB key on your keyboard to go to the next field which is Hair.

Type:

Black

Press the TAB key on your keyboard to go to the next field which is Eyes.

Type:

Brown

Press the TAB key on your keyboard to go to the next field which is Height.

Type:

Short

Press the TAB key on your keyboard to go to the next field which is Build.

Type:

Medium

Press the TAB key on your keyboard to go to the next field which is Glasses.

You will not place a check in the box because Anne Ville, suspect one, does not wear glasses. If the suspect wears glasses you would click in the box to place a check.

Your table should now look like the one below. Notice there is no mark in the glasses field because the suspect did not wear glasses.

First Field Entered

Now using the same process you used above for entering Anne Ville's data enter the remaining suspect data listed below. Remember you have already entered the data for Anne Ville so you would continue on with the second suspect data which is Kitty Litter.

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 Yes
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

Once you have entered all your data click the Save Icon icon on the Title Bar to save your table.

Queries:

What's a query? A query is a way to get specific information from the database. Essentially, it's a question. You will now generate queries in order to determine who the actual thief is.

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:

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

The maid stated, "I found some brown hair on the windowsill where the thief entered. Does that help?"

The gardener asked, "Could it have been that short person I saw running toward the gate?"

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

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.

Creating Queries:

Click the Create tab. In the Ribbon, click Query Wizard.

Querie Wizard

When the New Query window appears, make sure Simple Query Wizard is selected.

Simple Querie Wizard

Then click the OK button.

Now we need to look at the information to determine what fields we want to include.

First you want to know who the person is that fits the criteria so we will first select Name of Suspect and then the click the Select icon to place the Name of Suspect in the Selected Fields box.

Name

Your query should now look like this:

Suspect

Clue Number 1:

The first clue is that the suspect wore glasses. Select glasses and then the click the Select icon to place the glasses field in the Selected Fields box.

Clue Number 2:

The second clue was that the thief had brown hair. Click on the hair field and then click the Select icon to add it to the Selected Fields box.

Clue Number 3:

The third clue was that thief was short. Add Height to the Selected Fields box.

Clue Number 4:

The forth clue given was that the thief was a woman. Add Sex to the Selected Fields box.

Clue Number 5:

The fifth clue given was that the thief had green eyes. Add Eyes to the Selected Fields box. Your query dialog box should look like the one below:

Feilds Selected

Click the Next button. You will then see the screen below. Leave the default as shown and select the Next button.

Simple Queri Wizard

At the next screen you will assign a title "Robbery Mystery 1" and select "Modify the query design."

Simple Query

Click Finish to enter the modify the query section. Now you will type in your criteria. The criteria is listed below and the graphic below the criteria shows where you will type in this data:

Glasses - Yes
Hair - Brown
Height - Short
Sex - Female
Eyes - Green

Query Modified

Now to run your query all you need to do is click on the Run button shown below:

Run Query

You should now have only one person that matches all the criteria. My query is shown below but I have hidden the suspect's name. Write down your name because you are actually going to create an arrest warrant for this criminal but first you will need to create another query with a different set of criteria.

Suspect One Query

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."

For your second query name it Robbery Mystery 2.

Check your database and determine who the thief is. Now you will create an arrest warrant for both criminals.

Creating an Arrest Warrant in Microsoft Publisher:

Open up Publisher and select Invitation layout and create an arrest warrant for your two criminals. Make sure you include the two names that were generated in your two queries.

Arrest Warrant

You will turn in your access database along with both of your publisher arrest warrants.

Divider

Access 2003 Instructions

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 Yes
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