Access 2007 Instructions

Access 2003 Instructions

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.

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:

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.

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

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

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.

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. 

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:

---

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.