accessmanual tx

7/28/2019 AccessManual TX

1/104

M I C R O SO F T A C C E SS 97M I C R O SO F T A C C E SS 97

I N T R O D U C T I O N

Training manuals provided by Knowledge AllianceAustin

2113 Wells Branch Pkwy #500AAustin, Texas 78728

(512) 989-8558(512) 989-8580 fax


2/104

Introduction

TEAMTech Austin Page 2

Preface

FutureTech Training, Inc. (FTT) prepared this manual for use in the area of Trainingand Development for continuing education. It is intended that these materials will beused to assist students in the learning process during the class, after the class to

review, and continued learning as a reference guide. This manual is NOTintended toreplace the Users Guide Manual that is provided by the company with the software.

This training manual was prepared using Microsoft Word 97, and Windows 95. Themaster copy was printed to disk using the Adobe Acrobat PDF program.

ACKNOWLEDGMENTS

Primary Course Developers: Russ D. Peterson, Jr.

Kevin J. Karschnik

Copyright Notice

Copyright 1997 by FutureTech Training, Inc. All Rights Reserved. 040798

This publication, including the student manual, diskette, quick reference cards, and FTTProductivity Tracking, or any part thereof, may not be reproduced or transmitted inany form or by any means, electronic or mechanical, including photocopying, recording,storage in an information retrieval system, or otherwise, without the prior writtenpermission of FutureTech Training, Inc., 1106 Clayton Lane Suite 105E, Austin, TX

78723, (512) 459-0715.

DisclaimerWhile FutureTech Training, Inc. makes a sincere effort to ensure the accuracy andquality of these materials described herein, all material is provided without warranty,including, but not limited to, the implied warranties of merchantability or fitness for aparticular purpose. FutureTech Training, Inc. disclaims all liability for any direct,indirect, incidental or consequential, special or exemplary damages resulting from theuse of this product or the products described in this manual.

Trademark Notices

FutureTech Training, Inc., FTT, and the FutureTech Training, Inc. logo areregistered trademarks of FutureTech Training, Inc.Microsoft and Windows are registered trademarks of Microsoft Corporation.

All other product names and services identified throughout this book are trademarks oftheir respective companies. No such use is intended to convey endorsement.

TEAMTech is a national partnership between United Way of America, IBM,Americorps*VISTA and Public Allies. The goal of the project is to develop strong


3/104

Introduction


leadership and technological skills among non-profit organizations. Austin is one oftwelve cities participating in the project. Americorps*VISTA volunteers go into thecommunity to help enhance human service agencies competency in technologythrough different avenues, such as computer software training and the installation ofdonated computer equipment.

This is the second year of the TEAMTech Austin project. Last year TEAMTech Austinfocused on a pilot project, creating an Information and Referral database networkcalled InfoLink. The closed InfoLink Network was made available to non-profitagencies to help facilitate collaboration between agencies, and to make the agenciesmore effective at distributing information to the public. This year the United WayCapital Area plans to expand the InfoLink pilot project by putting it on the World WideWeb. It is our hope that this will make agency information more easily available andaccessible to the public. In addition, our goal for this year is to provide Microsoft

Access training to non-profit agencies in order to gain an increased knowledge of thesoftware to better use it. Also, TEAMTech members will provide assistance to agenciesdeveloping a technology plan to sustain their agencies into the next millennium. Wewill fulfill this goal partly through the creation of a technology resource manual.


4/104

Introduction


Introduction

Welcome

How to Use This ManualThe purpose of this manual is to make your software application easier tounderstand so you can apply the information to your work. The following termsand symbols will help you identify specific sections of this book.

LESSONS The bold, italicized, large print at the introductionof each chapter represents a new topic. Eachtraining guide is divided into several lessons foreasy reference after class.

Topic Headings Each lesson is divided into many topics that arelabeled by a bold, italic heading. These are toassist you during and after class to find specifictopics on the subject matter.

Subtopics Subtopics are also bold and italic and will be foundwithin each topic to emphasize the informationinside the main areas.

Exercises The smile symbol throughout the book refers to anexercise to be done in class or during a practicesession.

Warning This symbol indicates a pitfall or warning for you towatch out for in your work.

Glossary The glossary found at the end of this manual is acollection of terms and definitions from this book.


5/104


6/104

Table of Contents


Table of Contents

Lesson One: Getting Started In Access........................................................9Course Objectives .................................................................................. 10What is Access?..................................................................................... 11Start Access ........................................................................................... 13The Access Interface.............................................................................. 14Understanding Database Terms............................................................. 16Create a Database with the Database Wizard ....................................... 17Creating a New Database from Scratch ................................................. 21

Lesson Two: Working With Tables ...............................................................23Create a Table........................................................................................ 24Field Data Types .................................................................................... 26Set the Primary Key................................................................................ 28

Table Wizard vs. From Scratch ........................................................... 29Enter Data .............................................................................................. 32Modify the Table Design......................................................................... 35Define Table Relationships .................................................................... 37Edit Data in a Table................................................................................ 40Edit Data With the Zoom Box................................................................. 42Use AutoCorrect to Edit Data ................................................................. 43

Lesson Three: Locating Records .................................................................. 45The Find Feature.................................................................................... 46Sort Data ................................................................................................ 48

Filter the Data......................................................................................... 51Filter by Form ......................................................................................... 52Filter by Selection................................................................................... 53Save a Filter as a Query......................................................................... 54

Lesson Four: Forms in Access......................................................................55What is a Form? ..................................................................................... 56The Form Wizard ................................................................................... 57Use a Form to Locate Records............................................................... 60Change the Design of a Form ................................................................ 61Use AutoFormat with Forms................................................................... 64

Lesson Five: Querying Data ..........................................................................65What is a Query? ................................................................................... 66Use the Dynaset to Edit Data ................................................................. 70Use an Expression as Criteria................................................................ 71Save the Query Object ........................................................................... 73Perform Calculations with a Query......................................................... 74Grouping and Totals of Queried Data .................................................... 75


7/104

Table of Contents


Query Several Tables............................................................................. 76

Lesson Six: Reporting the Data..................................................................... 79What is a Report? .................................................................................. 80The Report Wizard ................................................................................. 82

Customize the Report............................................................................. 85Preview and Save a Report.................................................................... 87

Lesson Seven: Importing Data......................................................................89Import Data from Microsoft Excel ........................................................... 90Exit Access............................................................................................. 92

Index.................................................................................................................93

Glossary...........................................................................................................95


8/104


9/104

Lesson One: Getting Started in Access


Lesson One: Getting Started In Access

1. Course Objectives

2. What is Access?

3. Starting Access

4. The Access Interface

5. Understanding Database Terms

6. Creating a Database with the Database Wizard

7. Creating a New Database

8. Creating a Table

9. Field Data Types

10. Table Wizard vs. from Scratch

11. Entering Data

12. Modifying the Table Design

13. Defining Table Relationships


10/104



Course Objectives

This course was designed for the user who has fundamental experience inWindows 95.

Before ClassAll students participating in this course should be familiar with the basicoperations of Windows 95 and the use of a mouse.

After ClassUpon successful completion of this course the student will be able to completethe following objectives:

Identify the parts of the Access screen Create a Table Object in a Database

Identify Data Field types

Enter and Edit data in a Table

Set a Primary key for a Table

Define Table relationships

Filter / Sort / and Find records

Create and Use a Form

Create and Use a Query

Create and Customize a Report

Import data from Excel


11/104



What is Access?

The function of a database is to store and retrieve information in an efficientmanner. The most common example of a database is an address book used for

storing names, addresses, and phone numbers. The address book is anexample of a flat database. A flat database is one in which all of the informationis stored in one file. With a flat database, it is necessary to enter the sameinformation repeatedly throughout the entire database.

Microsoft Access, on the other hand, is an example of a relational database.The relational database is more complex than a flat database because all theinformation is stored in multiple files. All relational databases are based on theconcept of storing information without duplication. In a relational database, it isonly necessary to make changes in one place, and then all other areas adjustaccordingly.


12/104



A relational database stores all information in multiple database files. Eachdatabase file contains different pieces of information. For example, onedatabase can hold customer information, while sales information is in a seconddatabase, and product information is in a third database. By placing informationin separate database files, there is an elimination of the need for redundant data

entry.

The Customer, Inventory, and Products database files are connected to eachother through a common field. This connection is called a join and creates abridge for Access to use for pulling information for search requests. Theexample below shows how three database files connect by a common field.

The Customer and Products databases join through the Customer ID Numberfield. The Inventory then joins to the Products database through the InventoryCode field.


13/104



Start Access

To start Access 97 from Windows 95, go to Start / Programs / Access.

Start Access

1. Go to Introduction Folderon your desktop

2. Double-click on Lesson1 to select todays class database

3. Access will load the database


14/104



The Access Interface

The parts of the screen in Access are identical to many of the standard parts ofthe screen that seen in other Microsoft applications.

Title Bar: The title bar is located at the top of open windows. Active and inactivewindows have differently colored title bars. Use the title bar for maximizing andrestoring a window with a double-click and for moving a window by dragging iton the screen.

Control Menu Icon: The control menu icon appears in the upper left-handcorner of open windows. There are two different types of control menus inWindows 95: one for the active application and one for each of the documents.Use the control menu icon to close a window by double-clicking on it.

Menu Bar: The menu bar appears directly below the title bar. The menu barhouses all the menus that needed to operate in the current window. Access themenus by clicking the desired menu or by using the ALT key combination withthe appropriate underlined letter.

Minimize, Maximize, Close, and Restore: These buttons appear in the upperright hand corner of the screen.

The Minimize button reduces a window to an icon.

The Maximize button enlarges a window to take upas much space as allowed, usually the whole screen.

The Restore button returns a window to its previous size.

The Close button closes the open window.


15/104



Scroll Bars: The scroll bars appear on the right and bottom of the screen.These bars are for moving around the screen. There are several ways to scroll:click the scroll arrows, click inside the scroll bar, or drag the scroll box.

The Database Window: When a database opens in Access, it opens into theDatabase Window. This window contains all of the necessary objects for thecreation of a functional, relational database. Navigate to this window at any timewith the F11 keystroke.

Toolbars: The toolbars in Access provide a quick method of utilizing features byeliminating the need to always use the menu bar. The toolbar changesfrequently in Access, depending on the view and object that currently in use.

The toolbar pictured below is the standard toolbar in the database window.

Status Bar: The status bar located at the bottom of the screen displaysimportant information. Before the creation of a database, Access will be waiting

in the Ready mode.


16/104



Understanding Database Terms

The database terminology used in Access differs somewhat from that used inother databases.

Object Tables, Queries, Forms, Reports, Macros, and Modules are alltermed objects. Each object stores different types of informationused in the database.

Table The Table is the seedlevel of all data in thedatabase. All datainformation is stored in

the Table. This is the firsttype of object created in anew database.

Query A Query is an inquiry into the data. A Query allows the user to ask aquestion of the data. Access provides an answer.

Form A Form is generally used to look at one record at a time on thescreen. Forms are used for easy data entry.

Report A Report is an object that places the data in a presentable format for

printing to paper. View Reports on screen or printed to paper.

Field A field is the smallest unit of information in a database. The firstname of a customer is an example of a field entry.

Record A record is the sum of all fields for one client, person, product, etc.In a Table, one record is one row of the Table.

Database A database in Access is the sum of all objects in the databasewindow. The entire database is saved as one file with an extensionof MDB, which stands for Microsoft Database.


17/104



Create a Database with the DatabaseWizard

Access offers many Wizards to help in the creation of new databases, Tables,Forms, and etc. The Wizards asks questions and then use the answers given toautomatically build whatever was requested. Users are encouraged to useWizards for the creation of new objects.

To create a new database with a Wizard, select File / New from themenu bar, or click on the New Database button. The New Databasebutton looks like a blank piece of paper.

Create a New Database Using a Wizard

1. Click the New Database button

2. In the New dialog box, select the Databases tab

3. Next, double-click the Address Book icon

4. Give the file your last name, and click Create


18/104



5. When the following dialog box appears over the Database window, click Next

6. Add Childrens Names to the list of fields in the Table. Select, Yes includesample data (including sample data can assist in learning to use thedatabase more quickly).


19/104



7. Click the Next button to move to the next step

8. For the screen display, choose International, and click Next

9. Choose the Bold Report style, and click Next

10. Name the database Addresses, and click Next


20/104



11. Choose the option for starting the database after the Wizard builds it, andclick Finish

12. View the Address Book


21/104



Creating a New Database from Scratch

To create a database from scratch, select File/New from the menu bar, or clickthe New Database button on the toolbar. These are the same steps taken to use

the Database Wizard.

Then, choose BlankDatabase from the General tab. Following this command,Access asks for a name for the database. The name of the database shouldreflect all of the objects that will be contained in the database. This file namecould be as simple as the name of a company.

Create a New Database

1. Close the Lesson1 database if necessary

2. Click the Blank Database icon, and click OK

3. Type Projects for the name of the database

4. Select the C: drive from the list of drives then OK

5. Notice how Access creates an empty database window


22/104


23/104

Lesson Two: Working With Tables



1. Create a Table2. Field Data Types

3. Set the Primary Key

4. Table Wizard vs. "From Scratch"

5. Enter Data

6. Modify the Table Design

7. Define Table Relationships

8. Edit Data in a Table9. Edit Data With the Zoom Box

10. Use AutoCorrect to Edit Data


24/104



Create a Table

The first step in creating any database is the creation of Tables. Each Tableconsists of rows and columns. The fields (columns) of the Table are defined

during the creation of the Table. The fields need to reflect the types ofinformation to be collected about each entry, such as product, order, etc.

Prior to creating the Table it is a good idea to plan out the strategy for the Table,and for the entire database. The planning stage of the creation process shouldreceive ample attention without dominating the entire project.

It is difficult to plan out the perfect database. Plan out as much as possible, butbe aware that Access makes it easy to make changes to the database later, ifnecessary

Rules for Table Creation

Design the Table to contain information about only one type of data(donors, corporate sponsors, donations, and etc.).

Designate a Primary Key to identify each record individually. This key

should never have a duplicate entry. A counter field works nicely asan arbitrary key.

Avoid redundancy in field creation. If the Contact name is in theDonor Table, do not place a Contact name field in the DonationsTable.

Avoid repeating fields like item 1, item 2, item 3. A sub-form objectcan take the place of these fields.


25/104



Table ViewsThere are two options available for viewing Tables, the Datasheet view and theDesign View. When viewing Tables, toggle between the two views by clickingthe Datasheet view button or the Design view button.

Datasheet View: The Datasheet view is a window that displays data from aTable in a row and column format. The Datasheet view is where the useredits fields, adds and deletes data, and searches for data. Click the Datasheetview button to change to the Datasheet view.

Design View: The Design view is where the Table layout is designed.Click the Design view button to change to the Design view.


26/104



Field Data Types

Each field in a Table is defined as a certain type of data. For example, the nameof a company would be considered a Text field.

The following Table defines the field types used in Tables within Access.

Field Type Description Example

Text Holds up to 255 characters of information. Input can be bothalpha and numeric

Company Name, Address,Phone Number

Memo Holds up to 64kilobytes of information. Used to store largeamounts of text as a memo.

Notes field in a customerTable

Number Numeric values of 1, 2, 4, or 8bytes Quantity Sold or in Inventory

Date / Time Dates and times of 8 bytes canbe used in calculation

Date of sale

Currency Monetary values up to 8 bytesin length

Price

Auto Number Numeric value automaticallyincremented by Access for eachrecord added

Customer ID, Order Number,PO Number

Yes / No Boolean values, one bit inlength, used for true / false

fields

Invoiced, Health Plan, MailingList

OLE Object OLE objects from otherapplications up to 1 Gigabyte inlength

Picture of the employee,picture of the product

Hyperlink Email addresses, Internet URLs [email protected]


27/104



Define the Following Types of Data

Field Name Data Type

1. Inventory Code2. List Price

3. Item Description

4. Quantity in Stock

5. Phone Number

6. Employee ID

7. Full Time or Part Time

8. Product Photo

9. Customer Notes

10. Last Name


28/104



Set the Primary Key

The Primary Key is the field whose value or values uniquely identifies eachrecord in a Table. In a relationship, the Primary Key refers to specific records in

one Table from another Table. The Primary Key field can not be duplicated inthe Table. Because of this, the unique ID field in a Table is often set as thePrimary Key. For example, the last name field cannot be set as the Primary Keysince there may be more than one Jones, or Smith.

The Primary Key also gives Access a method of uniquely identifying, locatingand relating records from separate Tables since the Primary Key has noduplicates in a Table.

Set a Primary Key

Setting this key is done from the design view of the Table. After

activating the field to be set as the Primary Key, click the Primary Keybutton on the toolbar. If a field is set as the Primary Key in a Table thatalready contains data and Access locates duplicates, it will display a warningthat will disallow the key.

Set Multiple Primary Keys

It is possible to set more than one field as the Primary Key. This instructsAccess that together, the two fields need to be unique. For example, setting theLast name and the First name as the multiple Primary Keys means that no tworecords can have the same First and Last name.

Set the multiple keys by selecting all of the fields while holding down the CTRLkey, and then click the Primary Key button.


29/104



Table Wizard vs. From Scratch

Access makes it possible to create a Table with the Table Wizard or to start fromscratch. Depending on how much planning occurred prior to creation, the choice

will vary. Access 97 uses a revamped Table Wizard that creates Tablesautomatically. Databases created with the Database Wizard do not requireTable creation.

The Table Wizard

If there is not adequate time or resources to plan out the types of fields desiredin the database, it may be a wise choice to use the Table Wizard as a startingpoint. By clicking on the New button in the Database Window while viewing theTable objects, Access asks if the user wants to use the Wizard or to create aTable from scratch.

The Wizard suggests fields based on the type of Table being created. Ifallowed, Access also sets the data types, field properties, and the Primary Key.The Primary Key is discussed fully later in Lesson Two.

Create a Table with Table Wizard

1. On the Table tab click the New button

2. Click the Table Wizard button

3. Select the Projects Sample Table

4. Double click the four fields shown in the picture below

5. Click the Next button to move to the next step

6. Name the Table Projects and let Access set the Primary key


30/104



7. Click Next to move to the next step

8. Select the Modify the Table design option, and click Finish

9. An example of how the Table should look appears below

10. Switch to the Datasheet view of the Table

11. Close the Table after viewing


31/104



Start from ScratchIf all of the fields are planned out, then it may be beneficial to create a Tablefrom scratch. Access provides a blank Table grid for defining fields and datatypes and properties. Creating a Table from scratch can be faster than using theWizard when the user is familiar with the data types and field properties.

Create the Following Table

1. Click the New button while viewing the Table objects

2. Select the Design View in the New Table dialog box that appears next

3. Type the field names, data types, and descriptions, so your computer

will look like the picture in the book

4. Click Once on Product ID

5. Click the Primary Key button to make Product ID the Primary Key

6. Save the Table, and name it Projects

7. Switch to the Datasheet view


32/104



Enter Data

Enter data in Access from three different locations: the Table Datasheet View,the Forms, and the Query Dynasets. Remember that when entering data in any

of these locations, the data is actually placed in the underlying Table.

Add new records by clicking the New Record button on the toolbar. In thedatasheet view, the following keystrokes are helpful for editing and navigating in

Access.

The symbols pictured below are seen during data entry in Access. Each symbolrepresents the current status of the record.

Current Record

New Record

Edited and Un-saved Records

Add a New Record

Data Entry Short Cuts

Ctrl + ; Current DateCtrl + : Current TimeCtrl + Alt + Space Default field valueCtrl + Prev. field valueCtrl + Enter New line in fieldCtrl + plus sign Add a new recordCtrl + minus sign Delete recordShift + Enter Save RecordSpacebar Toggle check box

Navigating Keystrokes in a Datasheet

Ctrl + Home First Field in First RecordCtrl + End Last field in last recordEnd Last field in current recordHome First cell in current recordEnd in text box End of current lineHome in text box Beginning of current lineTab, or Right Arrow Next FieldShift + Tab Previous FieldRight and Left Arrow Next and Prev. fieldUp and Down Arrow Prev. and Next recordPg Up Up by one screenPg Dn Down by one screenCtrl + Down Arrow Current field in last recordCtrl + Up Arrow Current field in first recordCtrl + Pg Up Right one screenCtrl + Pg Down Left one screen


33/104



Navigate with the Mouse

It is possible to navigate in the Datasheet view by using the mouse. Themovements in an Access datasheet are similar to the navigation movements inan Excel spreadsheet.

In the Datasheet view of the Table objects, scroll through the records using thescroll bars and use the navigational buttons located at the bottom of the window.Use these buttons to go quickly to a record in the Table.

Save Records

Access saves data during entry record by record. If data is edited in a field andthen moves off of the record by clicking or pressing the up or down arrow,

Access automatically saves the changes to the record. If there is ever any doubtas to whether the current record is saved or not, look for the symbol to the left ofthe record. The symbols on the previous page explain the status of the record.

Without moving off of the record, save the changes to the record by pressing thekeystroke SHIFT + ENTER. This instructs Access to save the changes to the

record.

Navigate in a Table

1. Close down the Projects Database we created

2. Open the Table named Donors in the datasheet view

3. How many total records are there?

4. Navigate to record number 332


34/104



Enter Records in a Table

1. Click on the new record button

2. Enter the records listed below

3. Press Shift + Enterto save the last record

Donor ID Company Name Contact Person City Street Address State Zip Code Phone Number

1508 Slate & Sons John Slate Austin 451 Bedrock Way TX 78700 512-555-0023

1509 Acme, Inc. W. E. Coyote Austin 333 Desert Ave. TX 78700 512-555-4818

1510 Your Information Here


35/104



Modify the Table Design

Once the Tables are defined in the database, there may be a need to insertmore fields, rename certain fields, or to change the properties associated with

the fields. Make these changes from within the Design view.

If any of the changes are going to affect the data in the database, Access willask for confirmation. Changing the attributes of the database at the seed levelcan lead to certain effects in all other objects based on that Table.

WARNING: Access does not make changes to the fields in Forms, Reports,and Queries when a change is made to the Table. These changes must bemade manually.


36/104



Modify the Table Design

1. Open the Donor Table in the Design view

2. Change the Company Name Field to Company

3. Change the State field size from 50 to 2

4. Drag the City field down below the StreetAddress field

5. Drag the State field down below the City field

6. Save the changes to the Table


37/104



Define Table Relationships

The backbone of a relational database is the Table relationship. Each Table inthe database will most likely be attached to another Table via a common field.

The bridge that is created between the two Tables allows Access to pullinformation from multiple sources to build powerful Forms, Queries, and Reports.

Create Relationships

Create relationships by clicking the Table Relationship button on thetoolbar or by selecting Edit / Relationships from the menu bar. Theresulting window allows the user to drag a field from one Table to the field ofanother. This creates the relationship. Relationships are displayed by the linedrawn from one Table to the next. The enhanced Relationship Window in

Access 97 allows the user to define relationships between Tables and changeany Tables design on the spot.

Edit a Relationship

If any changes are ever desired, double click on the relationship line to accessthe relationship dialog box. From here, make any necessary changes, and thenclick OK to apply them.

Delete a Relationship

Delete relationships by clicking on the relationship line and then pressing delete

on the keyboard. If Access cannot delete the relationship, it displays a dialogbox and gives the reason.

WARNING: Deleting a relationship could severely affect Forms, Queries,and Reports in the database if they rely on data fields from the connectedTables.

Enforce Referential Integrity


38/104



This option tells Access that no record in any Table should exist if there is not acorresponding record in the related Table. For example, if one Table includesthe employee general information and the other Table contains the detailedinformation on their pay structure, it may be a good idea want to enforcereferential integrity. This would prohibit an employee from existing without a pay

structure, and it would prohibit a pay structure from existing without a relatedemployee record. Referential Integrity can only be enforced on two fields thathave the same data type---for example, text and text, or number and number.

Create a Relationship

1. From the database window, click the Relationships button

2. Notice the relationship set up

3. Double click thejoining line

4. Notice the relationship type, and then click OK

5. Drag Fundraiser ID from the Projects Table to the Donations Table

(drop it on Fundraiser ID)

6. Click Create to create the relationship

7. Click on Referential Integrity

8. Close down the Relationships window and save the layout


39/104



More Practice with the Primary Key

1. Open the Donations Table in Design view

2. Click on the DonationDate

1. Hold down CTRL and click on Donor ID Number

2. Click on the Primary Key button on the toolbar

3. Notice the key symbol to the left of the field

4. Save the changes and notice the errors(there are duplicates)

5. Click OK to get past the error dialog boxes

6. Hold down CTRL and click on Fundraiser ID

7. Click on the Primary keybutton on the toolbar

8. Close and Save the Table


40/104



Edit Data in a Table

Data can be edited at any time, and the changes are made to the underlyingTable and are saved record by record. It is possible to edit chosen data from the

Datasheet view of the Table, from a Form view object, or from a Dynasetresulting from a select Query.

When editing data, toggle between the Edit mode (mouse) and the Navigatemode (keyboard) by pressing the F2 function key. Other editing keystrokes arefound in Lesson One of this manual.


41/104



Undo

The Undo feature of Access works the same as it does in other Microsoftapplications. The Undo feature will only undo the last action performed. It willnot undo changes performed on multiple fields in a record when each field wasedited one-by-one.

Undo Field/Record

The Undo Record feature will undo any and all changes made to any fields inthe current record. This feature cannot undo changes to a record if the recordhas been saved.

Edit the Data

1. Open the Donor Table in the datasheet view

2. Navigate to Donor number 1003

3. Add Inc. to the end of Edgewater Tours


5. Change Williams first name to Bill


7. Change the state ofMD to ND (North Dakota) but do not move off of

the record

8. Click the Undo Record / Field button


10. Change Flexibility Co. to Flexibility, Inc.

11. Change the contact name to John Houser

12. Press Shift + Enterto save the changes to the record

13. Click on the Undo button


42/104



Edit Data With the Zoom Box

The contents of a field in the datasheet view can be difficult to read and editwhen the amount of data surpasses the width of the column. To compensate for

that, Access has incorporated the Zoom Box which allows the user to edit thecontents of a field in a larger window.

Access the Zoom Box feature by using the SHIFT + F2 keystroke. The resultingdialog box displays a much larger text area for editing. The Zoom Box can alsobe helpful for creating Expressions in a QBE grid.

When exiting the Zoom box, editing changes for this field are not saved in therecord, it is necessary to move off of the record or to press SHIFT + ENTER tosave.

Edit Data with the Zoom Box

1. Navigate to the first record with CTRL + Home

2. Move to the last column with the End key

3. Press the keystroke SHIFT + F2

4. Press CTRL + Home to move to the top of this field5. Add the word Additional to the first line of text then OK

6. Press the keystroke SHIFT + ENTER to save the record


43/104



Use AutoCorrect to Edit Data

Access 97 includes AutoCorrect which allows Access to monitor typing. UseAutoCorrect to automatically correct frequently mistyped text and to

automatically replace abbreviations with long names. Access will correct theerror after the spacebar is pressed.

To Use the AutoCorrect feature, go to Tools / AutoCorrect. Next, select theReplace Text As You Type check box. In the Replace box, type the text to becorrected automatically. Then, in the With box, type the corrected text, andclick Add.

To see how this feature works, type teh. AutoCorrect will change the wordafter the spacebar is pressed.


44/104


45/104

Lesson Three: Locating Records



1. The Find Feature

2. Sort Data

3. Filter the Data

4. Filter by Form

5. Filter by Selection

6. Save a Filter as a Query


46/104



The Find Feature

The Find feature in Access locates records that match specifiedcriteria. This feature is most often used in a Datasheet or Form view

of a Table. Access the Find dialog box by selecting Edit / Find fromthe menu bar or by clicking the Find button on the toolbar.

This feature locates records, but it does not replace the found data with otherdata. To perform a find and replace, use an action Query.

Before entering the Find dialog box, it is usually wise to activate the field to besearched. This makes it possible to select the Search Only Current Fieldoption in the Find dialog box. Narrowing the search down to one field decreasesthe amount of time it will take to find text.

Search In Current Field Choose for Access to search only thecurrent field or all of the fields in the Table.

Search Instructs Access where to begin searching:above or below the current record in theTable or all of the Table.

Match Case Access will find the text only if it matchesthe case.

Search Fields as Formatted Tells Access to find the text only if it isformatted the same as what is typed in theFind What text box. For example, dates arestored as numbers but may be displayed as

5 Jan 92.

Match Select the match type for the searchpartof field, the whole field, or the start of a field.


47/104



Locate Records with the Find Feature

1. Open the DonorTable

2. Press Tab twice to enter the Contact Name field

Make sure your cursor is in the Contact Name field- this is the field that the find

feature will search!

3. Click on the Find button on the toolbar

4. Type in the name Susan

5. Select Any Part of Field from the Match list

6. Click on the Find Next button

7. Notice how Susanna is also found

8. Click Find Next until you reach the end of the records

9. Click OK once you reach the end of the records


48/104



Sort Data

All data in Tables can be sorted according to different fields. Whensorting data, it is possible choose to use the Quick Sort buttons on the

toolbar to sort according to one field. There is also a Filter / Sortbutton to create sorts based on more than one field.

The default sort for any Table is the order of entry. The only time this changesis if a Primary key has been established in the Table. With a Primary Key setup, this field now becomes the default sort for the Table.

Quick Sort

After activating the appropriate field in the Table or Form view, click thequick sort button for ascending or descending order. This method ofsorting does not allow sorting on more than one key.

Sort the Data with the Quick Sort

1. Close the Donor Table Object

2. Open the Donations Table

3. Notice the records are sorted by date (the Primary Key)

1. Activate the Donation by clicking on it

2. Click on the Sort Descending button on the toolbar3. Notice the new order of records

4. Close the Table without saving and then reopen the Table

5. Notice the order has returned to its default


49/104



Sort on More Than One Field

By utilizing the Records/Filter/Advanced Filter / Sort option on the menu, thereare more advanced sorts that sort records on more than one field.

The Filter dialog box consists of a window with a list of fields from the Table anda grid in the bottom half of the window. Drag fields from the field list to the gridto determine which fields the Table will be sorted by and the sort order. Fieldscan also be double-clicked, typed, or selected from the drop down list in the grid

atThe bottom of the screen.


50/104



Sort by Two Keys

1. Close the Donations if necessary

2. Open the Donors Table Object

3. Notice the sort order

4. Click on Records/Filter/Advanced Filter / Sort

5. Drag the State field to the first column in the grid

6. Double-click the Company field to add it to the grid

7. Set the sort order to ascending for both columns

8. Click the Apply Filter button, and notice the new order

9. To return to the original order, click on Records/Remove Filter/Sort


51/104



Filter the Data

It may become necessary at some time to view a portion of the data in the Tablerather than every record. Use the Filter / Sort feature of Access to temporarily

eliminate the records which are not to be viewed.

For example, as a sales rep, it may be desirable to view only those clientsresiding in a particular city or state. Placing a criteria in the criteria row of thegrid filters out the undesirable data. The filter will remain in effect until the ShowAllRecords button is clicked, or until the Table is exited.

Practice Filtering Data

1. Click on Records/Filter/ Advanced Filter / Sort

2. Add the State field and the City field

3. Sort the City in ascending order

4. Add the criteria of TX for the State field

(Access will add the quotation marks)

5. Apply the filterby clicking on the apply filter button

6. Notice the total number of records showing


52/104



Filter by Form

Filtering by Form makes it possible to create a filter by typing the valuesbeing looked for in a fill in the blank Form or datasheet. Many people

find this feature easier to use than Advanced Filter/Sort.

Once the Table, Query, or Form to be filtered is open, click the Filter by Formbutton on the toolbar. A blank Form or datasheet containing the field namesdisplays. Select the field that will specify criteria that records must meet to beincluded in the filtered set or records. Select the search value from the drop-down list in the field, and apply the filter.

It is possible to specify alternative values that records can have and still make itthrough the filter by clicking on the Or tab at the bottom of the window andselecting the other value.

Practice Using Filter by Form

1. In the DonorTable, click on the Filter by Form button

2. Click in the City field and choose Austin

3. Choose the Apply Filterbutton to see your results

4. Click on the Filter by Form button again to add an additional filter

value

5. With Austin still highlighted in the City field, click on Orat the bottom

of the window

6. Select Charleston and apply the filter


53/104



Filter by Selection

With Filter by Selection, it is possible to specify which set of records isto be worked with by highlighting the search value inside the Form or

datasheet. Then, click the toolbar. Once the focus narrows to a specificset of records, narrow it even further by selecting another value from within thesubset of records.

Filtering by Selection instantly selects the data that matches the current field orselection. Filtering Excluding Selection is similar to filtering by selection; itselects the data that does not match the current field. This is done by right-clicking on the field and choosing Filter Excluding Selection.

Suppose there is a record that contains some data that is being looked for. Tofind other similar records, click in the field containing the desired information and

click the FilterbySelection button on the toolbar.

Practice Using Filter by Selection

1. Clear the previous filter

2. In the DonorTable, locate the State field

3. Select a record containing NV by clicking in that field

4. Click the Filter by Selection button on the toolbar and see yoursubset of data


54/104



Save a Filter as a Query

After applying a filter to the data, it is possible to have access to this filtered dataon a regular basis without recreating the filter every time. Since the filter is only

temporary, save the filter as a Query object.

By saving the filter as a Query, there is a new Query object available for use atany time. By saving it as a Query object, it also becomes possible to createForms and Reports based on the Query. Save the filter by returning to the

Advanced Filter/Sort window and selecting File / Save Filter as Query fromthe menu bar.

Practice Saving a Filter as a Query

1. Return to the Advanced Filter/Sort design window if necessary

2. Select File / Save Filter as Query from the menu bar

3. Name the Query object My Nevada Donors, and then click OK

4. Perform the Show All Records action by clicking the Apply Filter

button again

5. Close the Donors Table Object

6. Click the Query Objects tab and notice the new object


55/104

Lesson Four: Forms in Access



1. What is a Form?

2. The Form Wizard

3. Use a Form to Locate Records

4. Change the Design of a Form

5. Use AutoFormat with Forms


56/104



What is a Form?

Forms are generally used in Access for viewing one record at a time. View morethan one record at a time by using a tabular Form or a subform. The Forms in

Access and the available design capabilities are heralded in the softwareindustry as the best.

The Forms in Access can become quite complex from a design standpoint, andvery friendly from an end user standpoint. Forms are normally set up for the enduser as a graphical interface for performing data entry.

To move from one field to the next in a Form, either press the ENTER orTABkeys. To move backwards through the fields press SHIFT+TAB orSHIFT+ENTER. Navigate to the next or previous record by pressing the Page Down orPage Up keys, respectively. These keystrokes apply only if the Form is one

screen or less.


57/104



The Form Wizard

There are several ways to create Forms in Access. From the Forms Objects tabin the database window, click the New button. A dialog box displays with the

choice of using the Form Wizard or other types of Forms. The Form Wizard ishighly recommended. The Form Wizard makes it easy to create great-lookingdata entry Forms in a few seconds.

The Form Wizard in Access 97 is more powerful and easier to use than inprevious versions. It is now possible to choose fields from multiple Tables orQueries.

Before selecting either of these buttons, tell Access upon which Table or Querythat it should base this Form.

The Form Wizard is by far the best way to start Forms. Once Access hascreated the basic layout for the Table, switch to the Design View of the Form toalter the Form to meet the specifications.


58/104



Create a Form with the Wizard

1. While on the Forms tab, click the New Button

2. Select the Donors Table from the drop down list

3. Click the Form Wizard button

4. Click the double arrow to the right button and then Next

5. Select the Columnar Layout and then Next

6. Select the Pattern style then Next


59/104



Finish the Form Wizard

1. Add the title Donors Entry Form

2. Select to Open the Form to view or enter information, and then click

Finish

3. The Form should match the one pictured below


60/104



Use a Form to Locate Records

The Form is the most common layout for end users to interface with anydatabase. Once in the Form, it will become necessary to filter, sort, and located

records using the Form. In Access 97, Forms automatically inherit filters fromtheir source Tables or Queries.

Locate records in the Form with the Find button on the toolbar. Prior to clickingthe find button, activate the field in which to search. This will speed up thesearch process.

Find a Record in a Form

1. Activate the Company field (You must have your cursor in the field that you

want to activate)

2. Click the Find (binoculars) button on the toolbar

3. Type Second in the Find What Edit box

4. Select All in the Search list

5. Select Any Part of Field in the Match list

6. Click the Find Next button

7. Click the Close button to exit the Find feature


61/104



Change the Design of a Form

The design of the Form can be altered to meet any specifications. Access hasgained a reputation for its incredible ability to produce Forms and Reports with

great ease and flexibility.

From within the Form, switch to Design View by clicking the Design Viewbutton on the toolbar. The design view is divided into a header, detail, andfooter section. Most likely, the area of concern will be the detail section.

Header and Footer The information located in these sections only appears atthe top and bottom of the Form.

Detail This area contains all of the fields from the Table that tobe viewed and/or edited.


62/104



Design View Pointer Shapes

The pointer will take on many different shapes when the mouse moves in thedesign view. These shapes allow different operations to be performed with thefields in the Form.

Mouse Description Usage in Form DesignA large flat hand Used to move a data field and its label

to a new location in the Form. Placethe mouse pointer along the edge of aselected field.

A hand with the pointer finger extended Used to move only this field or object toa new location. Place the mousepointer on the handle in the upper leftcorner of the selected field

An I-beam Used to drop an insertion point forediting text in an object. Place themouse pointer in the middle of aselected field and then click.

A bi-directional arrow Used to resize fields and other objectsby clicking and dragging. Place themouse pointer on top of a resizehandle on a selected object.


63/104



Change the Design of the Form

1. Click the Design View button on the toolbar

2. Edit the captions forPhone and Zip to match the picture

3. Resize the City, Phone, Zip, Address, and Notes fields to match

4. Drag the fields to new positions to match the picture

5. Save the Form asMy Donor Entry Form

6. Switch to the Form View to see the changes


64/104



Use AutoFormat with Forms(For References Purposes)

To create a Form from scratch, go to the database window and select

Form, click the New button, and choose the Design View. At this point,the blank Form is an empty canvas upon which anything can be created. Initiallythe Form design will have a standard look. To choose a style for the design,click Format/AutoFormat or use the AutoFormat button on the toolbar.

It is also possible to use the AutoFormat after the creation of a Form. In theDesign View, select Format/AutoFormat or the AutoFormat button, and select thedesign to be applied to the Form. AutoFormat may also be used with Reports.


65/104

Lesson Five: Querying Data



1. What is a Query?

2. Use the Dynaset to Edit Data

3. Use an Expression as Criteria

4. Save the Query Object

5. Perform Calculations with a Query

6. Group and Totals of Queried Data

7. Query Several Tables


66/104



What is a Query?

The Query capabilities reveal the tremendous power of not only a relationaldatabase, but Access itself. The Query provides the user with answers to

questions about the data.

Queries allow the user to see the desired data in any specified order. It is alsopossible to perform calculations in data and make global changes to Tables.

When faced with a question like this: I would like to see a list of all of ourcustomers in California who purchased more than $1,000 worth of merchandisebetween the months of July and September of last year, Access can provide aresponse in a matter of seconds!

The resulting data is referred to as the Dynaset for the Query. This dynamic set

of data will constantly change based on changes made to the underlying Table.Any changes that are made to the Dynaset will also change the records in theunderlying Table.

While viewing the Query objects in the database window, click the New button.The resulting dialog box gives two options: to use the Query Wizard to createone of four types of Queries, or to create a new Query from scratch.


67/104



Query Wizard vs. From Scratch

The Query Wizard is probably the least useful of the Wizards. It can beconfusing at times. It is nice to be able to choose from four Query Wizards, butit may be more efficient to create Queries from scratch.

The Four Query WizardsQuery Wizards offer an easy way to perform special-purpose Queries.

Simple Query Retrieves data from one or more Tables anddisplays the results in a datasheet where the usercan update the records (with some restrictions). Itis also possible to use a Query to grouprecords and calculate sums, counts, averages,and other types of totals.

Crosstab Query Displays summarized values (sums, counts, and

averages) from one field in a Table and groupsthem by one set of facts listed down the left side ofthe datasheet and another set of facts listedacross the top of the datasheet.

Find Duplicates Query Determines whether a Table contains duplicatevalues in one or more fields. Use the results todetermine if there are duplicate records in theTable, or to determine which records in the Tableshare the same value.

Find Unmatched Query Finds records in one Table that do not haverelated records in another Table.


68/104



Once the design of a Query is entered, Access will prompt ask which from whichTables or Queries to pull data. It is possible to pull data from one or moreTables, as long Access can establish a common link between the Tables.

The Query By Example Grid

It is possible to create all Queries in Access by using the Query By Example(QBE) grid located in the Design view of Queries. Drag fields down to the QBEgrid, double-click them, or select them in the grid from the list box.

Field What is the name of the field from the Table?

Sort Shoulddata be sorted ascending or descending according to thisfield?

Show Should this field show up in the Dynaset?

Criteria Is there a criteria as to whether this field shows up in the Dynaset ornot?


69/104



Create a One Table Query

1. Click the New button while viewing the Query objects

2. Click Design View, and click OK

3. Select the Donor Table, click Add, and then Close

4. Add the fields picturedbelow by double-clicking on them

5. Add the criteria of TX to the state column

6. Switch to the Datasheet view of the Query

7. Notice the resulting Dynaset


70/104



Use the Dynaset to Edit DataThe resulting Dynaset of a Query is actually a filtered look at the Table(s) onwhich it is based. Although it looks and acts like a Table, the Dynaset is actuallya dynamic or live view of one or more Tables. The data in the Dynaset can be

changed, edited, or deleted, and the these changes are reflected in the actualTable(s) of the database.

If several clients have moved to another location, change their addresses byQuerying them out of the Table and then making the appropriate changes to theDynaset. Changes made in the Dynaset are updated in the underlying Tables.

Editing Records in the Dynaset

1. Return to the Design View of your Query

2. Change the state criteria toCA

3. Sort the Dynaset in ascending order according to the company name

4. Run the Query and notice the number of records

5. Delete the Records for Kahn Inc. and Kinders Preschool

6. Return to the Design View of the Query

7. Run the Query again

8. Notice the number of records


71/104



Use an Expression as Criteria

Dynasets will not always be based on a single criteria that matches text to afield. Many Queries will involve comparisons with numbers or dates. Most of

these comparisons will entail the usage of expressions.

For example, it is possible to see a list of everyone who spent more than $1,000dollars with a company in a single order. This expression would have a criteriaof Selling Price > 1000.

The following symbols are used to establish a relationship in the criteria.

Algebraic Symbol Relationship

= Equal to

> Greater than

< Less than>= Greater than or equal to


72/104



Other Criteria Terms

Like Likeis the proper syntax for telling Access to look for text or numbersthat are similar to the criteria. When typing in criteria using awildcard, such as the asterisk, Access uses the syntax Like B*(as

an example.)

Is This expression is generally used with the term Null. Null meansempty, but it does not equal zero. Zero is a value which meansneither positive nor negative. The criteria Is Nullwould extract thoserecords that do not contain any value in that field.

Between Use the between function to establish an inclusive range for thecriteria. For example, use it to see all transactions Between 1/1/94

And 12/31/95.

Use Expressions as Criteria1. Close down the current Query design

2. Click the New button while viewing the Query objects

3. Click Design View and click on OK

4. Add the Donations Table then click on Close

5. Add all of the fields to the QBE grid

6. Add criteria to answer each of the following questions:

7. How many HPIII donations were there?

8. How many donations were over $1,000?

9. How many donations were made between 1/1/92 and 3/1/92?

10.How many HPIII donations over $1,000 were made in December of

1991?


73/104



Save the Query Object

Query objects, once created, can be saved for later use. The Queryobject names are not limited to eight characters with a three character

extension because they are stored in the database file (MDB) created earlier.

By saving the Query, it is possible to access the information at a later date, butsince the data in the Table may have changed, the resulting Dynaset will also bechanged. Most Queries are saved for later usage as the basis for Reports.

Practice Saving a Query

1. From the Design view of the Query, click the diskette button

2. Type in the name My Donations

3. Click the OK button

4. Close the Query design window

5. Observe the new Query object in your database window

6. Open My Donations in the Design view


74/104



Perform Calculations with a Query

Should the need arise, it is possible to add a new field in the Query. This newfield may compute a product of two other fields in the Query Table. This field

could be the extended price of a product, the gross pay of an employee, or thecommission of a sale.

By typing in the expression in an available column of the grid, Access willcompute the expression and give the result in the Dynaset. When a new field iscreated in this manner, the field is now available for use in Reports based onthis Query.

Some common expressions to use in Access:

+ Add

- Subtract* Multiply\ Divide

Calculate in the Query

1. Return to the design view of the Query if necessary

2. Delete the Donor ID field from the QBE grid

3. In the first available column type [Donation]+[In-Kind Value]

4. Run the Query and notice the last column

5. Return to the design view by clicking on the design button

6. Delete Expr1 from your new field

7. Type in Extended Price and then Run the Query


75/104



Grouping and Totals of Queried Data

In the resulting Dynaset of a Query, data is filtered only if that particularfield includes a criteria in it. This type of grouping can yield computed

totals in the Query by department, product number, customer, etc.

Group data by a particular field in the QBE grid by turning on the Totals row ofthe grid. To do this, click the Totals button on the toolbar. The Totals buttonlooks like the Greek letter Sigma. By clicking this button, Access adds anotherrow to the grid.

As a default, the words Group by appear under each field. To reveal manyother available functions, drop down the list of choices available for the Totalcell. Additional available functions include Sum, Avg, Min, Max, and Count.These functions allow the user to use these fields for grouping, categorizing, and

subtotaling.

Grouping and Summing in a Query

1. Move to the Design view if

necessary

2. Delete the Donation Date,

Donation, In-Kind Value columns

3. Click on the Totals button on the

toolbar

4. Drop down the choices for the

Total cell under Extended Price

5. Select the Sum function

6. Run the Query and notice the

result


76/104



Query Several Tables

The true power of Access is revealed by its ability to allow the user to Queryinformation from multiple Tables. Querying data from several Tables is the end

result of the relational database.

As an example, to print invoices from a relational database, it would benecessary to pull the customer information from the Customer Table, the orderinformation from the Orders Table, and the product information from the ProductInformation. The relationships created in Access make the above actionspossible.

Once inside the Query Design view, it is possible to add more than one Table tothe window. The Table relationships are designated with lines drawn from onefield to the next. By dragging data fields from the several Tables shown, Access

automatically retrieves the requested data.


77/104



Table NamesUnder the View menu, there is a Table Names option. This option is used toadd a row to the QBE grid. This new row will show which Table the fieldoriginated from. This information is important for Querying multiple Tables.

Querying Several Tables

1. Close down the My Donations Query and Save it

2. Create a new Query by clicking on the New button

3. Click on Design View and click on OK

4. Add all three Tables to the Query grid

5. Click on View/Table Names

6. Drag down the fields shown in the picture

7. Add the criteria shown in the picture

8. Run the Query by switching to the datasheet

9. Notice the information from each Table is combined in the Dynaset

Create the Following Queries

1. The list for all donors who gave to the IBM 3rd

annual project (386)

2. The list of all customers in CA or NV who gave to the 3rd IBM project

3. The list of Donors who made a single donation greater than $1,000

4. The Donor contacts who gave to the 2nd

HP bake after February

1,1992


78/104


79/104

Lesson Six: Reporting the Data



1. What is a Report?

2. The Report Wizard

3. Customize the Report

4. Preview and Save a Report


80/104



What is a Report?

Reports in Access are created for presentation of Tabled or queried data in aprofessional manner. Programmers in Visual Basic and C++ utilize Access for

printing Reports because of its tremendous ease and flexibility.

Reports are based on either a Table or a Query. Any field can be dropped intothe Report from the available list of fields. The Report is broken down intoseveral sections. The sections are listed and defined below.

Report HeaderAnything in this section is placed at the top of the Report, and itwill only show up at the top of the first page. Generally, thename of the Report and the date go in this section.

Page Header Text and objects in this section will show up at the top of every

page. Most often, the column headings for the data appear inthis section.

Group Header If the Report is a Groups / Totals Report, this section willgenerally contain the heading for a group. As an example, thedepartment name would appear here if a list of employees bydepartment is shown.

Detail The detail section is normally the largest section of the Report.Place data fields that will print out the data in each record of theQuery or Table here.

Group Footer This area displays at the end of every group. It will generallycontain any totals that apply to the entire group.

Page Footer Information in this section will appear at the bottom of everypage. Users will often place the page number in this section.

Report Footer Information in this section appears only at the end of theReport. In a Groups / Totals Report, this section could containthe grand totals for information that was sub-totaled at the endof each group.


81/104



The picture below displays the design view for a grouping Report. Upon firstglance at a design for a Report, it can appear somewhat intimidating. Theconcepts of the Report are actually quite simple, as explained on the previouspage.

Observe the Report Design

1. Close down the Query object from the previous exercise if necessary

2. Move to the Report objects in the database window

3. Select the Report named Donations by Month

4. Click the Design button to observe the design

5. Close the Report object window


82/104



The Report Wizard

One of the greatest front end advancements from the previous version of Accessto the current one is the addition of Wizards, and of the Report Wizards in

particular. Accessing the Report Wizard removes the need to start Reports fromscratch. With the massive number of Reports created for a database, this is atime saver.

After clicking on the New button while viewing the Report objects, Access willask for the name of the Table or Query from which this Report is to be based.This version of Access offers five types of Report Wizards from which to choose.

After clicking on the appropriate Report Wizard button, the wizard will begin.

Types of Report WizardsThe first step in the use of Wizards is to select which one to use. Notice that

one of the wizards listed here is Mailing Labels. This means that it is no longernecessary to export the data to Microsoft Word to create mail labels, and that itis now possible to create the mailing labels in Access! The available ReportWizards are listed below.

Report Wizard Speeds up the process of creating a Reportbecause it does all of the basic work. The ReportWizard prompts for information and creates aReport based on the answers.

AutoReport: Columnar Each field appears on a separate line with a label

to its left. Does not ask any more questions.

AutoReport: Tabular The fields in each record appear on one line, andthe labels print once at the top of each page.

Chart Wizard Creates a chart with the Report.

Label Wizard Creates a Report formatted for mailing labels.


83/104



Create a Grouping Report

1. From the Report objects, click the New button

2. Click the Report Wizard button, and click OK

3. Select the Table Donations from the list of Tables / Queries

4. Select all fields for the Report by clicking on the right double arrow

5. Click the Next button

6. Double-click the Donation Date field7. Click the Grouping Options button and select Month


84/104



8. Click the Next button

9. Click the drop-down arrow and choose the Fundraiser ID field and the

Donation field as the fields by which to sort

10. Click the Summary Options button, select the sum for Donation and

In-kind Value, click OK, and then click Next

11. Select the Outline 1 layout, and click Next

12. Select the Corporate style, and click Next

13. Type in the title My Donations by Month

14. Select Preview the Report

15. Click the Finish button


85/104



Customize the Report

Once a Report is created in Access using a Wizard, the user may find itnecessary to make some desired adjustments to the Report prior to printing.

The following paragraphs describe the formatting options in the Design view.

Delete a Field Delete a field by selecting it with a click and thenpressing the delete key on the keyboard.

Move a Field Move fields by clicking and dragging.

Resize a Field Resize fields by clicking and dragging the resize handlesthat appear when a field is selected. Do not make a fieldso small that it hides the data.

Resize a Section Resize the different sections of a Report to allow formore fields by placing the mouse pointer near the bottomof the section. The mouse should take the shape of atwo-headed arrow. Resize the section with a click anddrag either up or down.

Add Lines / Color Draw color and lines by using Back Color, Fore Color,and Border Color for the Report. These are buttons, andthey are located on the toolbar. By first selecting anobject and then a color from the palette, format theobjects on the Report. Draw lines and boxes by first

selecting the object and then clicking on the BorderColor button.

In the Design View of the Report, the Toolbox button on the toolbar opensthe Formatting (Form/Report Design) Toolbar for additional formattingoptions.


86/104



Customize a Report

1. Go to Design View of the Report

2. Click the Heading object to select it, and drag it so that it extends

over the entire width of the Report

3. Click the Center button on the toolbar

4. Drop down the list of available fonts and select Arial

5. Change the point size of the font to 18

6. Dragthe Donation Date Footerto the same height as the header

7. Select both sums by using a Shift + Click

8. Click each sum object in the footer and extend the height

9. Delete the Donation Date from between Donation and In-Kind Value

(on the Header and the Detail)

10. Resize the Donation and the In-Kind Value fields from the right (on

the Header and the Detail)

11. This action will line up the numbers with the headings

12. The Report should resemble the picture below


87/104



Preview and Save a Report

Preview a Report

The Print Preview button on the toolbar will Preview the Report.

This preview is true WYSIWYG. Access uses actual data from theQuery or database and displays an actual preview of the data.

Layout Preview is used to get a quick peek at how the Report is goingto be laid out. This button should only be used to check the verticaland horizontal alignment of data and labels in the Report. Access doesnot perform the actual Query, and it does not pull all true data from the Table.The data may be mixed up; for instance, the contact name of one person and thecompany name of another person may appear together. To use Layout Preview,click the Report View drop-down arrow and choose Layout Preview.

Warning: The Layout Preview button is for layout purposes only! Thedisplayed data is only a sample, and it is not true WYSIWYG.

Save a Report

Once the Report is formatted, save the Report from the Design viewby clicking the diskette button on the toolbar. This saves the Reportunder the name given during the creation of the Report. As with other objects in

Access, the Report name length is not limited to eight characters and a threecharacter extension.

Save the Report1. From the Design View, go to File/Save As

2. Save the Report as My Donationsby Month, and click OK

3. Close the Report


88/104


89/104

Lesson Seven: Importing Data



1. Import Data from Microsoft Excel

2. Exit Access


90/104



Import Data from Microsoft Excel

As an estimate, 90% of all Access users are converting from another Form ofdatabase. The most common question is, Can I transfer all of my data into

Access without any major headaches? The answer is a resounding, YES!

Import from the database window by selecting File/Get External Data and thenchoosing Import or Link Tables from the menu bar.

Access opens the Import dialog box and asks the user to select the location ofthe file, the file type, and the file name.


91/104



Import Data from an Excel File

1. Click File/Get External Data/Import

2. Select Microsoft Excel from the Files of Type list

3. Select A: for the files location

4. Select XLIMPORT.XLS from the diskette, and then IMPORT

5. Select Show Worksheets,and click Next

6. Select First Row Contains Column Headings and click Next

7. Select to place the data in a newtable, and click Next

8. Do not specify any new fields; click Next

9. Let Access add a Primary Key, click Next, and then Finish

10. Notice that there is now a new Table object called XLIMPORT


92/104



Exit Access

To exit out of Access, close and save all databases, and then go to File /Exit oruse the ALT + F4 keystroke.

Exit Out of Access

1. Save and close any open database

2. Go to File / Exit on the Menu bar


93/104

Glossary


Index

Add Field Lines / Color, 85

AutoCorrect, 43

AutoFormat, 64AutoReport: Columnar, 82

AutoReport: Tabular, 82

BlankDatabase, 21

Chart Wizard, 82

Control Menu Icon, 14

Counter, 26

Creating a New Database, 21

Creating a Table, 24

Creating Relationships, 37

Criteria, 68, 71, 72Criteria Symbols, 71

Crosstab Query, 67, 96

Currency, 26

Customize a Report, 86

Customizing the Report, 85

Data Entry Symbols, 32

Database, 16, 96

Database Terms, 16

Database Window, 15

Database Wizard, 17

Datasheet view, 40

Datasheet View, 25, 32Date / Time, 26

Delete a Field, 85

Deleting a Relationship. SeeTable

Relationship

Design of a Form, 61

Design view, 35

Design View, 25, 61, 64

Design View Pointer Shapes, 62

Detail, 61, 80

Dynaset, 32, 66, 69, 70, 74

Edit Data, 70

Editing a Relationship, 37

Editing Data, 40, 42

Entering Data, 32

Entering Records, 34

Exit, 92

Expression, 42, 71, 72

Field, 16, 26, 27, 32, 41, 47, 49, 68, 71, 85, 102

Field Data Types, 26

Filter by Form, 52

Filter by Selection, 53

Filter Data, 51

Filtering, 51Find Feature, 46, 47

flat database, 11, 98

Form, 16, 56, 60

Form Wizard, 57, 58, 59

Forms, 64

Glossary, 95

Group Footer, 80

Group Header, 80

Grouping and Totals, 75

Header and Footer, 61

Import Data, 91

Importing Data, 90

Label Wizard, 82

Locate Records, 60

Locating Records, 45

Match Case, 46

Maximize, 14

Memo, 26

Menu Bar, 14

Minimize, 14Move a Field, 85

Multiple Primary Keys, 28

Navigating With the Mouse, 33

Number, 26

Object, 16, 26, 50, 73, 97, 101

OLE Object, 26

Page Footer, 80

Page Header, 80

Performing Calculations, 74

Primary Key, 28, 39Print Preview, 87

QBE grid, 42, 68, 72, 74, 75, 77

Query, 16, 54, 66

Query By Example Grid, 68

Query Wizard, 66, 67

Querying Several Tables, 76

Quick Preview, 87


94/104

Glossary


Quick Sort, 48

Record, 16, 98

Referential Integrity, 38

relational database, 11, 102

Report, 16, 80, 98

Report Footer, 80Report Header, 80

Report Sections, 80

Report Wizard, 82

Reporting the Data, 79

Resize a Field, 85

Resize a Field Section, 85

Restore, 14

Save a Report, 87

Saving a Filter, 54

Saving a Report, 87

Saving Records, 33

Saving the Query, 73

Scratch, 31

Scroll Bars, 15

Set a Primary Key, 28

Sort Data, 48, 49

Sorting, 50

Start Access, 13

Status Bar, 15

subform, 56

Table, 16

Table Creation, 24Table Design, 35, 36

Table Names button, 77

Table Relationships, 37

Table Wizard, 29

tabular form, 56

Text, 26

Title Bar, 14

Toolbars, 15

Toolbox button, 85

Undo, 41

Undo Record, 41

Wildcards, 71

WYSIWYG, 87

Zoom Box, 42


95/104

Glossary


Glossary

AActionThe basic building block of a macro. A self-contained instruction that can be combined withother actions to automate tasks.

ActiveA command in a menu, window, or icon that appears inverted or in dark letters.

ApplicationA program used for performing a certain type of work, such as word processing, spreadsheets,graphics, projects, presentations, databases, and desktop publishing. This term is usedinterchangeably with "Program." There are both Windows Applications and Non-Windows

Applications.

Arrow KeysThe keys used to navigate around the screen. They are usually located between the numberkeypad and the typewriter pad on the keyboard. There is an up, down, left, and right arrow key.

BBinary

A number system that uses only combinations of 0 and 1. These digits can be used to representoff and on or true and false. The binary number system is what all digital computers use.

BooleanAn expression that can be evaluated as being either true (non-zero) or false (zero). The fielddata type Yes / No is Boolean; Yes = 1, No = 0.

Burn-inA condition a monitor can get that causes faded images to remain on the screen causingdamage to the display. Using a sc

accessmanual tx

Documents