Solving a Mystery/Creating an Access Database
Databases are used to store and organize information. One example of a database is
the telephone book, which contains an enormous amount of information that is
organized for ease of use.
Assignment: You are a detective assigned to the robbery division. You will use the Microsoft
Access to help solve a mystery. 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.
Mystery 1:
It was a dark and stormy night. You had just gotten to sleep when the phone rang. 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:
a) Mrs. Rich said, "The thief must have worn glasses. I found a bottle of lens cleaner
near the safe.
b) The maid stated, "I found some brown hair on the windowsill where the thief
entered. Does that help?"
c) The gardener asked, "Could it have been that short person I saw running toward the
gate?"
d) . "I'm sure it was a woman," piped in Jeeves the butler. "And I noticed she was short
and small like the gardener said."
e) The cook added, "I'll never forget those cold green eyes! She was very scary."
To solve the crime first, you must set up the database.
1. Click on Novell-delivered Applications > Open Access
2. When the New File dialog box opens, under New click General Templates then
Blank Database > Click Ok
7th Grade Boot Camp Unit 2
1
3. A File New Database Dialog Page will open. On the file name line, type Mystery
(replacing db1). This is a very critical step. This is where you are actually saving the
file to your file!!!
4. Then click create. You will then be given several options to choose from.
5. Choose Create table in Design View> Double Click
6. Maximize this Window (In the right hand upper corner click on the box next to
the Red box with White X)
7. Type the following information in the Table under its appropriate column. To move
from field to field use Tab. The word Text will automatically fill in under Data Type
when you tab to that field.
8. Under glasses >pull down arrow and select yes/no.
7th Grade Boot Camp Unit 2
2
9. Now you have your table set up. Save your file as Mystery Database following the
directions below:
o Click File on the Menu Toolbar
o Select Save As
o Name the file Mystery Database in the Save As text
box > Save as table
10. Click OK
11. When prompted to assign a primary key, choose YES because this
will help you keep track of your records. 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.
12. IMPORTANT…Close your table by selecting the small black x next
to the Type a question for help box. Do not close out the entire file by
selecting the red box with the white X. Once you have closed the table you will be
back to the same Database Design View page.
Designing a Form: Now you will design a Form for entering information into the table
13. In the Database window, click Forms in the Objects Column on the left hand side of
the page.
14. Double click on Create Form Using Wizard (second choice)
15. 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.
16. Click next.
17. Select the column layout (first choice)
18. Click next, then select standard. Click next.
19. Title your form Suspect Database.
20. Select Modify the form’s design
7th Grade Boot Camp Unit 2
3
21. Then click finish. The form will appear in design view.
22. Save > File > Save As > Save Box Should Read: Suspect Database as Form> Click OK.
23. Close the database by again selecting the SMALL BLACK X.
24. Now you can create records for each suspect. Double click on your Form under
Object and Double Click on Suspects Database
25. For Glasses > Click inside the box.
26. ID Numbers will automatically be generated.
27. Use the Tab key to move to each field.
28. To move to the next name…click on the arrow next to the number on the bottom of
the page.
SAMPLE ONLY
Turn page to find
suspect data
7th Grade Boot Camp Unit 2
4
29. Begin entering the information in the appropriate field’s one suspect
at a time. You have 12 suspects.
Name Sex Hair Eyes Height Build Glasses
1 Anne Ville Female Black Brown Short Medium No
2 Val Entine Female Black Green Average Medium Yes
3 Bea Gone Female Blonde Blue Short Small Yes
4 Terry Cloth Female Brown Blue Short Large Yes
5 Penny Loafer Female Brown Brown Average Medium No
6 Patty Cakes Female Brown Green Short Small Yes
7 Kitty Litter Female Red Green Short Small Yes
8 Bob Forapples Male Black Blue Tall Large Yes
9 Allen Wrench Male Black Brown Short Small No
10 Dan Druff Male Black Green Short Large Yes
11 Owen Money Male Blonde Blue Short Large No
12 Rich Relative Male Brown Green Short Small Yes
30. Save file> File > Save As> Information should read Suspect Database As Form>
CLICK OK
31. Close the database by again selecting the SMALL BLACK X.
32. 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.
Creating Queries:
33. Click on Tables under Objects
34. Find you your Mystery Database table.
35. Drop down the new object menu down arrow and choose Query.
7th Grade Boot Camp Unit 2
5
Choose Design View, and then click OK.
36. Choose the field you wish to find information about. In the first field 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 Yes to indicate that yes, the thief wore glasses.
37. 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.
38. The third clue was that thief was short and small. In the third field box, choose
Height, and then type Short into the criteria cell.
39. In the forth field box, choose Build because the clue given was that the thief was
Small.
40. The next clue given was that the thief was a woman. Click in the fifth select Sex
and in the criteria cell type Female.
41. The last clue was that the thief had green eyes. Select Eyes as the field, then type
Green in the criteria cell.
42. In the last field select Name.
43. To run the query, click the run query button on the formatting toolbar. You
should have only one suspect listed.
44. Raise your hand to show the teacher the name of the suspect.
7th Grade Boot Camp Unit 2
6