Name ______

Solving a Mystery – Microsoft Access

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.

  1. Open Microsoft Access and click one time on Blank Database.
  2. On the right side, name your database Mystery.
  3. Click the Create button to begin creating your database.
  4. Click on View and select Design View.
  5. When the Save As window appears, type: Mystery.
  6. Click in the second box beneath Field Name.
  7. Type: Name of Suspect.
  8. Press the ENTER key on your keyboard
  9. Select text as the Data Type and type Name of Suspect in Description
  10. Press the Enter key and type the word gender and select text as the Data Type. For description type Gender of the Suspect.
  11. Press the Enter key and type the word hair and select text as the Data Type. For description type Hair Color of the Suspect.
  12. Press the Enter key and type the word eyes and select text as the Data Type. For description type Eye Color of the Suspect.
  13. Press the Enter key and type the word height and select text as the Data Type. For description type Height of the Suspect.
  14. Press the Enter key and type the word build and select text as the Data Type. For description type Build of the Suspect.
  15. 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.
  16. Click View, then Datasheet View. You DO want to save the table.
  17. Click in the box under the Name of Suspect column header.
  18. Type: Anne Ville
  19. Press the TAB key on your keyboard to go to the next field which is Gender.
  20. Type: Female
  21. Press the TAB key on your keyboard to go to the next field which is Hair.
  22. Type: Black
  23. Press the TAB key on your keyboard to go to the next field which is Eyes.
  24. Type: Brown
  25. Press the TAB key on your keyboard to go to the next field which is Height.
  26. Type:Short
  27. Press the TAB key on your keyboard to go to the next field which is Build.
  28. Type: Medium
  29. Press the TAB key on your keyboard to go to the next field which is Glasses.
  30. 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.
  31. 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 that line has been crossed out in the table below. You need to 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
  1. Once you have entered all of the data click the disk icon on the very top bar to save your table.

Queries: 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.

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

  1. Click the Create tab and click Query Wizard.
  2. When the New Query window appears, make sure Simple Query Wizard is selected.
  3. Click the OK button.
  4. You want to know who the person is that fits the criteria so select Name of Suspect and then the click the icon to place the Name of Suspect in the Selected Fields box.
  5. Clue Number 1:

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

  1. Clue Number 2:

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

  1. Clue Number 3:

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

  1. Clue Number 4:

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

  1. Clue Number 5:

The fifth clue given was that the thief had green eyes. Add Eyes to the Selected Fields box.

  1. Click the button.
  2. Leave the default as shown and select the button.
  3. At the next screen you will assign a title "Robbery Mystery 1" and select "Modify the query design."
  4. Click Finish to enter the modify the query section.
  5. At the bottom of the screen, type the following in the criteria row.

Glasses - Yes
Hair - Brown
Height - Short
Gender - Female
Eyes – Green

  1. Click on the Run button at the top of the screen (next to View.)
  2. You should now have only one person that matches all the criteria. Write down the criminal’s name on a piece of paper, 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.
  3. 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."

  1. Name your second query Robbery Mystery 2.
  2. Check your database and determine who the thief is.
  3. Create an arrest warrant for both criminals in Microsoft Publisher.
  4. In Publisher, 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.)
  5. Turn in your access database on your flash drive.
  6. You will also need to print both of your publisher arrest warrants and turn those in.