For Evaluation Only. Access Microsoft Business Certification Series. Microsoft Office. Level 1 Essential Skills Courseware #:

(1)

Microsoft

^®

Office

Access ²⁰⁰⁷

Level 1 – Essential Skills Courseware #: 6266-1

March 2008 Microsoft Business Certification Series

For Evaluation Only

(2)

Microsoft^® Office Access 2007 – Level 1

Developers: Catherine Barker (formerly Catherine Payne) Editors: Sue Wong, Cristina Goodwin

Publishers: Kelly Hegedus, Kevin Yulo

This courseware is one in a series prepared by CCI Learning Solutions Inc. for use by students and instructors in courses on computer software applications. CCI designed these materials to assist students and instructors in making the learning process both effective and enjoyable.

This training manual is copyrighted and all rights are reserved by CCI Learning Solutions, Inc. No part of this publication may be reproduced, transmitted, stored in a retrieval system, modified, or translated into any language or computer language, in any form or by any means, electronic, mechanical, magnetic, optical, chemical, manual or otherwise without written permission of CCI Learning Solutions, Canada: 1-800-668-1669.

The information in this courseware is distributed on an “as is” basis, without warranty. While every precaution has been taken in the preparation of this courseware, neither the author nor CCI Learning Solutions Inc. shall have any liability to any person or entity with respect to any liability, loss, or damage caused or alleged to be caused directly or indirectly by the instructions contained in this courseware or by the computer software and hardware products described therein.

CCI Learning Solutions Inc. would like to acknowledge the financial support of the Government of Canada through the Book Publishing Industry Development Program for our publishing activities.

Working With the Data Files

The exercises in this courseware require you to use the data files provided for the book. Follow the instructions shown to download the data files for this courseware.

1 Launch your browser and navigate to the CCI Web site location http://www.ccilearning.com/data.

2 Enter 6266 in the Courseware # box and click .

3 Select the 6266-1-student-data.exe file then click Run. Click Run again in the Internet Explorer – Security Warning window, if necessary.

4 In the WinZip Self-Extractor dialog box, use the Browse button to specify the Windows Desktop as the location to unzip the file, and then click Unzip.

5 The 6266-1 Student folder, containing the required student work files, has now been downloaded to your desktop. It is recommended that you rename the folder using your own name before starting the exercises in this courseware. You can reinstall and use the work files as many times as you like.

All student data files have been checked for viruses at the time of development. CCI and its developers are not responsible for any virus infection that may occur as a result of customer or distribution channel manipulation. Please ensure your anti-virus software is updated with the latest virus pattern to prevent new viruses from infecting your system.

For Evaluation Only

(3)

Lesson 3: Manipulating a Table

Lesson Objectives ... 69

Lesson 3 Buttons ... 69

Restructuring Tables ... 70

Formatting Tables ... 73

Adding a Total Row ... 77

Modifying Tables ... 79

Modifying Table Properties ... 79

Renaming Tables ... 82

Deleting Tables ... 83

Printing and Distributing Tables... 84

Saving a Table as a Web Page ... 87

Exporting as PDF or XPS Format ... 89

Lesson Summary ... 91

Review Questions ... 92

Lesson 4: Data Manipulations

Inserting and Deleting Records ... 94

Changing Data and Undoing Changes ... 96

Attaching and Detaching Documents to Records ... 98

Importing and Exporting Data ... 102

Importing Data from Other Applications ... 102

Saving and Running Import Specifications ... 106

Exporting Data to Other Applications ... 110

Saving and Running Export Specifications ... 113

Finding and Replacing Data ... 116

Lesson 5: Sorting and Filtering

Sorting Records ... 124

Filtering Records ... 127

Lesson 6: Select Queries

What is a Query?... 138

Creating a Query using the Wizard ... 138

Creating a Query in Design View ... 142

Saving, Closing and Running Queries ... 145

Adjusting Queries ... 147

Adding, Modifying, Deleting and Changing the Order of Fields ... 147

Sorting Fields ... 149

Showing and Hiding Fields ... 152

For Evaluation Only

(4)

Applying Criteria ... 155

Using Comparison Operators ... 156

Using Wildcard Characters ... 161

Using the AND Operator ... 163

Using the OR Operator ... 165

Using AND and OR Operators in the Same Field ... 167

Functions and Calculated Fields ... 169

Using the Date Function ... 169

Calculated Fields ... 171

Changing Field Properties ... 174

Working with Aliases ... 175

Filtering Query Data ... 176

Lesson 7: Relating Multiple Tables

Establishing Table Relationships ... 182

Understanding Relationships, Types of Relationships and Prerequisite to Defining Relationships ... 182

Enforced Referential Integrity ... 189

Using Cascade Update and Cascade Delete ... 193

Print Relationships ... 194

Using the Table Analyzer ... 198

Lesson 8: Maintaining Databases

Database Maintenance ... 210

Backing Up and Restoring a Database ... 210

Compacting and Repairing a Database ... 211

Working with Object Dependencies ... 212

Opening Databases ... 214

Modify Database Options ... 215

Modifying Database Properties ... 217

Using the Database Documenter ... 219

Saving a Database to a Previous Version ... 222

Saving a Database with a Password ... 223

Splitting Databases ... 224

Appendices

Appendix A: A Quick Review of the Basics ... A 2 Appendix B: Answers to Review Questions ... A 13 Appendix C: Courseware Mapping ... A 23 Appendix D: Glossary of Terms ... A 24

For Evaluation Only

(5)

About This Courseware

Courseware Description ...vi

Course Series ...vi

Course Length ...vi

Course Prerequisites ...vi

Classroom Setup ...vi

Course Design ... vii

Course Objectives ... viii

Conventions and Graphics ... viii

Lesson 1: Introducing Access

Overview ... 2

What is a Database? ... 2

What is Access? ... 2

Looking at the Screen ... 5

Using Screen Tips ... 7

Using the Ribbon ... 7

Using the Quick Access Toolbar ... 9

Planning a Database ... 14

Planning Questions ... 14

Designing the Database ... 14

Defining Table Fields ... 18

Defining Appropriate Data Types ... 19

Defining Tables ... 21

Creating a Database Using a Template ... 24

Moving Around a Database ... 26

Creating a Blank Database ... 32

Organizing Files ... 34

Saving a Database ... 34

Closing a Database ... 34

Opening a Database ... 35

Lesson 2: Creating and Modifying Tables

Creating Tables using Datasheet View ... 38

Moving Around a Table ... 42

Inserting, Deleting and Modifying Data in a Table ... 44

Creating Tables using Table Templates ... 50

Creating Tables Based on Existing Tables ... 50

Creating a Table in Design View ... 52

Modifying Data Types ... 55

Modifying Field Properties ... 58

Adding and Removing Primary Keys ... 64

Adding and Removing Multiple Primary Keys ... 65

For Evaluation Only

(6)

Course Description

Microsoft^® Office Access 2007 – Level 1 teaches students how to create simple databases, create and modify database objects including tables, forms, reports and queries. You will use a variety of commands, functions, and Microsoft Office Access 2007 capabilities. It is designed for computer users who are new to database programs or who only plan to use Access occasionally.

Students who complete this course can be on their way to preparing for a Microsoft Certified Application Specialist exam.

By completing the other levels available for this application students will have reviewed all of the exam objectives.

Course Series

Microsoft Office Access 2007 – Level 1 is one of the courses in the Microsoft Business Certification Series.

Other courses available in the series include:

• Microsoft Office Access 2007 – Level 2

• Microsoft Office Word 2007 – Level 1, Level 2, Level 3

• Microsoft Office Excel 2007 – Level 1, Level 2, Level 3

• Microsoft Office PowerPoint 2007 – Level 1, Level 2

• Microsoft Office Outlook 2007 – Level 1, Level 2

• Microsoft Windows Vista – Level 1, Level 2

Course Length

The Microsoft Business Certification Series can adapt to meet various course length requirements. There are two types of exercises in this book: Learn the Skill and Practice the Skill (refer to the Conventions and Graphics section to identify the different icons used for each type).

For the shortest course lengths, students can perform just the Learn the Skill exercises.

For longer course lengths, students can complete both the Learn the Skill and Practice the Skill exercises.

Instructors should refer to the Instructor Resources for this book to learn more about how the material can be adapted.

Course Prerequisites

This course is designed for students who are familiar with personal computers, using a keyboard and using a mouse. The course assumes that students have completed the Microsoft Windows Level 1 course or have equivalent Microsoft Windows knowledge and experience.

Students who wish to become proficient using the features of Microsoft Office Access 2007 will benefit from taking this course.

Classroom Setup

If you are upgrading from Microsoft Office 2003 to the 2007 release, additional hardware should not be required, although an operating system upgrade may be required. If you are upgrading from Microsoft Office 2000 or Office XP to the 2007 release, ensure that the hardware and operating system meet the minimum Microsoft Office 2007 system requirements (as published by Microsoft). Instructors should evaluate classroom computers before installing Microsoft Office 2007 system products to verify that the student computers meet or exceed the minimum system requirements.

It is likely your instructor set up the classroom computers based on the system requirements to run the software for this course. Most software configurations on your computer are identical to those on your instructor’s computer. However, your

For Evaluation Only

(7)

Course Design

This course book was developed for instructor-led training and will assist you during class. Together with comprehensive instructional text and objectives checklists, this course book provides easy-to-follow hands-on lab exercises and a glossary of course-specific terms.

This course book is organized in the following manner:

Microsoft Office Access 2007

Table of Contents

^Lessons

Lesson Objectives

Narrative Text 3 Graphics 3 Tips and Tricks 3 Tech Notes

^Exercises

3 Graphics 3 Tips and Tricks 3 Tech Notes

Lesson Summary

Lesson Review

Appendices

A Review of Basics

Apply Your Skills Exercises

Answers to Review Questions

Courseware Mapping

Glossary of Terms

^Index

When you return to your home or office, you will find this course book to be a valuable resource for reviewing exercises and applying the skills you have learned. Each lesson concludes with questions that review the material. Lesson review questions are provided as a study resource only and in no way guarantee a passing score on a certification exam.

Appendixes in the back of this course book provide additional information, resources, and answers to review questions.

For Evaluation Only

(8)

Course Objectives

After completing this course, you will be able to:

¬ identify parts of the Access screen

¬ define fields, data types and tables

¬ create a database using templates, create a blank database, open, save and close databases

¬ create a table using Datasheet View or Design View

¬ insert, delete and modify data in a table

¬ add, modify and remove primary key fields

¬ insert, modify and delete a field

¬ add a total row to a table

¬ format, rename and delete tables

¬ save database items in a variety of file formats

¬ import or export data from or to other applications

¬ find and replace data using exact or partial matches, case sensitive and wildcard searches

¬ sort and filter data

¬ create, save, close, or run a query using the wizard or in Design View

¬ create query criteria using comparison, wildcard, AND, OR operators, or calculated fields

¬ change queries field properties and apply aliases

Conventions and Graphics

The following conventions are used in CCI coursebooks.

File Names or Database Field Names

File names or database field names are indicated in italic font style.

Exercise Text Content to be entered by the student during an exercise appears in Courier New font.

Procedures Procedures and commands for completing a task are indicated in bold font style.

Menu Options and Features Menu options and features are listed in the left hand column and corresponding descriptions are in the right hand column.

The following graphics are used in CCI coursebooks.

Specific Keyboard Graphics to easily identify the key on the keyboard to press.

Technical Notes point out exceptions or special circumstances that you may find when working with a particular procedure, or may indicate there is another method to complete the task.

Learn the Skill

Learn the Skill graphics signal the start of step-by-step hands-on exercises or other activities.

Practice the Skill

Practice the Skill exercises follow Learn the Skill exercises and provide additional practice and reinforcement or may present an alternative method of completing a task.

Indicates the numbered objective from the Microsoft Certified Application Specialist exam being covered in this topic.

For Evaluation Only

(9)

Lesson 1: Introducing Access

Lesson Objectives

The objectives of this lesson are to introduce the basic features of Microsoft Access. Upon successful completion of this lesson, you should be able to do the following:

identify parts of the Access screen

define fields, data types and tables

create a database using templates

move around a database

create a blank database

open, save and close databases

Lesson 1 Buttons

The following command buttons are covered in this lesson:

Quick Access Toolbar Customize Quick Access Toolbar More

Miscellaneous

Office Button

Navigation Pane menu with Shutter Bar Open/Close button

Search Bar with Clear Search String button

For Evaluation Only

(10)

Overview

What is a Database?

A database is a tool for storing, collecting and organizing related information. Databases are either manual or computerized filing systems that can store information about people, products, orders, or anything else. Some examples of a database are; supplier lists, inventory tracking, survey/voting results, reservation systems and customer management systems.

Many databases start as a simple list in a spreadsheet or word processing program. As the list gets bigger, redundancies, anomalies and inconsistencies begin to appear in the data. The data becomes difficult to comprehend as a list, and there are limitations in sorting, searching, reporting or extracting subsets of data.

The more information the database is required to store and manage the more appropriate it becomes to create a computerized database. A computerized database ensures data is located, sorted, extracted, displayed and reported quickly and effectively, and that the integrity, validity and security of the data in the database is improved compared to a manual database.

A database can be either simple or complex. A complex database system might include a variety of data files, and other computer-based tools to gather, store, manipulate, organize and display data. The system could also manage security and back-up capabilities. A system that is used to store, process and manage data in a systematic way is known as a Database Management System or DBMS.

What is Access?

Microsoft Office Access 2007 is a database management system (DBMS), as it is used by organizations to manage simple lists or to design complex multi-user database applications over local area networks and the Internet. Access provides a powerful set of tools that enable you to create a database and start entering, organizing, searching, sorting, tracking, reporting, and sharing information in a systematic way.

An Access database is a container of objects; it can contain one table or a collection of tables, forms, reports and queries. For example, a call tracking system that uses three tables is not three databases, but one database containing three tables; one that contains the callers’ details, another than contains the call content details and another that contains follow-up details.

An Access 2007 database stores its tables and objects in a single file that has the file extension .accdb, and databases created in earlier Access formats have the file extension .mdb. You can use Access 2007 to create files in earlier file formats (for example, Access 2000 and Access 2002-2003).

Using Access, you can:

• Add new data to a database, such as a new customer.

• Edit existing data in the database, such as changing the customers address.

• Delete information, for example, if a customer has relocated.

• Organize and view the data in different ways, such as view a list of customers by city.

• Share the data with others via reports, e-mail messages, an intranet, or the Internet.

For Evaluation Only

(11)

Tables

The basic building block of any database is a table. A database table is similar in appearance to a list or spreadsheet, in that the data is stored in rows and columns.

Each row in a table is a record, where the individual pieces of information are stored, across the row. Each record consists of one or more fields. Fields correspond to the columns in the table. For example, you might have a table named “Fleet” where each record (row) contains information about a different vehicle in a vehicle fleet, and each field (column) contains a different type of information, such as make, model, year, and so on. Fields must be designated a data type, whether the data contained in the field is text, number, date or time, or some other type.

The main difference between storing your data in a spreadsheet and storing it in a database is in how the data is organized into tables. In a database, the data is organized into tables so that duplication of data does not occur.

For example, if you are storing information about vehicles, each vehicle should only be entered once in a vehicle table. Data about drivers will be stored in a drivers table, and data about vehicle servicing will be stored in a servicing table. This process of putting data into separate and related tables is called normalization.

A complex database might contain a dozen or more tables: many of which might be related to each other by a common field. The ability to work with multiple related tables is a feature of a relational database. Different types of relationships can be created.

Forms

Forms are commonly displayed as data entry screens. They are the interfaces that you work with when working with data, and they often contain elements and command buttons that make data entry and performing various tasks quick and easy.

You can create a database without using forms by simply editing your data in the table datasheets. However, most database users prefer the user-friendly forms for viewing, entering, editing and deleting data in the underlying table.

Forms enable you to control how users interact with the data. For example, you can create a form that shows only certain fields and enables only certain operations to be performed. This helps protect the data and ensure that valid data is entered properly and that the database data has integrity.

For Evaluation Only

(12)

Reports

You use reports to print and summarize data in tables. A report can print a subset of forms. For example, a report can print, “How many vehicles do we have in the fleet?”, “How much in sales did we make this month?”, or “In what cities are our customers located?”, or a catalog of services, or a month of invoices. Each report is formatted to present the information in the most readable way possible.

You can run a report at any time and it will always reflect the current state of the data in the database at that time.

You use reports generally for printing, but you can also view them on the screen, exported them to another application, or you can send a report as an e-mail message.

Queries

You use queries to extract subsets of data from one or more tables. The data you want to see can be stored in several tables, and a query lets you to view data from several tables in a single datasheet. In addition, because you generally do not want to see all the records at once, queries let you select certain fields and add criteria to

“filter” specific records.

The result of a query is referred to as “dynaset,” because it is a dynamically changing subset of data from one or more tables. A query retrieves data and makes it available for use, you can view the results of the query on the screen, print it out, or copy it to the clipboard, or you can use the output of the query as the record source for a form or report.

Macros

Macros add functionality to your database by automating specific tasks. For example, you can attach a macro to a button on a form so that the macro runs whenever the button is clicked. Macros contain actions that perform tasks, such as adding, deleting, or printing a record, opening a report, running a query, or closing the form. Macros are timesaving devices as they automate database operations.

Modules

Modules also add functionality to your database. You can write modules in the Visual Basic for Applications (VBA) programming language. Writing modules enables you to create procedures that run complex and intelligent time saving automated operations to a database.

As this course is an introduction to basic and intermediate skills for Access 2007, not all of the features above are covered in this courseware.

For Evaluation Only

(13)

Looking at the Screen

Microsoft Access is a member of the Microsoft Office Professional suite and shares many common features with Excel, Word, PowerPoint and Outlook. When you start Access, a screen similar to the one shown here appears:

To create a new database, click Blank Database, click Create and click the Home tab to view the screen shown here:

View Buttons Search

Filter Navigation Buttons

Access Work Area

Close Object Button Microsoft Office Access Help Button

Split Bar

Group Dialog Box Launcher

Ribbon Tabs Command button

Navigation pane

Quick Access Toolbar Ribbon Title Bar Minimize, Restore, Close Buttons

Microsoft Office Button

For Evaluation Only

(14)

Microsoft Office Button

Located in the top left corner of the Access window, this button when clicked, displays a menu from which you can select important commands (e.g., New, Open, Save, etc.).

Each of the commands displayed in the menu contains a set of sub-commands to manipulate the database. You can also double-click the Office Button to exit the Access program.

Quick Access Toolbar

This toolbar is located to the direct right of the Office Button and provides quick access to frequently used commands. You can easily customize the toolbar to contain commands and macros that you use regularly.

Title bar Located at the top of screen, the title bar indicates the contents of the window (e.g., Database1-Microsoft Access.)

Ribbon This is a collection of tabs (e.g., Home, Create, External Data, Database Tools, etc.) and is located directly below the title bar, providing quick access to commands you must use to complete a task.

Ribbon tabs Each Ribbon tab relates to a type of activity, such as creating objects in a database, or working with external data. You will see extra tabs when applicable; for example, you will see the Report Design Tools tab when you click a field on a report.

A Ribbon tab organizes command buttons into logical groups. The group name appears on the Ribbon tab below the group of command buttons.

Command buttons Command buttons appear on Ribbon tabs in groups. When you click a command button, an action occurs. For example, when you click Bold, the bold format is applied.

Dialog box launchers

Some of the groups in a tab contain a dialog box launcher, a diagonal arrow to the right of the group name, providing quick access to a dialog box with more commands or options.

Minimize/Maximize/

Restore/Close button

Located in the top right corner of the Access window, these buttons enable you to minimize ( ) the program to a button on the taskbar, restore ( ) the program to its former size, maximize ( ) the window to full screen, and close ( ) the database.

Microsoft Office Access

Help button

Located directly below the Close button, the Help feature provides access to information and tips about the program’s features. You can enter a question using your own words or you can type in keywords in the Search field to display Help topics you want to learn more about.

Navigation Pane Located on the left side of the screen, the Navigation Pane provides access to database objects including the tables, forms, reports, and other components. For example, you use the Navigation Pane to open objects to use them, or to change their design.

Access Work Area The Access work area is the large area to the right of the Navigation Pane. When objects in the Navigation Pane are opened, they appear in the Access work area so that they can be used.

Navigation buttons Located at the bottom of the window, the Navigation buttons enable you to move between records in a table or form. The first button moves you to the first record; the second button moves you to the previous record, the third button moves you to the next record, the fourth button moves you to the last record and the last button creates a new blank record.

Filter button Located at the bottom of the window to the right of the navigation buttons, this button enables you to remove or re-apply a filter.

Search button Located at the bottom of the window to the right of the Filter button, this button enables you to search and find characters in the table records.

For Evaluation Only

(15)

Status bar Located at the bottom of the screen, the status bar indicates the currently displayed view (Datasheet View). You can right-click the status bar to show or hide elements on the status bar.

Split Bar Drag this bar to increase or decrease the size of the Navigation Pane, or to increase or decrease the size of the object you are viewing to the right of the Navigation Pane.

Close Object Button

Closes the object viewed on the right of the Navigation Pane.

View Buttons Located on the right of the status bar, they enable you to switch between alternate views of the currently object, such as between Datasheet View and Design View.

The previous illustration displays an example of commonly used elements of the Access screen. You can customize the appearance of your screen, so not all elements shown in the example will necessarily display on your screen. Many of these screen elements will display when you use them regularly; otherwise, Access automatically hides them until you activate them.

Using Screen Tips

Identifying buttons and other interface elements on the screen is easier with ScreenTips. To view a ScreenTip, use the mouse cursor to point to the object and wait a second. A tip will then pop up displaying the name of the button or interface element and a brief description of its purpose.

You can turn the ScreenTips on or off as desired. They can be very helpful when you are learning how to use Access; however, you have the option to turn them off when you become more familiar with the interface.

To set the type of ScreenTips assistance you want, click the Office Button and then click Access Options. In the Popular category, click the arrow for ScreenTip style and select the appropriate option from the list.

You will find that you can customize a number of the elements shown in this section to be displayed or hidden, based on your preferences. In most cases, you can set items by clicking the Access Options command on the Office Button.

Using the Ribbon

The Ribbon is designed to help you quickly find the commands that you need to complete a task. Commands are grouped logically on tabs. Each tab relates to a type of activity, such as creating a form. To reduce screen clutter, some tabs appear only when they are applicable.

For Evaluation Only

(16)

Keeping the Ribbon Minimized

You cannot delete or replace the Ribbon; however, you can minimize the Ribbon to make more space available on your screen.

You can access any command by clicking the applicable tab. After you click the option or command you want to use, the Ribbon will automatically be minimized again.

To minimize the Ribbon, on the Quick Access Toolbar, click Customize Quick Access Toolbar, and then click Minimize the Ribbon.

Restore the Ribbon

Use one of the following methods to restore the Ribbon:

• Double-click a tab to restore the Ribbon, or

• press + , or

• on the Quick Access Toolbar, click Customize Quick Access Toolbar, and then click Minimize the Ribbon.

Work with the Ribbon using the Keyboard

If you prefer to use the keyboard instead of the mouse, Access provides keyboard shortcuts that enable you to perform tasks quickly.

To access the Ribbon using the keyboard, press either or .

The KeyTips are displayed over each feature available in the current view. You can press the letter shown in the KeyTip over the feature you want to use.

For Evaluation Only

(17)

Depending on which letter you press, you may see additional KeyTips. For example, if the Home tab is active and you press , the Create tab is displayed as well as the KeyTips for the groups in that tab. Continue pressing letters until you press the letter of the specific command or option that you want to use. In some cases, you have to first press the letter of the group that contains the command.

To cancel the action that you are taking and hide the KeyTips, press and release the key.

Using the Quick Access Toolbar

The Quick Access Toolbar provides quick access to frequently used commands. By default, the Access Quick Access Toolbar contains the Save, Undo and Repeat commands; however, you can easily customize the toolbar to contain commands and macros you use regularly.

Customizing the Quick Access Toolbar

This customizable toolbar contains commands that are independent of the tab that is displayed. You can add commands to the Quick Access Toolbar from a list of commands in the Access Options dialog box. To customize the Quick Access Toolbar, use one of the following methods:

• Click the Office Button and click Access Options. In the Access Options list, click Customize, or

• click Customize Quick Access Toolbar and click More Commands. Click the Choose Commands from drop-down and select a command category (as necessary) from the list. In the Commands list, click the command you want to add to the Quick Access Toolbar, and then click Add, or

• to add a command to the Quick Access Toolbar directly from commands on the Ribbon, click the appropriate tab to display the command that you want to add. Right-click the command and click Add to Quick Access Toolbar on the shortcut menu.

To remove a command, select the command from the Customize Quick Access Toolbar list and click Remove.

To rearrange commands, select the command from the Customize Quick Access Toolbar list and click Move Up or Move Down.

For Evaluation Only

(18)

Moving the Quick Access Toolbar

You can move the Quick Access Toolbar to one of two possible locations. If you do not want the Quick Access Toolbar displayed in its default location next to the Office Button, you can move it to display below the Ribbon.

To change the location of the Quick Access Toolbar, click Customize Quick Access Toolbar and then click Show Below the Ribbon or Show Above the Ribbon.

Learn the Skill

This exercise provides practice with viewing ScreenTips, minimizing the Ribbon, and customizing the Quick Access Toolbar.

1 Start Microsoft Office Access, if not already open.

2 Click Blank Database

3 In the File Name box, leave the name as: Database1 and click Create.

4 Under Table Tools, on the Datasheet tab, in the Fields & Columns group, point to New Field to see the ScreenTip.

Note the ScreenTip is displayed when the mouse pointer is positioned on the button.

5 Click the Office Button, and then click Access Options.

6 In the Popular category, click the arrow for ScreenTip style and click Don’t show ScreenTips.

7 Click OK.

8 Under Table Tools, on the Datasheet tab in the Fields & Columns group, point to New Field again.

Note that when you point to a button the ScreenTip now no longer appears.

9 On the Quick Access Toolbar, click Customize Quick Access Toolbar.

For Evaluation Only

(19)

10 Click Minimize the Ribbon.

Note the Ribbon appears minimized.

11 On the Quick Access Toolbar, click Customize Quick Access Toolbar.

12 Click Show Below the Ribbon.

Note the Quick Access Toolbar now appears below the Ribbon.

13 Double-click the Datasheet tab and in the Fields & Columns group, right-click New Field, and then click Add to Quick Access Toolbar.

Double-clicking a tab resets the Ribbon to show again. If you single-click, you will see the commands for this tab, but as soon as you chose the option, it minimizes again.

Note New Field appears on the right-hand side of the Quick Access Toolbar.

14 On the Create tab, in the Tables group, right-click Table and click Add to Quick Access Toolbar.

Note New Field and Table appear on the right-hand side of the Quick Access Toolbar.

15 On the Quick Access Toolbar, click Customize Quick Access Toolbar and click More Commands.

16 Under Choose commands from, click Form, and then click Add.

17 Under Customize Quick Access Toolbar, click Redo, and then click Remove.

18 Under Customize Quick Access Toolbar, click Undo. Click Move Down until Undo is the last command in the Customize Quick Access Toolbar list.

For Evaluation Only

(20)

Note New Field, Table and Form have been added, Redo has been removed and Undo has been moved to the bottom of the Customize Quick Access Toolbar list.

19 Click the arrow in the box under Customize Quick Access Toolbar and click For Database1.

20 Click the arrow in the box under Choose commands from and click All Commands.

21 In the Choose commands from list, click Ascending, and then click Add.

22 In the Choose commands from list, click Descending, and then click Add. Click OK.

Note Ascending and Descending have been added to the Quick Access Toolbar for Database1.

23 On the Quick Access Toolbar, click Customize Quick Access Toolbar and click Show Above the Ribbon.

Note the Quick Access Toolbar now appears above the Ribbon.

24 Click the Office Button, and then click Access Options.

25 Click Popular and click the arrow for ScreenTip style, click Show feature descriptions in ScreenTips, and then click OK.

Note that when you position your mouse pointer on any button, the feature description appears in the ScreenTip.

26 Click Customize Quick Access Toolbar and click More Commands. Click Reset.

For Evaluation Only

(21)

27 Click Yes.

28 Click Customize Quick Access Toolbar and click For Database1.

29 Click Reset and click Yes. Click OK.

Note the Quick Access Toolbar is reset to its default setting.

30 Click the Office Button and click Close Database.

Practice the Skill

This exercise will review your knowledge of the screen.

1 Use your knowledge of the screen features to label the items listed below the following illustration.

a. Quick Access Toolbar c. Title bar e. Office Button g. Zoom Controls b. Datasheet tab d. ScreenTip f. Navigation Pane h. Navigation buttons

For Evaluation Only

(22)

Planning a Database

Before you begin the design process, you must know what kind of database you are going to be creating and you should have a clear idea of the purpose of the database. You must plan carefully and with full consideration of the database requirements.

Do not assume you can start creating tables and their relationships without planning the database on paper ahead of time. It is much better to thoroughly plan the database and make your mistakes ahead of time than to have to redo the database later on because the design is poor.

It is not very difficult to change the structure of a table once you have entered data into it, but it is better to avoid making any changes once you have entered data into a table. Therefore, before you create a database, the first step is to plan its structure. Even a simple one-table database with no other objects will still require some degree of planning, the more complex the database the more important the planning.

Planning Questions

• What is the purpose of the database? Why do I want a database? What kind of data do I have? What kind of data do I want to put in the database? What is the best way to organize it? (These questions help you define your tables).

• If I want to enter data into a table, how can I simplify the process to make it as easy as possible for someone to do data entry? What are the different ways I am going to effectively edit enter and add data to the database? (This question helps you define the forms you may need).

• Once I have entered the data into the database, what kinds of questions do I want to ask about the data?

(This question will help you define the queries and reports you will want to make based on your data).

Designing the Database

A well-designed database promotes consistent data entry and retrieval and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you want it. Follow these general steps to plan a database on paper first:

1. Determine the purpose/scope for your database, and the problem(s) you want to solve. Defining on paper what will and will not be included in the database is the first step to creating a successful database. For example, the purpose of the database might be, “to keep a list of my customers,” “to track my inventory,” “to administer my assets,” or “to handle reservations.”

For example, the purpose of a database might be to manage the lease of vehicles by divisions from various international suppliers:

Site Contact Phone Division Vehicle Supplier Country Continent North J Smith 345-9898 Commercials Mazda

Honda Mitsubishi Nissan

Vehicle On Time Vehicle On Time Transport Supplies Transport Supplies

USA USA Mexico Mexico

North America North America North America North America North J Smith 345-9898 Rentals Chrysler

Ford Nissan

Automotive Antics Automotive Antics Trans Dealers

Belgium Belgium USA

Europe Europe North America South M Yee 322-7761 Commercials

Rentals

Mazda Honda Nissan

Wheels Ltd Wheels Ltd Mobile Dealers

USA USA Italy

North America North America Europe South M Yee 322-7761 Commercials Jaguar Véhiculer Deals Luxembourg Europe

For Evaluation Only

(23)

If other people will use the database, be sure to speak with them about the data they will require. The planning would include consultation with user groups, investigation into current methods and systems, and studying what other firms are using. Be sure to talk to potential users, they will highlight database requirements you might have overlooked and they will make suggestions and have ideas on what constitutes a successful database to them.

2. Consider the information you will store in your database. Typically, information falls into broad categories.

Accurately identifying these categories is critical to designing an efficient database, because you will store different types and amounts of data in each category. For example, a database intended to track sales has categories such as “products,” “invoices,” and “customers.” A database intended to manage the purchase of vehicles by division has categories such as “vehicles,” “suppliers” and “divisions.”

3. Once you have determined the broad categories, consider how these categories are related. You can do this by writing simple sentences that describe how the categories interact, such as, “customers order products,”

“products are delivered to customers,” and “customers are invoiced for products.” In the case of managing the purchase of vehicles by division, “a company has divisions,” “divisions purchase vehicles,” and

“suppliers supply vehicles.” Each of these pairs suggests a relationship between the data in one category and the data in the other category.

4. Once you have identified your categories of information, you are ready to organize your database tables, because these categories of information are effectively your database tables. Tables group data containing a common topic or purpose. For example, you might use one table to store site details, while you use another table to store site details, such as site, contact, phone and another table to store division details, and another table to store vehicle details, and another table to store supplier details such as supplier, country, continent and so on.

Typically, databases are organized in one of three ways:

- A single table in a single file; use a single table if you want to track data in one category only, such as site names, contact and phone.

- Multiple tables in a single file; use multiple tables if your data is more complex, such as site, divisions, vehicles and suppliers.

- Multiple tables in multiple files; use multiple files if you want to share the same data among several different database solutions. For example, you can store your site information in a separate file if you plan to use that information in more than one solution.

- Use relationships to share data between tables in the same file or with tables in external files.

Site Table

Division Table

Vehicle Table

Supplier Table

Country Table

Continent Table

5. Determine the database tables and the data they will include, and in turn, which fields you will need. To make it easy to search and sort records, create separate fields for site into site name, contact and phone.

Separating your data into multiple fields at the time of data entry can make it easier to generate future reports.

For example, using separate fields to capture vehicle details such as make, model and year makes it easier to compile summary and sub-summary reports at the end of a week, month, or year.

For Evaluation Only

(24)

Site Table Site Name Contact Phone

Division Table Division Name Vehicle Table Make

Model Year

Supplier Table Supplier Name

Country Table Country Name Continent Table Continent Name

6. Determine the match fields for each table, and circle each one in your plan. For example, in the site table you might want to assign each site a unique, identifying number. You would not enter a site identification number into the table unless you had a new site to add, so the existence of a site number determines the existence of a record.

Site Table Site ID Site Name Contact Phone

Division Table Division ID Division Name Vehicle Table Vehicle ID Make Model Year

Supplier Table Supplier ID Supplier Name Contact Continent

For Evaluation Only

(25)

Country Table Country ID Country Name Continent Table Continent ID Continent Name

7. For each table, decide which fields will store data, and which ones will be used from other (related) tables.

Each table has only one subject, and all fields in a table describe only that subject. For example, the fields in one record of the site table together store all the information about one site.

Based on a table's subject, you can see where it makes sense to store the data and where to use data from a related table. Other than match fields, all fields should appear only once in your database. Cross out occurrences of fields that do not pertain to the table's subject.

Site Table Site ID Site Name Contact Phone

Division Table Division ID Division Name Vehicle Table Vehicle ID Make Model Year

Supplier Table Supplier ID Supplier Name Contact Continent Country Table Country ID Country Name Continent Table Continent ID Continent Name

8. Determine the relationships between the tables. In your plan, draw a line from each match field in a table to the corresponding match field in the related table. What establishes a relationship between tables is that their match fields contain matching data.

Relationships also make it possible to group your data to resolve complex questions. For example, relationships can be used to determine current vehicle levels, lease projections, and other tasks where it is necessary to view data across multiple tables.

For Evaluation Only

(26)

9. Determine whether you want to share your database with other users and how they will access the file.

10. Consider who will use the database and whether you want to restrict access to it. When you create the database, assign access privileges as required.

11. Decide what layouts you need, and plan a separate layout for each task. For example, create separate layouts for printing labels or envelopes.

12. Create your database.

Defining Table Fields

When you are defining the fields (columns) in each table, you are defining fields that must be manually entered by the data entry user.

Each field should be atomic; this means each should hold the smallest meaningful value. The most common disregard of this rule is storing a person's full name that is the first name and last name, in the same field. If you separate first name and last name, you will be able to sort people by last name when creating a phone directory or address a letter using a full name (Mrs. Mary Baker) and in the same letter add a greeting line (Dear Mary) by separating the first name.

Do not include fields to hold data that can be calculated from other fields. For example, if you had fields holding an employee's hourly pay rate and weekly hours, you would not include a gross pay field, if you have a field holding a product’s price and the quantity ordered, you would not include a total field. Calculations are not fields in a table; they are calculated controls that you can create in queries, reports and forms.

Learn the Skill

In this exercise, you will learn how to define fields in a database table.

1 List the fields that you need in a customer table to be able to phone, e-mail or send a facsimile to a customer on a frequent basis.

2 What fields are required in a products table, if you have to track a product’s wholesale price, its retail price (calculated as a 30% markup on the wholesale price), quantity in stock and quantity on order?

1.1

For Evaluation Only

(27)

Practice the Skill

In this exercise, you will learn more about how to define fields in a database table.

1 List the fields to include in a survey table, to be able to enter either a yes or no response to each survey question.

2 If the purpose of the database is to track the date, time and brief details of phone calls made to call centre staff from customers, what tables are required in the database.

3 What fields are required in each table to achieve the purpose of the database?

Defining Appropriate Data Types

When you first design and build a database, you plan one or more tables, you plan the fields (columns) for each table, and you set an appropriate data type for each field. For example, if you want to store dates and times, you set a field to the Date/Time data type, if you want to store names and addresses, you set one or more fields to the Text data type, and if you want to store monetary values, you can set a field to the Currency data type.

Data types provide a basic form of data validation because they help ensure that users enter the proper types of data in your table fields. For example, you cannot enter text in a field set to accept only numbers. A common disregard of data types is setting a phone number to a number field; most phone numbers include alpha characters such as hyphens or brackets for area codes for example (07)347-4419. The inclusions of alphanumeric characters make this data type a text field, not a number field.

Access provides ten data types, each with a specific purpose defining the type of data expected to be entered in each field (column). The following table lists the data types, explains the type of data stored and any limits.

Data Type Stores Limits

Text Alphanumeric data (text, numbers and so on)

Stores up to 255 alphanumeric characters

Memo Alphanumeric data (text, numbers and so on)

If you have to enter more than 255 characters, you should click the Memo field data type. A memo field will store up to 2 gigabytes of data. This is the size limit for an Access database, so be aware that the database will perform slowly. Memo fields also support rich-text formatting including bullets, numbering, bold, italics and underline.

Number Numeric data only Number will only accept a “real” number, that is a number without any alpha characters such as hyphen (-), or a leading zero; for example (07)346-8989 is not a number because of the alpha characters including brackets, hyphens and the leading zero.

Date/Time Dates and times only Access stores all dates and times.

Currency Monetary data Access stores data as numbers with precision to four decimal places to accommodate exchange rate entries. You should use this data type to store financial or monetary data, and when you do not want Access to round values.

AutoNumber Unique values created by Access when you create a new record

This data type is typically used for primary keys. This is a number that is generated automatically either incrementally or randomly by Access.

Yes/No Boolean Access uses -1 or True for all Yes values and 0 or False for all No values. This type of field is commonly referred to as a Boolean field where only Yes or No can be entered. By default, this field appears as a check box that can be selected or cleared by pressing the

.

1.1

For Evaluation Only

(28)

OLE Object Images, documents, graphs, and other objects

This type of data field is used to insert or create bitmap images of the original document or other objects, and then display the bitmap in the table fields and form or report controls in your database. This type of field is commonly used when a photograph or image is inserted into a field, as opposed to typing text or a number in a field.

Hyperlink Web addresses, files on an intranet, internet or email addresses

A hyperlink, stores up to 1 gigabyte of data. You can store links to Web sites, files or e-mail addresses. The data entered typically appears blue and is underlined.

Attachment Any supported type of file

When you create an attachment field, you can attach any supported files including images, spreadsheets, Outlook items, and word- processing documents to the records in your database in the same way that you attach files to e-mail messages. Attachment fields provide greater flexibility and manage storage space more efficiently than an OLE Object data type.

Lookup Lookup Wizard This option is actually not a data type. You use the Lookup Wizard to create lookup fields that have drop-down arrows, these fields generally link foreign key fields to other tables. If you create a lookup field, you are also able to select more than one value, as lookup fields let you store multiple values (this will be discussed later in this course book).

Learn the Skill

In this exercise, you will learn how to identify the appropriate field data types.

1 Identify the appropriate data type for each field:

a) Customer’s last name b) Customers e-mail address c) Employee’s salary

d) Employee’s job application file e) Supplier’s international phone number f) Supplier’s Web address

g) Vehicle registration number

h) If a vehicle can or cannot be used for towing i) Patient’s age in years

j) Patient date of admission

Practice the Skill

In this exercise, you will learn more about appropriate field data types.

1 Identify the appropriate data type for each field:

a) Automatically created driver unique identification number b) Driver’s photograph

c) Patient’s date of birth d) Patient’s file e) Product quantity

f) If a product is discontinued or not

g) Client’s nationality (assume they are able to have more than one) h) Client’s description of events (generally 2-3 paragraphs of text)

For Evaluation Only. Access Microsoft Business Certification Series. Microsoft Office. Level 1 Essential Skills Courseware #:

Microsoft

Office

Access 2007

Level 1 – Essential Skills Courseware #: 6266-1

For Evaluation Only

Working With the Data Files

For Evaluation Only

Lesson 3: Manipulating a Table

Lesson 4: Data Manipulations

Lesson 5: Sorting and Filtering

Lesson 6: Select Queries

For Evaluation Only

Lesson 7: Relating Multiple Tables

Lesson 8: Maintaining Databases

Appendices

For Evaluation Only

Table of Contents

About This Courseware

Lesson 1: Introducing Access

Lesson 2: Creating and Modifying Tables

For Evaluation Only

Course Description

Course Series

Course Length

Course Prerequisites

Classroom Setup

For Evaluation Only

Course Design





























For Evaluation Only

Course Objectives

Conventions and Graphics

Learn the Skill

Practice the Skill

For Evaluation Only

Lesson 1: Introducing Access

Lesson Objectives

Lesson 1 Buttons

For Evaluation Only

Overview

What is a Database?

What is Access?

For Evaluation Only

Tables

Forms

For Evaluation Only

Reports

Queries

Macros

Modules

For Evaluation Only

Looking at the Screen

For Evaluation Only

For Evaluation Only

Using Screen Tips

Using the Ribbon

For Evaluation Only

Keeping the Ribbon Minimized

Restore the Ribbon

Work with the Ribbon using the Keyboard

For Evaluation Only

Using the Quick Access Toolbar

Customizing the Quick Access Toolbar

For Evaluation Only

Moving the Quick Access Toolbar

Learn the Skill

Access ²⁰⁰⁷