Exercise #2

Objective - The objective of this exercise is to create a table in datasheet view

Your parents are not satisfied that you worry about your grades enough. They want you to keep a record of your test grades in four of your school subjects. Use Microsoft Access to create a table of your grades.

1) Open Microsoft Access

2) Choose New from the pull down File menu

3) Choose the option for a Blank Database

4) Name the file ‘My Grade Report’

5) Create a new table by selecting the new button under the Tables tab

and then Datasheet view.

6) Set up 4 fields: Math, History, English, and Science

7) In order to name these 4 fields, right click the gray area at the

top of each column labeled Field # and choose rename field at the

bottom. Enter the new names.

8) Enter in the information for these fields below in each cell

Math: 68, 74, 83, 76

History: 96, 84, 88, 77

English: 81, 45, 92, 77

Science: 91, 93, 89, 99

9) Save and call the table ‘My Grade Report’

10) Let Microsoft Access create a primary key

Back to the Table of Contents

Move on to Exercise #3

Exercise #3

Information for Your Employer

Objective -The intention of this exercise is to familiarize the student with Access, and introduce the student into data entry.

Solution: Answer to Project 03

You are required by your new employer to submit some information about yourself in an organized format. Let’s begin by making our first table.

-Open access, start with a blank database. Call the file “My First Database”. Click Create.

-Double click “create table by entering data” to create the table by entering data

-Place arrow above first column, where is says “field 1,” click. This should highlight the entire column

-Go to format tab on the menu, click rename column

-Call this column “Name”

-Do the same with the second column. Call the second column “Address”.

-Do the same with the third column. Call the third column “Phone”.

-In the first cell under name, type your full name.

-In the first cell under address, type your address

-In the first cell under phone type your phone number.

-Adjust the widths of each of the columns by double clicking the line that separates the two columns (your cursor should look like a double arrow).

-Save the table as “Table One”

-Click “no” when asked to define a primary key

Back to the Table of Contents

Move on to Exercise #4

Exercise #4

Creating a Table in Design Mode

Objective - The goal of this exercise is to use design mode to create a table.

Solution: Answer to Project 04

Create a table in Design Mode

Start Access

Select Blank Access Database

Select a location for the database to be saved

Name the database Table 1

Double click Create a Table in Design View to select that option

Click on the first row

Enter field names

FirstName

LastName

Gender

BirthDate

Specify Data Types

Text for FirstName, LastName, Gender

Date/Time for BirthDate

Insert a row into the table after FirstName

Click on the LastName row

Right click and select insert rows

Type MiddleName

Save as Table 1

Back to the Table of Contents

Move on to Exercise #5

Exercise #5

Guests Table

Objective - This exercise will teach you how to make the first steps in creating an Access database using Table Wizard.

Solution: Answer to Project 05

Choose to open a blank access database.
Entitle the new database, Tablenumber1.
Highlight Create Table in Design view and the press NEW command.
Choose Highlight Table Wizard and press Enter.
Choose Personal, and then Guests under Sample Tables.
Under Sample Fields, highlight First Name and click on the > icon to the right. Do the same for Last name, City, and State.
Highlight First Name under the Fields in my new table. Click the Rename Field command and type, Guest’s First Name. Click OK. Change the Last Name Field to Guest’s Last Name.
Press Next, Choose Yes for set a primary key for me.
Press Next, leave this page the way it is, Press Next, and bubble in Modify the Table Design. Click Finish
Save to disk and give to your instructor.

Back to the Table of Contents

Move on to Exercise #6

Exercise #6

Exercise Log

Solution: Answer to Project 06

Objective - The goal of this exercise is to be able to enter data into a table and save it.

Summer is approaching and you want to start to get in shape for the nice weather. You decide to start going to the gym a few times a week. In order to stay disciplined you decide to keep careful records of what you do everyday. Enter in this sample data for the first week of your new workout. Print out a copy of the table to submit to your instructor, and save it to disk. Title it Exercise log.

-Click on New in the File menu

-Select Blank Database

-Name it ‘Exercise Log’

-Create a new table by selection ’Create table by entering data’

-Set up the following fields: Workout Date, Exercise Type, Time Exercised, Distance Traveled, Hours Sleep, and Notes

-To set up these fields, click the gray area at the top of each column and enter in the appropriate names

-Enter in the following information under these fields

-Save this and call the table Exercise Log

-Allow the program to create a primary key for you

Workout Date	Exercise Type	Time Exercised	Distance Traveled	Hours Sleep	Notes
3/10/2003	Running	30 minutes	3 miles	8
3/11/2003	Bicep Curls	5 minutes		7	30 lbs
3/11/2003	Tricep Extension	8 minutes		7	100 lbs
3/11/2003	Precher Curls	9 minutes		7	60 lbs
3/11/2003	Hammerhead Curls	7 minutes		7	30 lbs
3/14/2003	Running	40 minutes	3.5 miles	8.5
3/15/2003	Bench Press	12 minutes		8	145 lbs
3/15/2003	Flys	10 minutes		8	45 lbs
3/15/2003	Pec Deck	8 minutes		8	100 lbs
3/15/2003	Cable Cross	12 minutes		8	65 lbs

Back to the Table of Contents

Move on to Exercise #7

Exercise #7

Address Book

Solution: Answer to Project 07

Objective - The objective of this exercise is to create and change a blank database with datasheet view.

You are constantly losing your friends numbers and addresses so you decide to make an address book using a Microsoft Access Table so you can have the information saved neatly on your computer.

1) Open Microsoft Access

2) Choose New from the pull down File menu

3) Choose the option for a Blank Database

4) Name the file ‘My Address Book’

5) Create a new table by selecting the new button under the Tables tab

and then click Datasheet view.

6) Set up seven fields: First name, Last name, Phone Number, Address,

City, State, and Postal code

7) In order to name these 7 fields, right click the gray area at the

top of each column labeled Field # and choose rename field at the

bottom. Enter the new names.

8) Enter in the information for these fields below them in each cell

9) Save and call the table My Address Book

10) Let Microsoft Access create a primary key

Back to the Table of Contents

Move on to Exercise #8

Exercise #8

Party Management Database

Solution: Answer to Project 08

Objective - The purpose of this exercise is to tech the student how to use the Wizard effectively. Also, the student will learn how to utilize Access for everyday needs.

Your Employer has asked you to begin a comprehensive database for an event she is planning. She tells you that three companies are attending the event, and she would like you to add them to the attendee list. Use Access to assist you in your quest.

-Open Access, select “Access Database Wizards, Pages, and Products”

-Click “ok”

-On the “Databases” tab

-Select “Event Management”

-Click “Ok”

-For the File Name, type “The Big Party”

-Click “Create”

-The Database Wizard should appear. Click “Next.”

-Click “Next” again

-Chose style “Blends”

-Click “Next”

-Chose style “Bold”

-Click “Next”

-Call the database “My Big Party”

-Click the box next to the text “Yes, I’d like to include a picture”

-Click the box that says “Picture”

-Chose an appropriate picture from your files

-Click “Next”

-Click “Finish”

-Wait a few moments for the Wizard to load all of the information.

-Enter the following information for your company:

COMPANY NAME: Fink Inc.

ADDRESS: 123 Hartswick Terrace

CITY: New Hardgot

STATE: CT

POSTAL CODE: 45693

COUNTRY: USA

PHONE NUMBER: (226) 987-5632

FAX: (708) 957-5848

-Leave all other fields blank, and close the window

-On the main switchboard, click “Enter/View Attendees”

-Add “COMPANY A,” “COMPANY B,” and “COMPANY C” as attendees. There should be three records.

-Close the Window, open “the Big Party: Database” window that is minimized at the bottom of the Access window

-Double click “Switchboard”

-Click the box next to “Preview Reports”

-Click the box next to “Preview Attendee Listing”

-Close all Windows

Back to the Table of Contents

Move on to Exercise #9

Exercise #9

Creating a table using Table Wizard

Solution: Answer to Project 09

Objective - The purpose of this exercise is to teach you how to utilize Access's table Wizard feature.

Create a table using Table Wizard

Start Access

Select Blank Access Database

Select a location for the database to be saved

Name the database address table

Double click Create a Table by using wizard to select that option

Select personal

Select addresses under sample tables

For sample fields select by pressing the > key the following fields:

AddressID

FirstName

LastName

Address

City

StateOrProvince

PostalCode

Click Finish

Enter your information

Save the table as address table

Back to the Table of Contents

Move on to Exercise #10

Exercise #10

Changing Field Properites

Objective - This exercise will teach you how to format your database fields by creating and changing field properties.

Solution: Answer to Project 10

For this Exercise you must refer to the work done in Exercise #5

Open the Exercise1 file from Exercise 1 entitles Mailing List.
Go to View, and select Design View.
Highlight the Last Name Field. Using the top tool bar, select Make it the Primary key by choosing the “small key” icon.
Highlight the Mailing List ID field. Right click and choose Delete Rows.
Click the Empty Field under State. Type in Gender. Press Enter. Place the cursor in the Gender box. In the Validation Rule box, type “M” or “F”.
Highlight Last Name, under the Field Properties, switch the Required box from No to Yes. Do the same for First Name.
Save the File. Then go to View, and choose Datasheet View. Enter the First Name as John. The Last Name as Smith. The City as College Park. The State as Maryland. The Gender as M.
Print this and give to your instructor.

Back to the Table of Contents

Move on to Exercise #11

Exercise #11

Contact Database

Solution: Answer to Project 11

Objective - The goal of this exercise is to show you how to create a report via Access's provided templates.

You are interning at a Public Relations Firm in New York in the Communications Department. Your supervisor, Kimberly Jones has just taken on a new account. She is far too busy to update her contacts with all the new people she will be working with. Before she leaves for Los Angeles for a conference she drops off ten business cards for you to add. She wants to be able to distribute a contact list internally so that the others working on the account will have everyone’s information. Create a Contact Management Report in Access that includes the ten contacts she gave to you. Save it to disk and print out a copy of the report to turn into your professor.

Start by Opening Access

Open the File menu and Click New

Select General Templates

Click the Databases Tab

Select Contact Management

Enter in the File name field ‘Peterson Account Contact List’ and press Create

Keep the default Field types

Select Industrial screen displays

Pick the Casual report style

Title the database ‘Peterson Account Contact List’

Once the Wizard loads, click on Enter/View Contacts

Enter the ten contacts Kimberly gave to you:

Back to the Table of Contents

Move on to Exercise #12

Exercise #12

Household Inventory

Solution: Answer to Project 12

Objective - The goal of this exercise is to learn how to navigate a switchboard, preview a report, and use a template.

The weatherman in your area said a horrible storm is approaching and that people will need to be indoors for about a week most likely. Your family and you decide to make a household inventory using Microsoft Access to figure out if you need more food before the storm hits.

1) Open Microsoft Access

2) Choose New Database from the pull down File menu

3) Choose the Databases tab and find the template labeled Household

inventory. Highlight it and click OK.

4) Name the file ‘My Household Inventory’ and click Create

5) The Wizard will pop up, just click finish.

6) In separate records we are going to establish what food you have

in your house.

7) The Main switchboard will pop up. Click on Enter/View Household

Inventory. Enter the following information into a record with the Category Food. Enter the description as the type of food it is, notes are how many items are left, and the location is either the garage or the kitchen. Enter the information into separate records.

Category: Food

Canned Goods: left: 15 loc: garage

Fruit: left: 5 loc: kitchen

Pasta Boxes: left: 2 loc: kitchen

Frozen Dinners: left: 10 loc: garage

Deserts: left: 12 loc: garage

6) Click on Preview Reports on the Main Switchboard. Print for your professor the inventory by category

report and the inventory by room report. Save your work and exit Microsoft access.

Back to the Table of Contents

Move on to Exercise #13

Exercise #13

Superbowl Database

Solution: Answer to Project 13

Objective - The purpose pf this exercise is for the student to become practiced in making a table presentable to a teacher, professor, or employer. The student will receive practice creating an option group so that their work is more easily read by others.

You are applying for a job with the NFL. To test your knowledge of football, as well as your computer skills, they have asked you to create a table of all the Superbowls and a brief list of information pertaining to each. You want to impress them of your knowledge of Access by making a report that is easy to read and pleasing to the eye.

-Open Access, click to open the Superbowl database.

-Under the “Objects” heading, click “Reports”

-Double click “Create a Report Using Wizard”

-Click the arrow to add all the “available fields” to the “selected fields” except for “Winner”

-Continue clicking “Next” until the wizard asks you for a title. Call the report “Awesome Superbowl!!!”

-Select “Modify”

-Click “Next”

-Resize the heading and details boxes so that all headings are readable, and all details line up with their headings

-Click the “Toolbox” button in the toolbar (the picture of a hammer and wrench)

-Click the Option Group Button on the toolbox toolbar. The mouse changes to a form in which you can create a textbox.

-Click and drag in the form where you want the option group to go, preferably at the end of the row.

- You should see the option group Wizard.

- Under “Label Names”, enter AFC as the first option and NFC as the second.

-AFC and NFC will be assigned values 1 and 2 respectively.

-Store the Value in the field “Winner”

-Click Next

-Make sure the option button is selected as the type of control

-Chose the same, “bold” style

-Click Finish

-Click and drag the option group to the end of the row

-Resize the row so it fits across one page

-Point to the border of the option group on the form, click the right mouse button to display a shortcut menu, and click properties. Click the all tab. Change the name to “Winner”

-Save

-Print the Report by clicking the “Print” icon on the toolbar

-Close

Back to the Table of Contents

Move on to Exercise #14

Exercise #14

Creating and Modifying a Table

Solution: Answer to Project 14

Objective - The objective of this exercise is to practice creating and modifying a table.

Create a table using Table Wizard

Name the database students

Select Business

Select Students for sample table

Select the following sample fields:

StudentID

FirstName

LastName

ParentsNames

Address

City

StateOrProvince

PostalCode

PhoneNumber

StudentNumber

Rename Fields

Rename the field StateOrProvince

Right click the StateOrProvince column and select rename column

Change the field name to state

Change StudentNumber to SSN using the same technique as above

Enter the information for 5 fictional people

Define a Primary Key

Switch to Design View

Click the icon located on the top left of the screen

Right click next to SSN and select primary key

A small key will appear next to SSN indicating that you have set that as the Primary Key

Remove a field

Right click on StudentID

Select Delete rows to delete this field

A box may appear asking you if you are sure you would like to delete the field, click yes

Save the table as students

Back to the Table of Contents

Move on to Exercise #15

Exercise #15

Creating a Form From Existing Data

Objective - This exercise will teach you how to enter data into your database in different forms.

Solution: Answer to Project 15

For this exercise you must refer back to the work done in Exercise #’s 5 and 10.

Highlight the Mailing List table from the previous Exercise
Choose Forms from the window on the left and then highlight Create form in design view.
Click the New command above.
Select Form Wizard, and pull up guests from the arrow down key below.
Select all five field categories using the > icon.
Remove the Guest ID field using the < icon.
Press next and then choose the Columnar layout and press next again.
Choose Industrial on the next screen and press next again.
For the title of the form, type in General Guest List.
When the next window opens, type in the following names and information pressing the right arrow and star icon after each:

Henry Roberts

New York, New York

Sally McCarthy

Raleigh, North Carolina

Peter Howard

Los Angeles, California

Mike Jones

Denver, Colorado

Go to View and choose Data Sheet view.
Print and give this page to your instructor.

Back to the Table of Contents

Move on to Exercise #16

Exercise #16

Group

Accounting Class Grades Database

Solution: Answer to Project 16

Objective - This exercise will teach you how to create relational databases.

This is a group project, meant for a small group of 2 or 3 people. You are going to create a record keeping system for the grades for your Accounting class. You will need to create two tables of data that are brought together in a visual form.

Click New in the File Menu

Select Blank Database

Enter the File name as ‘Spring 2003 BMGT220 Grades’

Click Create

Click ‘Start a new Table by Entering Data’

Create Fields for Social Security Number, First name, Last name, ClassID, MajorID, Midterm, Final, and Final Grade. Enter in the following data:

SSN#	First name	Last name	ClassID	MajorID	Midterm	Final	Final Grade
145-67-9384	Joe	Glowacki	C01	C01	77	85	B
236-74-8267	Bill	Humboldt	C02	C01	45	69	F
234-55-9383	Tracy	Oxford	C01	C02	83	81	B
232-44-8127	Johnny	Perez	C02	C03	62	90	B
331-54-9821	Sean	Vivivino	C02	C04	88	77	B
110-23-0192	Whitney	Potter	C02	C04	78	80	C
020-54-0921	Danny	Stewart	C02	C05	93	90	A
010-02-9302	Samantha	Young	C02	C06	80	85	B

Create a New table the same way and enter in the following data for your Class IDs

ClassID	Class
C01	Freshman
C02	Sophomore

Create another table the same way and enter the following data for your MajorIDs

MajorID	Major
C01	General Business
C02	Undecided
C03	Accounting
C04	Finance
C05	Marketing
C06	Other

You need to now set up your relationships so the class and majors view properly in the Form you will create.

In the Tools menu, select Relationships…

In the Relationships menu, click Show table

In the tables menu, Add all of your Tables

In the window for your Major Table, drag MajorID over to MajorID in your Grades table.

Check off to Enforce Referential Integrity and click OK

Do the same for ClassID in your Class Table and ClassID in your Grades Table

Save this and close the Relationships window

Now you want to create a form by clicking ‘Create form using wizard’

You want to include the following fields in your form: Class from your Class table, SSN#, First name, Last Name, Midterm, Final, and Final Grade from your Grades Table, and Major from your Major Table

Select to view your data by Grades

Select the tabular Layout for your Form

Select the Stone style

Title your Form ‘BMGT220 Spring 2003 Grades Report’

Select to Open the Form to View or Enter Information and press OK

Expand any cells which need to be larger to fit the data.

Save your work

Turn in a copy of your entire Database, and a printed copy of your Grades Form

Back to the Table of Contents

Move on to Exercise #17

Exercise #17

Group

Donations Database

Solution: Answer to Project 17

Objective - This exercise teaches you how to work a wizard and template.

The chairperson, Maureen Heinis, of The Bolder the Better campaign for Disease Cure plans on having many donations made to her foundation. All her donations come through one donor, Peter Pablo, who has clients that give donations, through him, to the Bolder the Better campaign. She wants to record her first 6 donations before they stack up and become very tedious. She wants your help. First record Peter Pablo’s information. Then, in 6 sub-records put the clients who made donations through Peter Pablo.

1) Open Microsoft Access

2) Choose New from the pull down File menu

3) Choose the Databases tab and find the template labeled Donations.

Highlight it and click OK.

4) Name the file ‘Donations Received’ and click Create

5) The Wizard will pop up, just click finish.

6) Enter the following under your campaign information:

Campaign Name: The Bolder the Better

Chairperson: Maureen Heinis

Description: Disease Cure

Notes: N/A (not applicable)

Launch Date: 5/01/03

End Date: 5/01/05

Fund Raising Goal: $50,000

7) After you complete this the Main Switchboard will pop up. Click Enter/View Contributors. Enter the

following information for Peter Pablo and his donations:

Donor Name: Peter Pablo

Home Phone: 973-225-6789

Address: 22 Isabella St

City: Newark

State: NJ

Postal Code: 07068

Date Pledged: 5/12/03

Amount Pledged: $50

Cardholder Name: Buddy Gump

Credit Card #: 0256-2365-8899-5456

Card Exp. Date: 09/30/03

Paid: YES

Payment Date: 5/12/03

Donor Name: Peter Pablo

Home Phone: 973-225-6789

Address: 22 Isabella St

City: Newark

State: NJ

Postal Code: 07068

Date Pledged: 6/23/03

Amount Pledged: $2450

Cardholder Name: Elizabeth Cobb

Credit Card #: 0456-7865-8649-1448

Card Exp. Date: 11/30/05

Paid: NO

Payment Date: 8/18/03

Donor Name: Peter Pablo

Home Phone: 973-225-6789

Address: 22 Isabella St

City: Newark

State: NJ

Postal Code: 07068

Date Pledged: 6/17/03

Amount Pledged: $150

Cardholder Name: Harriett Summers

Credit Card #: 0676-2577-7878-4556

Card Exp. Date: 12/31/04

Paid: NO

Payment Date: 1/1/04

Donor Name: Peter Pablo

Home Phone: 973-225-6789

Address: 22 Isabella St

City: Newark

State: NJ