Microsoft Access – Chapter 2

Exercises

 

 

In this lesson you will learn many new skills within the powerful program of Microsoft Access.  Listed below are some of the skills you will master upon completion of this lesson:

 

 

 

Ø       Create a new database by entering data

Ø       Edit Fields

Ø       Set a primary key for your database

Ø       Create a database using a template or wizard

Ø       Create relationships and set up relational databases

Ø       Create a form

Ø       Utilize and switch between the different views in Access

Ø       Create and utilize reports from information in your database

Ø       Change field properties

Ø       Learn how to import tables

Ø        

 

 

Table of Contents


 

 

 

Exercise #1.

Exercise #2.

Exercise #3.

Exercise #4.

Exercise #5.

Exercise #6.

Exercise #7.

Exercise #8.

Exercise #9.

Exercise #10.

Exercise #11.

Exercise #12.

Exercise #13.

Exercise #14.

Exercise #15.

 


Group Activities


 

Exercise #16.

Exercise #17.

Exercise #18.

Exercise #19.

Exercise #20.

 

 

Phone Numbers List

Grade Report

Information for Your Employer

Creating a Table in Design Mode

Guests Table

Exercise Log

Address Book

Party Management Database

Creating a table using Table Wizard

Changing Field Properties

Contact Database

Household Inventory

Superbowl Database

Creating and Modifying a Table

Creating a Form From Existing Data

 

 

 

 

 

Accounting Class Grades Database

Donations Database

Classmates Information

Emergency Contact Information

Importing Tables

 

 

 

Back to Main Page

 

 

 

 

 

 

Exercise #1

 

Phone Number List

 

Solution: Answer to Project 01

 

Objective - The purpose of this exercise is to understand how to create a new database and enter new information into a table and save it.

 

-Click on New in the File menu

-Select Blank Database

-Name it ‘Phone Number List’

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

-Set up three fields: First name, Last name, Phone Number

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

-Enter in your own Information for these fields

-Save this and call the table Phone Numbers

-Allow the program to create a primary key for you


Back to the Table of Contents

 

Move on to Exercise #2


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Exercise #2

 

Grade Report

 

Solution: Answer to Project 02

 

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

 

  1. Choose to open a blank access database. 
  2. Entitle the new database, Tablenumber1.
  3. Highlight Create Table in Design view and the press NEW command.
  4.  Choose Highlight Table Wizard and press Enter.
  5. Choose Personal, and then Guests under Sample Tables.
  6. Under Sample Fields, highlight First Name and click on the > icon to the right.  Do the same for Last name, City, and State.
  7. 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. 
  8. Press Next, Choose Yes for set a primary key for me.
  9. Press Next, leave this page the way it is, Press Next, and bubble in Modify the Table Design. Click Finish
  10. 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

 

  1. Open the Exercise1 file from Exercise 1 entitles Mailing List.
  2. Go to View, and select Design View. 
  3. Highlight the Last Name Field.  Using the top tool bar, select Make it the Primary key by choosing the “small key” icon.
  4. Highlight the Mailing List ID field. Right click and choose Delete Rows.
  5. 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”. 
  6. Highlight Last Name, under the Field Properties, switch the Required box from No to Yes.  Do the same for First Name. 
  7. 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. 
  8. 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:


 

 

Robert Johnson

Director of Marketing

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8001 work

212-243-8009 fax

212-536-2367 cell

 

 

Cynthia Toomey

EVP

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8012 work

212-243-8009 fax

973-714-0683 cell

 

 

Angelique Woodson

Special Projects Manager

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8020 work

212-243-8009 fax

212-536-6066 cell

 

 

Chris Janson

Communications Intern

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8010 work

212-243-8009 fax

212-454-6760 cell

 

 

Judy Holland

Strategic Partnerships Manager

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8006 work

212-243-8009 fax

212-536-2213 cell

 

 

James Healton

Marketing Assistant

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8021 work

212-243-8009 fax

212-454-5678 cell

 

 

Becky Rathers

Program Development Director

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8030 work

212-243-8009 fax

212-442-6361 cell

 

 

Helen Stewart

Senior Account Executive

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8031 work

212-243-8009 fax

212-442-2531 cell

 

 

Daniel McLaughlin

CEO

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8017 work

212-243-8009 fax

212-223-6143 cell

 

 

Tony Moran

CFO

Peterson Prints Inc.

1200 Avenue of the Americas

Suite 200

New York, NY 10036

212-243-8019 work

212-243-8009 fax

212-717-8830 cell

 

 


 

Once you have entered all the contacts, return to the main Switchboard and click on Preview reports.  Click on Preview the Alphabetical Contact Listing Report. The List of the ten contacts you entered should come up.  Make sure you save your work, and print out a copy of the report to give to your instructor.

 

 



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.

 

  1. Highlight the Mailing List table from the previous Exercise    
  2. Choose Forms from the window on the left and then highlight Create form in design view.
  3. Click the New command above. 
  4. Select Form Wizard, and pull up guests from the arrow down key below. 
  5. Select all five field categories using the > icon. 
  6. Remove the Guest ID field using the < icon.
  7. Press next and then choose the Columnar layout and press next again.
  8. Choose Industrial on the next screen and press next again. 
  9. For the title of the form, type in General Guest List.
  10. 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

 

  1. Go to View and choose Data Sheet view.
  2. 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

Postal Code:              07068

Date Pledged:                       8/28/03

Amount Pledged:      $5000

Cardholder Name:    Matthew Comb

Credit Card #:           4561-1189-2645-2948

Card Exp. Date:        05/31/05

Paid:                           YES

Payment Date:          8/31/03

 

Donor Name:             Peter Pablo

Home Phone:                        973-225-6789

Address:                    22 Isabella St

City:                            Newark

State:                          NJ

Postal Code:              07068

Date Pledged:                       11/16/03

Amount Pledged:      $888

Cardholder Name:    Holly Frost

Credit Card #:           0879-2468-8796-2468

Card Exp. Date:        10/31/04

Paid:                           YES

Payment Date:          12/21/03

 

Donor Name:             Peter Pablo

Home Phone:                        973-395-8782

Address:                    22 Isabella St.

City:                            Newark

State:                          NJ

Postal Code:              07068

Date Pledged:                       12/1/03

Amount Pledged:      $3250

Cardholder Name:    Rebekah Pablo

Credit Card #:           9865-4465-8764-3169

Card Exp. Date:        04/30/06

Paid:                           NO

Payment Date:          9/12/04

 

 

 

8) Go back to the Main Switchboard and click on preview reports.  Print a campaign summary report,

    a pledge listing report, and an unpaid pledges report.  Hand these into your professor.

9) When you are finished click exit this database.  Be sure you have saved your work before exiting the

    program.

 

 

 

Back to the Table of Contents


Move on to Exercise #18

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Exercise #18

 

Group

 

Classmates Information

 

Solution: Answer to Project 18

 

Objective - The purpose of this exercise is to teach you how to gather information from various sources and compile it in a database.

           

-Go around and collect the following data from 7 other students:

            Name

            Age

            Phone Number

            Social Security Number

            Birthday

            Salary

-Start Microsoft Access, and open a blank database.

-Save as “My Group Database”

-Click the “New” button

-Highlight “Table Wizard” and click OK

-Choose “Students” as your sample table

-Enter the fields listed above, separating first and last names

-Click next

-Choose “Yes” then “finish”

-Click “view” then choose “design view”

-Choose SSN as a Number, Age as a Number, First Name as a Text, Last name as a Text, Phone as a Number, Salary as a currency, and birth-date as a date/time.

-Change the view back to datasheet, after saving the table.

-Enter in the appropriate information from your classmates

-Right Click the Last name column, and sort in ascending order.

 

 

 

Back to the Table of Contents

 


Move on to Exercise #19

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


 

 

Exercise #19

 

Group

 

Emergency Contact Information

 

Solution: Answer to Project 19

 

Objective - This exercise shows you how to create a database using a wizard and to obtain your data from outside sources.

 

Your teacher wants to have important information about your class in case s/he needs to get in touch with you for any reason.  You will divide into teams of four and enter your information into a table using Table Wizard.  Use students as the sample table.  The table should include the fields:  StudentsID, FirstName, LastName, Address, City, State, PostalCode, PhoneNumber, Major, and EmailName.  Set phone number as the primary key of the table.  Once you have completed the table save it as Group Table.  You will then make a form using form wizard.  Be sure that all of the fields that are included in the table are also included in the form.  Choose Columnar for the layout of the form.  Choose any style that your group wishes.  Title the form Student Information.  Print out the completed forms and submit them to your instructor.

 

Back to the Table of Contents

Move on to Exercise #20 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Exercise #20

 

Group

 

Importing Tables

 

Objective - This exercise will teach you how to import different tables into one database.

 

Solution: Answer to Project 20

 

In this exercise, a group of 4 people will create 4 different tables and bring them together into one database. 

 

Each member of the group should use the skills learned in Exercise #’s 5, 10, and 15 to create a 1 field table which includes their First Name, Last Name, Address, Telephone Number, and Gender.  Make the two Name categories Required, and make an “M” or “F” validation rule for Gender.  Save all of the different tables on the same computer as your name, (ie. JohnSmith)

 

After Everyone has completed their tables, take turns combining the different tables into one table using the Import table feature.

 

  1. Highlight  your table and click New.
  2. Highlight import table and click OK.
  3. Find the different tables of the people in your group on the list. 
  4. This will make the different tables be available under the same database. 
  5. Save and give it to your instructor on a disk

 

 

 

Back to the Table of Contents

 

Back to Main Page