Open LearningAccess 2007 Introductory

Open Learning Guide

Microsoft®

Access 2007

Introductory

Note: Microsoft, Access and Windows are registered trademarks of the Microsoft Corporation

Release OL318v1

Published by:

CiA Training Ltd

Business & Innovation Centre

SunderlandEnterprisePark

Sunderland SR5 2TH

United Kingdom

Tel: +44 (0)191 549 5002

Fax: +44 (0)191 549 9005

E-mail:

Web:

ISBN 13: 978-1-86005-537-9

Important Note

This guide was written using Windows Vista. If using Windows XP some dialog boxes will look different, although the content is the same.

A screen resolution of 1024 x 768 was used. Working in a different screen resolution, or with an application window which is not maximised, will change the look of the Office 2007 Ribbon.

The ribbon appearance is dynamic, it changes to fit the space available. The full ribbon may show a group containing several options, but if space is restricted it may show a single button that you need to click to see the same options, e.g. the Editing group may be replaced by the Editing button.

First published 2007

Copyright © 2007 CiA Training Ltd

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written permission of CiA Training Limited.

CiA Training's Open Learning guides are a collection of structured exercises building into a complete open learning package, to teach how to use a particular software application. They are designed to take the user through the features to enhance, fulfil and instil confidence in the product.

ACCESS INTRODUCTORY - The first guide in the Access series contains exercises covering the following topics:

  • Database Principles
/
  • Printing

  • Screen Layout
/
  • Editing Data

  • Using Help
/
  • Table Formatting

  • Moving Within a Table
/
  • Sorting

  • Using Filter
/
  • Using Forms

  • Creating a Database
/
  • Querying and Searching

  • Adding Data
/
  • Reports

This Open Learningguide is suitable for:

  • Any individual wishing to learn Microsoft Access without any prior knowledge of Access. The user works through the guide from start to finish.
  • Tutor led groups as reinforcement material. Can be used as and when necessary.

Aims and Objectives

To provide the necessary knowledge and techniques for the successful creation and manipulation of an Access 2007 database.

After completing the guide the user will be able to:

  • create and maintain a functioning database
  • create tables, data entry forms and professional reports
  • query a database
  • print out from the database.

Downloading the Data Files

The data associated with these exercises must be downloaded from our website. Go to: . Follow the on screen instructions to download the appropriate data files.

By default, the data files will be downloaded to Documents\CIADATA FILES\Open Learning\Access 2007 Introductory Data.

If you prefer, the data can be supplied on CD at an additional cost. Contact the Sales team at .

Introduction

This guide assumes that the program has been correctly and fully installed on your personal computer, that the computer is already switched on, and that a printer and mouse are attached. The guide was created using Access 2007.

Accompanying text for the exercises is contained on disk. This avoids unnecessary typing and speeds up the learning process.

Notation Used Throughout This Guide

  • Key presses are included within > e.g. <Enter>
  • The guide is split into individual exercises. Each exercise consists of a written explanation of the feature, followed by a stepped exercise. Read the Guidelines and then follow the Actions with reference to the Guidelines, if necessary.

Recommendations

  • It is suggested that the user add their name, the date and exercise number after completing each exercise that requires a printed copy.
  • Read the whole of each exercise before starting to work through it. This ensures understanding of the topic and prevents any unnecessary mistakes.
  • Measurements used throughout this guide are metric.
  • Some fonts used in this guide may not be available on all computers. If this is the case, select an alternative.

Section 1 Fundamentals......

1 - Starting Access......

2 - The Opening Screen......

3 - Opening an Existing Database......

4 - The Access Screen......

5 - The Ribbon......

6 - Help......

7 - Closing Access......

8 - Revision: Fundamentals......

Section 2 Databases......

9 - Database Principles......

10 - The Navigation Pane......

11 - Database Object Views......

12 - Tabbed Documents......

13 - Saving & Closing Databases......

14 - Revision: Databases......

Section 3 Tables......

15 - Tables......

16 - Using the Mouse within a Table......

17 - Using the Keyboard within a Table......

18 - Changing the Column Width......

19 - Changing the Row Height......

20 - Freezing Columns......

21 - Moving Columns......

22 - Hiding Columns......

23 - Changing Table Appearance......

24 - Previewing and Printing a Table......

25 - Revision: Tables......

Section 4 Sorting and Filtering......

26 - Sorting Data in Tables......

27 - Using Filter By Selection in Tables......

28 - Filter Options......

29 - Using Filter By Form in Tables......

30 - Using Advanced Filter/Sort......

31 - Quick Filters......

32 - Revision: Filters......

Section 5 Finding & Editing Records in a Table......

33 - Finding Text in Tables......

34 - Editing Records in a Table......

35 - Correcting Errors with Undo......

36 - Adding a New Record......

37 - Deleting a Record......

38 - Using Editing Shortcuts......

39 - Revision: Finding and Editing Records......

Section 6 Create a New Database and Table......

40 - Creating a New Database......

41 - Creating a Table in Design View......

42 - Using Field Properties......

43 - Entering Records in Datasheet View......

44 - Revision: Creating Database Tables......

Section 7 Queries......

45 - Creating a Query with a Wizard......

46 - Creating a Query in Design View......

47 - Using a Range of Values in a Query......

48 - Using a Pattern Search in a Query......

49 - Using a Non-Match in a Query......

50 - Using Current Date in Queries......

51 - Using AND in Queries......

52 - Using OR in Queries......

53 - Revision: Queries......

Section 8 Forms......

54 - Forms......

55 - Creating a Quick Form......

56 - Creating a Form with the Form Wizard......

57 - Creating a Form in Design View......

58 - Formatting a Form......

59 - Using a Form to Add Records......

60 - Using a Form to Delete Records......

61 - Revision: Forms......

Section 9 Reports......

62 - Reports......

63 - Creating a Quick Report......

64 - Using the Report Wizard......

65 - Creating a Report in Design View......

66 - Printing a Report......

67 - Revision: Reports......

Answers......

Glossary......

Index......

Other Products from CiA Training......

© CiA Training Ltd 20071

Open LearningAccess 2007 Introductory

Section 1
Fundamentals

By the end of this Section you should be able to:

Start Microsoft Access

Understand the Access Screen

Open an Existing Database

Understand the Ribbon and Quick Access Toolbar

Use Help

Exit Microsoft Access

Exercise 1 - Starting Access

Guidelines:

There are numerous ways to start the program. The following method is recommended for beginners.

Actions:

  1. Starting your computer will automatically show the WindowsDesktop with the Taskbar along the bottom. Click once on the Start button, , at the left of the Taskbar to show the Start menu.
  2. Move the mouse pointer over All Programs to display a list of available programs, some of which may be included in Folders.
  3. Click theMicrosoft Officefolder to display its contents.
  1. Click Microsoft Office Access 2007 to start the application.

Note:Depending on previous use, an icon for Access, , may be found on the initial Start menu or even on the Desktop.

Exercise 2 - The Opening Screen

Guidelines:

Unlike many other Office applications,Access does not start immediately with a blank screen. There is a preliminary screen from which the user can select the opening options. At this level the choices are whether to open an existing database or start creating a new one.

Actions:

  1. Look at the opening Access screen as shown below.There is a list of recently opened databases on the right which can be opened with a click and an icon to create a new blank database just below the screen title.

  1. The top line of the Access screen is the Title Bar. When a database is open, its name will be displayed here.
  2. At the right of the Title Bar are the usual Window buttons, , Minimize, Restore Down and Close.
  3. At the left of the Title Bar is the Office Button, . Click this button to show a list of basic database functions such as Opening, Closing, Saving and Printing.
  4. For now click in a blank area of the main screen to return.
  5. Leave the screen displayed for the next exercise.

Exercise 3 - Opening an Existing Database

Guidelines:

Once a database has been created, it may be opened at any time, if the storage location is known. When a database is opened, each individual object may be accessed.

Actions:

  1. Click the Office Button in the top left corner of the Access screen and click Open, . The Open dialog box is displayed.
  1. The default location for the supplied data files is within Documents. Double click Documents in the left pane to open it. Then double click the CIADATA FILES folder, then the Open Learning folder, then the Access 2007 Introductory Data folder. A list of supplied databases is displayed.

  1. Click on the file name Staff and then click on Open. The Staff database is now displayed in the Access Window.
  2. Leave the database open for the next exercise.

Exercise 4 - The Access Screen

Guidelines:

The main Access screen is where all objects in a database are created and edited. It has the following general layout.

Actions:

  1. Look at the top line of the window, the Title Bar. This contains the application title, i.e. Microsoft Access, and the name and type of the current database. In this example the Staff database is open and it has Access 2007 format.

Note:Databases with other (older) formats can be opened in Access 2007 but some processes may not work on them.

  1. At the left of the Title Bar is the Office Button, which has been used already. Click this button to display the list of options.
  2. At the bottom of the list is an button. Click this to display the Access Options dialog box. Settings can be changed here to control how Access will operate. For now click the Cancel button in the lower right corner to close the box and return to the main Access screen.

Exercise 4 - Continued

  1. Next to the OfficeButton is the Quick AccessToolbar, , which contains a few popular command buttons such as Save and Undo. This toolbar can be customised by adding further buttons.
  2. At the right of the Title bar are the usual Window control buttons. If the window is maximised these are, , Minimize, Restore Down and Close.

Note:If the window is not maximised, Restore Down is replaced by Maximize.

  1. Click Minimize, , to remove the Access window from the desktop. Click the Microsoft Access button on the Taskbar to restore it.
  2. Look at the area below the Title Bar. This is the Ribbon, which contains the commands needed to work with Access. It replaces the menus and toolbars of earlier Access releases and is described in the next exercise.

Note:Depending on the configuration of your computer there may be a SecurityWarning message below the Ribbon. It is not necessary to take any action about this. Ignore it unless specifically instructed by the guide.

  1. At the bottom of the window, above the Taskbar, is the Status Bar. This displays information relating to the current task. At present it should display Readyon the left.
  2. Down the left side of the screen is an area called the Navigation Pane which can list all of the objects included in the current database. This is described in a later exercise.
  3. At present it should show the names of the two tables in the Staff database. Double click on Personnel List to open this table and show its contents in the Work Pane.
  4. Leave this window open for the next exercise.

Exercise 5 - The Ribbon

Guidelines:

In previous versions of Microsoft Office applications, commands were controlled by a series of menus and toolbars. Access 2007 has replaced these with a Ribbon which is displayed at the top of the application window. The Ribbon contains buttons and drop down lists to control the operation of Access. The Ribbonhas a series of different views called Tabs, each one of which has a set of controls specific to a certain function or process. On each tab, the controls are further divided into separate Groups of connected functions.

Some tabs can be selected manually, some only appear when certain operations are active, for example open a Report in Design view and three Report Design tabs will appear on the Ribbon.

Actions:

  1. The Staff database should be open from the previous exercise with the Personnel List table open. Many of the Ribbon functions will not be available unless there is an object open.
  1. On the Ribbon, the Home tab should be selected. Three other basic tabs, Create, External Data and Database Tools are available. One extra tab, Datasheet, is available because at present a Table is open in Datasheet view.

Note:Any buttons displayed in pale grey are called ghosted and are not available to be selected at present.

  1. Notice how the buttons on the Ribbon are divided into Groups (Views, Clipboard, Font, etc.).

Note:The display of buttons on the Ribbon is dynamic and changes according to how much space is available. If the window is not maximised or the screen resolution is anything other than 1024 by 768, the Ribbon will not always appear as shown in this guide.

Exercise 5 - Continued

  1. Leave the cursor over any of the buttons. A ToolTip appears which gives more information and an alternative key press for the function if available.
  2. Buttons with a drop down arrow lead to further options. Click the Go To button, which is in the Find group. A list of options is displayed.
  3. Click the option. The last record in the table is highlighted.
  4. Some buttons will display a dialog box which needs data to be entered. Click the Find button, which is in the Find group. The Find and Replace dialog box is displayed. Click the Cancel button in the dialog box to remove it.
  5. To create a new object in the database, click the Create tab on the Ribbon. A new set of buttons is displayed, all concerned with creating new objects. Notice there are groups of commands relating to the creation of new Tables, Forms, Reports and Queries.
  6. Select the Home tab again.
  7. Click the drop down arrow on the View button at the left of the Ribbon. A list of the views available for displaying Tables is displayed.
  8. Different views are used for different purposes. Select Design View to see a different view of the table. Notice on the Ribbon that the Tab has changed because now the design of the table is being amended.
  9. Select the Home tab again and leave the database open for the next exercise.

Exercise 6 - Help

Guidelines:

Access has a comprehensive Help facility. This means that full advantage can be taken of the features incorporated in the program. Using Help can usually solve the majority of problems encountered.

Help is available either from Microsoft Office Online via the Internet, or from the content installed on your computer (Offline). The method of using Help is the same in either case but the content may vary slightly and there will be extra options when online. This guide assumes the Online option is selected.

Actions:

  1. Click the Help button, , in the upper right corner of the Access window to display the Access Help window. If there is no online connection, click the prompt to Show me offline help.

Note:Pressing the <F1> key will display the same Help window. The window can be moved, resized or maximised if required.

Exercise 6 - Continued

  1. Help can be used in two ways. Either type keywords into the Search box or browse through the listed topics.
  2. Type Printer into the Search box and click, .

Note:There may be many topics found by your search and it may be necessary to read through many pages to locate the most appropriate.

  1. Click the topic Set the default printer. Help text for this topic is displayed.
  2. Read the text then click the Back button, , on the dialog box toolbar to return to the previous screen.
  3. Type “delete a column” into the Search box and click, . The quotation marks force the whole phrase to be used and may help to narrow down the search.
  4. Click the first topic in the list. Help text for this topic is displayed.
  5. Read the text then click the Home button, , on the dialog box toolbar to return directly to the main help screen.
  6. Type xyz into the Search box and click, . Because the text is not found, some suggestions are made to help you find the required information. There are more options when connected online.
  7. Click the Home button, , to return directly to the main help screen.
  8. On the main screen the help topics are grouped into two columns of categories. Click on the Tables category. A list of topics under this heading is displayed. There are also two further subcategories which could be expanded.
  9. Click the topic Create a table (or Create tables in a database). A large article on the subject is opened. There are many sub headings within the article and links to them are collected near the start of the underIn this article. Read some of the article. There may be links to related articles.
  10. Click the Home button, .
  11. The button at the bottom right of the window indicates whether you are Connected to Office Online or not (Offline). Click on the button to see the alternative options, if available. Click in the Help window to remove the options display.
  12. Close the Help window.

Exercise 7 - Closing Access