Access Mystery

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.

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.

Click the Datasheet tab.

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

When the Save As window appears, type: Mystery.

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

Click in the second box beneath Field Name.

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:

Select text. as the Data Type and type Name of Suspect in Description: Or you may set up Combo Boxes

Press the Enter key and type the word gender and select text as the Data Type. For description type Gender of the Suspect. Combo Box - Male;Female

Press the Enter key and type the word hair and select text as the Data Type. For description type Hair Color of the Suspect. Combo Box - Black;Brown;Blond;Red

Press the Enter key and type the word eyes and select text as the Data Type. For description type Eye Color of the Suspect. Combo Box - Blue;Brown;Green

Press the Enter key and type the word height and select text as the Data Type. For description type Height of the Suspect. Combo Box - Tall;Short;Average

Press the Enter key and type the word build and select text as the Data Type. For description type Build of the Suspect. Combo Box - Small;Medium;Large

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.

Your Access table should now look like this:

Create Records

In the Ribbon, click View, then Datasheet View.

When the alert window appears, click the Yes button.

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

Type:

Anne Ville

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

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.

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.

Once you have entered all your data click the 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.

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

Then click the 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 icon to place the Name of Suspect in the Selected Fields box.

Your query should now look like this:

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.

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.

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:

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

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

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

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

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.

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.

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:

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.

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