January 25, 2000
n Define common
base features and
Identify features of
Microsoft Access 97
Access 97 Introduction
n Develop Microsoft Microsoft Access 97 is a desktop database that is fully integrated into the
Access 97 tables Microsoft Office 97 suite and allows you to take data from various sources and
store it in compact and readily accessible form. Once the data is stored it can
n Create Microsoft
Access 97 forms be manipulated, displayed and exported in a number of ways. Access 97 also
allows you to control how data is entered into the system. For the more
n Create Microsoft advanced user, Access 97 makes full use of System Query Language (SQL)
Access 97 queries
and Visual Basic for Access (VBA.) With SQL and VBA, a developer can
n Create Microsoft produce stand-alone applications to meet a variety of needs.
Access 97 reports
The wizards avail- Need Help?
able in Microsoft
The Help Desk is a service provided to all students, staff, and faculty at Mississippi State
University at no charge. The consultants are there to help you with any kind of computer-
will provide step-
related information or problems. Check the Web site at http://www.its.msstate.edu/helpdesk
for handouts and resolutions to several common computer problems. If you cannot find an
tions for many
answer to your question on the Web site or you if do not have access to the Internet,
please call us at 325-0631 (7:00 A.M. to 7:00 P.M. Monday through Friday). You can also
email us at firstname.lastname@example.org or come by our
office. We are located at 46 Magruder Street (the
blue house behind Rice Hall) with walk-in hours
from 8:00 A.M. to 5:00 P.M. weekdays.
James L. Stanmore, Jr.
This handout will guide you through an introduction to the features of Microsoft Access 97.
By the time you complete the exercises you will be able to:
Create, open and use tables
Create, open and use forms
Create and use simple queries
Create, use and print basic reports
Access 97 is an extremely powerful program that also has easy to use basic features. For
this workshop we will concentrate on the basic features. It is important to note, however,
that Access 97 and Access 2000 can support just about any need you have - the only
limitations are skill and knowledge.
At the end of this handout are several on-line resources that have a wealth of knowledge.
If you need further guidance after you complete this workshop they, as well as the exten-
sive help available within Access, can probably provide it for you. You will also find my
email address and the contact information for the ITS Help Desk on the front page of this
handout. We stand ready to assist you with any questions that you may have.
The skill aspect you will have to provide. As you use Access your knowledge will grow.
Don’t be afraid to expirement - just remember to back-up your database and any im-
portant data before you try anything! The last sentence is extremely important: always
back-up your database regularly. That means after any time the data is changed. It is
painful to watch the expression of a user that finds out they have to type in 1,500 contact
Enjoy the workshop and please remember to complete the on-line evaluation before you
leave. The class code will be placed on the board at the end of the session.
Starting Microsoft Access97
Create a Folder in Microsoft Access 97
1. Open program Microsoft Access. In the opening window, where it says
“Create a New Database Using”, click Blank Database, then click OK. In
File New Database window, change Save in: to C: drive. Click on Create New Folder
icon, and assign it the name Access1. Click OK.
Create a Database
1. In File New Database window, Save in: C drive in folder Access1. In File name: text
box, type in Student. Click on Create.
Working with Tables
1. Be sure Tables tab is at the front
2. Click on New, click on Design View, click on OK. Enter the following into Design
view of the Table:
3. Add field properties by clicking in the Field Name Year. Then in the lower portion of
the Design View window under Field Properties and on the line that says Validation
Rule type in: FR or SO or JR or SR. In the line that says Validation Text, type in:
“You must enter FR or SO or JR or SR”.
Working in Datasheet View of the Table
1. Click on the View Icon to change you to Datasheet view. When the pop-up Window
asks “Do you want to save the table now?” Click on Yes. In the Save As Dialog box
type in “Student Information” for the Table Name, then click OK. In the pop-up
window “There is no primary key defined….Do you want to create a primary key
now?” Click on Yes. Enter the following data into the table:
2. When finished entering the records, click on File -> Close.
Working with Forms
Create an AutoForm: Columnar
1. Click on the Forms Tab. Click on New. Click on AutoForm: Columnar. In the drop-down box,
choose the table “Student Information.” Click on OK.
2. Change records to see how records on forms correspond to records in datasheet view of Table.
Close the form. When it asks, “Do you want to save changes.” Click No.
Create a Form with the Form Wizard
1. Click on New. In New Form, click on Form Wizard. In New Form, choose “Student Informa-
tion” table as the table where the object’s data comes from.
2. In next dialog box of Form Wizard: “Which fields do you want on your form” Click on ‘>>’ to
choose all fields. Click on Next.
3. In next dialog box of Form Wizard: “What layout would you like for your form?” Click on Colum-
nar. Click on Next.
4. In the next dialog box of Form Wizard: “What style would you like?” Click on International (or
some other style of your choice.) Click on Next.
5. In the next dialog box of Form Wizard: “What title do you want for your form?” Keep Student
Information. Leave other defaults. Click on Finish.
6. Click on maximize button to enlarge form.
7. Click on View icon to change to Design View of the Form
Place a Label (or Title) on the Form
1. Place the pointer between Form Header and Detail. When the pointer changes to a resizing tool,
drag it down about 2 inches. Then place the pointer on the right border of the form. When it
changes to a resizing tool, drag it out about 6 inches.
2. In the toolbox, click on the Label tool and let go. Then move the mouse to the Form Header
area. You should see the letter A with a cross-hair next to it. Left click on the mouse and draw a
rectangle. Inside the rectangle, type “Student Information”. Then click on the grid. Then click inside
the rectangle again. Resizing and moving handles will appear.
3. Use the following tools to enhance the label.
- Font/Fore Color
- Fill/Back Color
- Special Effect – Shadowed
- Line/Border Color
- Choose Format -> Size -> to Fit. Then click on grid.
4. Click on Form View and view the form.
Place Clip Art on the Form
1. Click on Design View. Click on Unbound Object Frame in the Toolbox. Move the mouse to
Form Header area and draw a rectangle next to the label. When Insert Object window appears,
choose “Microsoft Clip Gallery”. Click OK. Choose a piece of clip art. Click on Insert. Right
click in rectange and choose Properties. Click on Format tab. In Size Mode choose Zoom. In
Special Effect choose Flat.
2. Close Unbound Object Frame: OLEUnbound 17 dialog box. Resize rectangle if necessary. Click
on Form View to view completed form.
3. Close Form Window. When it asks “Do you want to save changes to the design of form Student
Information?” Click on Yes.
Setting up Queries
Setting up a Select Query
1. Click on the Queries Tab. Click on the New button. Select Design View (if it’s not already
chosen). Click on OK. In the Show Table window, click on Add, to add the table “Student
Information’ for the Query. Then click on Close. Resize the windows so all the variables in the
Student Information Table can be seen.
2. Double click on First Name, Last Name , Year, Credits and Paid to add them to the Query Grid.
3. Run the Query by clicking on the Run Icon- the exclamation point. Return to the grid by clicking
on Design View.
4. In the Criteria Row in the Column Year, type in =FR. Run the query (click on the exclamation
point.) Return to the Query grid.
Running a Parameter Query
1. Remove the =”FR” from the Year column. In its place enter the following:
[Enter Year] . Run this query several times substituting FR, SO,
JR, and SR.
2. INFORMATION: If you don’t put a comparison operator Access assumes =.
Other comparison operators: > greater than
< less than
>= greater than or equal
<= less than or equal to
NOT not equal to
Using a Comparison Operator in a Query
1. Click the View button on the toolbar to return to Design View. Choose Edit -> Clear Grid. Put
First Name, Last Name and Credits in the query grid. Type “> 9 “in the Criteria Row in the
column Credits. Run the query.
Using Compound Criteria OR and AND
1. Click the View button on the toolbar to return to Design View. Add “Year” to the Query Grid.
In the Year column and the “or” row type JR. Run the query. Return to Design View. Put the JR
on the criteria row and take it away from the “or” row. Run the query.
Using Computed Fields in a Query
1. Return to Design View. Right click the Field row in the 5th column in the design grid and then click
Zoom on the shortcut menu. Type “Cost:[Credits]*50.00”. Click the OK button. Run the query.
2. Return to Design View. Right click in the Cost column, click on Properties. Click on the Gen-
eral tab, Format row and choose currency. Close properties window, run query again.
Using Functions in Queries
1. Click the View button on the toolbar to return to Design View. Clear grid. Add Credits to the
Design Grid. Click on the Totals icon on the toolbar. Click the Total row in the Credits column,
then click in the GroupBy cell and choose Avg. Run the query.
Using Grouping with Functions in Queries
1. Return to Design View. Add Year to the Design Grid. Run the Query. Choose File -> Close.
When it asks, “ Do you want to save changes to the design of the Query?” Choose Y For
Query Name, name it ‘Test Query Report”.
Create a New Report Using Auto Report:Tabular
1. In the main Database window, click on the Reports tab. Click on the New button, click on
AutoReports:Tabular. Choose the table “Student Information” from the table or query drop
down box. Click on OK. View the Report. Close the report with File -> Close. When it asks
“Do you want to save the changes…?” Click on Y Give the report a name, then click OK.
Create a New Report Using the Report Wizard
1. Click on the New button, click on Report Wizard. Choose Student Information table, click on
2. In the next dialog box, click on the double arrow to place all Available Fields into all Selected
Fields. Click on Next.
3. In the next dialog box – “Do you want to add any grouping levels?” Click on Year, click on the
arrow “ > “. Click on Next.
4. In the next dialog box – Choose Last name as the first sort field. Click on Next.
5. In the next dialog box – Keep the defaults and click on Next.
6. In the next dialog box – Choose a style. ‘Corporate’ for example. Then click on Next.
7. In the next dialog box – “What title do you want for your report? “ Leave ‘Student Information’ or
type in your own title. Click on Finish. View the report.
8. After viewing your report, choose File -> Close.
Create a Report From a Query
1. Create a Report Using the Output of “Test Query Report”
Create Mailing Labels Using a Report
1. Choose the Reports tab, click New,choose Label Wizard. Choose the table “Student Informa
2. Click on OK. Choose Avery number 5160. Click Next. Click Next again.
3. Finish constructing labels as demonstrated in class.
Converting from Excel97 to Access2000
Reasons for doing this:
1. Worksheet contains alot of redundant data.
2. Excel has limits that Access doesn’t.
3. Databases work with related tables.
4. To use Microsoft’s Access query and report capabilities.
1. Open program Microsoft Access. Click Blank Access database under heading
Create a New Database Using. Click OK.
2. In File > New Database Window, Save in C:\Access1. In File Name :put “convert”. Then
click on Create.
3. The new database opens.
4. Click on File >Get External Data > Import.
5. In the Import Window, In the Files of type: choose Microsoft Excel
In File name enter: j:\data\access\grades. Click on Import.
The worksheet ‘Grade Example.’ is displayed. Click on Next.
6. Click on First Row Contains Column Headings. Click on Next.
7. When it asks, “Where would you like to store your data?” Click on In a New Table.
Click on Next.
You can specify information about each of the fields you are importing.
Click on Next. Let Access add a primary key. Click on Next.
That’s all the information the wizard needs to import your data.
Click on Finish. Click on OK. Table is created.
When tables are deleted from databases, databases continue to increase in size and become
fragmented. You can compact the database to fix this. Click on Tools > Database Utilities >
Compact and Repair Database
Copying a Table
Click on the table, click on copy icon, click on paste icon and fill in the dialog box.
Applying What We’ve Learned
When you read the opportunity listing for faculty mentors for the travel/tourism course on cruise lines, you
saw an opportunity for a free cruise. Little did you realize that you would get the responsibility of keeping
track of the student information as your fare!
The instructors have provided you with basic student information. It is your task to generate the following
1. Class Roster
2. List identifying students with special needs
3. Emergency contact forms for the students to complete
4. Mailing labels
6. Welcome letter
7. Make it easy for anyone to view, enter or change information
Suggested course of action
First create a student information table, then create a data entry form for the table and enter data. Make
sure that you create the emergency contact fields: Parent’s address, work phone and home phone Next
generate your reports: class roster, special needs list, emergency contact form, mailing labels.
John Roberts, 453 Robin Dr., West Point, MS 39703, Fr, (662) 343-8769
Samantha Jones, 2267 Johnson Ave, West Point, MS 39704, Fr, (662) 341-896, Diabetic
Sarah Thomas, 105 Aberdeen Rd, Columbus, MS 39701, So, (662) 434-8796
Ron Minos, 34 University Blvd., Starkville, MS, 39716, Jr, (662) 323-4535, ADA
Renee Russo, 562 College Ln., Starkville, MS 39719, So, (662) 326-1187, Kosher
Jeanne Sarandon, 447 Boulder Creek Dr., Minnetonke, MN 55343, Fr. (611) 832-5523
Susan Bullock, 639 Magnolia Dr., Starkville, MS 39713,
General information and FAQ’s:
The Access Web at http://www.mvps.org/access/
Access Power User Website at http://www.kunicki.com/msaccess/
Microsoft Access SIG (Special Interest Group) at http://www.hal-pc.org/~SIG/access.html
On-line tutorial from the University of Regina at
VBA Tutorials at http://www.standard.net.au/~garyradley/VBATutor.htm
Templates and example queries:
Go to http://www.microsoft.com, select Support, Downloads and then select Access in the
Product box and your operating system in the System box (Win95, Win98, NT.)
User Training & Support
Information Technology Services
51 Magruder Street
Mississippi State, MS 39762
Help Desk 325-0631
What is a Database?
u A database is a collection of facts,
Productivity with Microsoft called data, related to a particular topic
which you want to refer to repeatedly for
making calculations or decisions
Information Technology Services
User Training & Support
Methods to Store and
u Word processors u Form letters
u Spreadsheets u Mailing lists
u Databases u Tables
u Cells u Designed to manipulate large quantities
u Advanced mathematical functions of data
u Provides functionality of word
processors and spreadsheets
u May have steep learning curve
When to Use a Database? Example Use of a Database
u Large quantity of data to organize and u Supplier contact information in a card
u Differing data types that must be u Order information in a spreadsheet
interrelated u Customer mailing addresses in a
u Require data entry by users with limited mailing list
access or training u Product information in a card file
u Must be organized and related
continuously by user
u In a database, relationships between
information are easy to create
Data Can Consist of Types of Databases
u Text u Flat-file
u Numbers u Relational
Flat-file Databases Relational Databases
u Collection of records, each consisting of u Several collections of records, each
related data consisting of related types of data
u i.e. Last name, first name, college, u i.e. collection of names, collection of
address, city, state, zip colleges
u All information must be in one file u Master file does not contain data, only
u Each record must contain all information references
pertaining to that record u Relationships between data is dynamic
u i.e. Each college record must contain and easily changed (permissions)
full spelling of college name u i.e. each college entry points to a
reference in the college file
Relational Databases (cont.) Access97
u Easily link two or more tables (files) so u Unique in storage method
they appear as one table u Single file contains all related tables,
u Minimize information duplication by indexes, forms and report definitions
requiring repetition of only those data u .mdb file even includes VBA
items, such as student ID, by which programming code you can added
multiple files are linked
u Access manages details of relating
elements (unlike Paradox or FoxPro)
Elements of Access97 Database
Elements of Access 97 Database
u A collection of data about a specific
topic. Tables organize data into rows
called records and columns called
fields. Records and fields combined
make up the table.
Elements of Access 97 Database
u The form is where you normally enter,
display and edit your data. It provides
greater flexibility than a table. As you
enter data into a form you are
simultaneously adding it to the table.
Elements of Access 97
Databases - Queries
u A query is either a question about the
data stored in your tables or a request
to perform an action on the data.
Elements of Access 97
Databases - Reports
u Reports give you control in presenting
your data. (More so than printing from
the tables.) They can also provide
Elements of Access 97
Databases - Macros
u Microsoft Access 97 - Help u A macro is a series of keystrokes that
u Using Access 97, Second Edition by you can record and then replay with a
Roger Jennings single command. Macros are good for
u Microsoft Office 97 - Introductory recording a series of tasks that are long,
complex or easily forgotten.
Concepts and Techniques, Shelly
A publication of
User Services, a division of Information Technology Services,
Mississippi State University
Mike Rackley, Head
Mississippi State University does not discriminate on the basis of race, color, religion,
national origin, sex, age, disability, or veteran status.