Solving a Mystery/Creating an Access Database
Created by Patricia Janann Nicholson

Adapted by Beth Meyer

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, Go to the Menu Bar and Choose File choose New. This screen will appear on the right side choose Blank Database.

·  Another dialog box will open. On the file name line, type your first name, first initial of your last name and the name of the file it will look something like this (JohnS-Mystery) (replacing db1). This is a very critical step. This is where you are actually saving the file to your folder!!!

·  Choose your folder to save it to, then click Create on the right side of the dialog box.

·  A new dialog box will open up Double Click on Create table in Design view.

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

When you click Yes to save your Table name the Table with your first name, first initial and the name of the table it will look something like this (JohnS-Suspect Info) then click OK

This new dialog box will appear click Yes. The Primary Key will automatically become the first record and will insert a number automatically.

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 below. You will see that all the fields automatically appear in the right column.

·  Click next.

·  Select the column layout

·  Click next, then select a form type you like

·  Click next.

·  Title your form Suspect Info.

·  Click modify the forms design radio button

·  Click on the title ID press the delete key, Click on the other ID field press the delete key

·  You can move the field where you like. To drag objects, you must click on them (you will see a small hand appear which is used to move the object on the page).

·  When you have the fields moved click on the View button which is circled on the dialog box.

·  This Screen will appear where you will begin typing in the suspect’s information.

Enter the following information about each of the suspects. Use the Tab key to move from field to field. If your Suspect wears glasses you have to use your mouse to check the box next to the field name Glasses.

Name / Gender / Hair / Eyes / Height / Build / Glasses
Kitty Litter / Female / Black / Brown / Short / Small / No
Bob Forapples / Male / Black / Blue / Tall / Large / Yes
Bea Gone / Female / Blonde / Blue / Short / Small / No
Starr Lett / Female / Blonde / Blue / Tall / Small / No
Bob Sledd / Male / Black / Green / Average / Large / Yes
Penny Loafer / Female / Brown / Brown / Average / Medium / No
Dan Druff / Male / Black / Green / Short / Large / No
Tim Burr / Male / Black / Green / Tall / Large / No
Patty Cakes / Female / Brown / Brown / Short / Small / Yes
Harry Knuckles / Male / Blonde / Blue / Short / Large / Yes
Oscar Award / Male / Blonde / Green / Average / Medium / Yes
Candy Graham / Female / Brown / Green / Average / Small / No
Jack Enjill / Male / Blonde / Green / Short / Medium / Yes
Rich Relative / Male / Brown / Brown / Short / Small / No
Frank Furter / Male / Red / Green / Average / Large / Yes
Sherry Soda / Female / Brown / Green / Short / Small / Yes
Val Entine / Female / Red / Blue / Average / Medium / No
Owen Money / Male / Blonde / Blue / Short / Large / No
Barry Sweet / Male / Blonde / Brown / Average / Medium / Yes
Tom Katt / Male / Red / Green / Tall / Large / Yes

Now that you have entered all of the suspects into your database you will need to exit this screen you will do that by clicking on the circled X in the right upper corner.

This new dialog box will appear Click Yes to save your information.

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. 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 woke you with it’s rendition of “Take me out to the ball game”. At the other end was Chief Ketchem. The chief told you a burglary had just been committed at Mrs. Rich's house and that only you would be able to solve the mystery.

Upon arrival at Mrs. Rich's house, you began interviewing everyone in the household. While talking to Mrs. Rich and her servants you learned the following information about the suspect.

  1. Mrs. Rich said, "The thief must have worn glasses. I found a bottle of lens cleaner near the safe.
  1. The maid stated, "I found some brown hair on the windowsill where the thief entered. Does that help?"
  1. The gardener asked, "Could it have been that short person I saw running toward the gate?"
  1. "I'm sure it was a woman," piped in Jeeves the butler. "and I noticed she was short like the gardener said."
  1. The cook added, "I'll never forget those cold green eyes! She was very scary."

Thanking Mrs. Rich and her servants for the clues you headed back to your office. On the drive you decided to use your database to narrow the possible suspects. But, in order to do this you needed to create a query that will pull out the most likely suspect. Follow the steps below to create your query.

Creating a Query:

Click on the Queries option in the Objects Column and then double click on Create query by using wizard.

The following dialog box will appear click on the double arrows to select all of the fields. Click the Next Arrow at the bottom.

A new dialog box will appear just click Next.

When the new dialog box appears click on Modify the query design and then Click Finish

This screen will appear:

Now we can type in the clues we received from Mrs. Rich and her servants. Below is the conversation again.

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

You will need to type the clues into the Criteria area of each field. The first clue is shown below:

Now fill in the other clues in the proper Criteria areas of each field. When you have completed entering the suspects criteria click on the and enter the suspect’s name on the rubric.

When you have written down the guilty person you will need to prepare for the second case click the Design View button in the left hand corner.

Now read the case below and fill in the new clues in the Criteria area.

Mystery Database: Case 2

No longer had you solved that case, another burglary case was called in. You were ordered by Chief Ketchum to go to Mrs. Elite’s home because the chief knew that you would be able to solve the crime with your Microsoft Access database. Upon arrival at Mrs. Elite's house, you began interviewing everyone in the household. While talking to Mrs. Elite and her servants you learned the following information about the suspect.

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

Follow the steps for Case 1 to complete Case 2. Write your answer on the rubric.

Solving a Mystery/Creating an Access Database
Name:
Hour:
Case 1 Suspect:
Case 2 Suspect:
Table includes all fields. / 2
Correct data type is used for all fields. / 1
Description is present for all fields. / 2
Spelling Errors 1 point subtracted for each error / 10
Form created / 1
All records are included in table / 20
Query one has the correct suspect / 2
Query two has the correct suspect / 2
Total / 40
Point Value for Grade
40-36 / A
35-32 / B
31-28 / C
27-24 / D
23 - 0 / F