Finding Information Using an Access Database

Finding Information Using an Access Database
Lesson Plan
Grading Rubric

Microsoft Access is a database program that keeps track of large amounts of data. One database your company might keep is customer information about products purchased. Another database might be customer information that includes customer addresses, phone numbers, e-mail address, etc. You, being the corporate president, will create a database that will keep track of you employees. Being an online international business, you might not even know what some of your employees look like. In this activity you will compile employee information in a database and search the database using specific information about two of your employees.

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.

Click OK.
Another dialog box will open. On the file name line, type Customers (replacing db1). This is a very critical step. This is where you are actually saving the file to your folder!!!

In the File name box, type customerdb.
Choose your folder to save it to, then click create. You will then be given several options to choose from.
Choose Create table in Design View.

If prompted to assign a primary key, choose yes because this will help you keep track of your records.
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.

Now you have your table set up. Save your file as Mystery Database following the directions below:

Click File on the Menu Toolbar
Select Save As
Navigate to your student folder
Name the file Customer Database in the Save As text box

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.

Click next.
Select the column layout
Click next, then select standard.
Click next.
Title your form Suspect Database.
Click modify the forms design radio button
Then click finish. The form will appear in design view.

Make any other changes you would like to the form in Design view, either by selecting and dragging objects to display fields words or by dragging objects to different locations. To drag objects, you must select them (you will see a small hand appear which when clicked on will move the object on the page).

Save then close the database. Now you can create records for each suspect. Double click on your form tab titled Customer Database, then begin entering your information in the appropriate fields.

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	Yew
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 have met two employees that were recommended for employee of the month, however, you have forgotten their names. You do know what they look like, so you will now generate queries in order to determine who they are. 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 Employee Description 1 below, then follow the directions for completing a query below. You will then use the same Database to determine who the second employeeof the month.

Employee 1:

It was at a conference in Paris where you first met the employee. You had been introduced to this employee from the Paris office on an unofficial visit to the company. The company supervisor, Ken Ketchem, gave a raving report on their job performance. Your personal secretary, Sara Lee, was with you at the time. Wanting to refresh your memory, you call Ms. Lee into your office to gather accurate clues.

The following is information that both you and Ms. Lee recall about the employee from that meeting are:

The employee wore glasses
The employee had brown hair
The employee was short
The employee was a woman
The employee had green eyes

With that, you use these clues to determine who the employee is. You will now open you Customer Database that contains the lists of employees. With your skills of running quarries, you will now know who the employee is. Follow the steps below in how to generate a query Once you have determined who she is, open the Certificate that you created earlier and enter her name on the recipients line. Have your teacher check your answer, then print out the certificate and put it in your portfolio. You will then find the second employee of the month, place their name on the template certificate and print it for your portfolio.

Creating Queries:

To create a query, complete the following steps:

Choose design view, then click OK.
Choose the field you wish to find information about (you can double click the fields and it will automatically fill in the field for you). In the following queries you will choose glasses, because the first clue was that the employee wore glasses. After selecting the field, you will need to determine the criteria. Click in the criteria cell and put a check mark in the checkbox to indicate that yes, the employee wore glasses.
The second clue was that the employee had brown hair. Click in the second field box and select hair. In the criteria cell, type the word brown.
The third clue was that employee was short. In the third field box, choose height, and then typed short into the criteria cell.
The forth clue given was that the employee was a woman. Click in the fourth criteria cell and type woman.
The last clue was that the employee had green eyes. Select eyes as the field, then type green in the criteria cell. Your query dialog box should look like the one below:

To run the query, click the run query button on the formatting toolbar. You should have only one customer listed. Write her name on the certificate, have your teacher check the answer, then print it out to include in your portfolio.

You now are the expert on running quarries. You will now use the same criteria to find another employee.

Employee 2:

At another company visit in Athens, you were introduced to an employee that had been successful in designing a sales campaign that increased sales from the Athens company by 50 percent. You, along with Sara Lee, interviewed the employee, but again both of you could not remember the employees name. Since you had been successful in finding the first employee using clues that both Ms. Lee and you recalled, you decide to make a list of clues on the second employee. What you both recalled are:

The employee was short
The employee had blond hair
The employee had been a large person
The employee was a man
The employee had blue eyes and wore glasses

Use the clues above to determine the second employees name. Open the certificate template and put his name on the recipients line. Have your teacher check your answer then print the certificate for your portfolio.