Open LearningAccess Introductory

Open Learning Guide

Microsoft® Access 2010

Introductory

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

Release OL342v1

Published by:

CiA Training Ltd

Business & Innovation Centre

SunderlandEnterprisePark

Sunderland

SR5 2TA

United Kingdom

Tel: +44 (0)191 549 5002

Fax: +44 (0)191 549 9005

E-mail:

Web:

ISBN:978-1-86005-883-7

Important Note

This guide was written for Microsoft Office 2010 running onWindows 7. If using earlier versions of Windows some dialog boxes maylook and function slightly differently to that described.

A screen resolution of 1024x768is assumed. Working at a different resolution(or with an application window which is not maximised)may change the look of the dynamic Office 2010Ribbon, which changes to fit the space available.

For example, theEditing Group on a full Ribbonwill contain several buttons, but if space is restricted it may be replaced by an Editing Button (which, when clicked, will display the full Editing Group).

First published 2010

Copyright © 2010 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.

Microsoft is a registered trademark and Windows is a trademark of the Microsoft Corporation. Screen images reproduced by permission of the Microsoft Corporation. All other trademarks in this book are acknowledged as the property of their respective owners.

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 Learning guide 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 2010 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 files associated with this guide must be downloaded from our website. To do this, go to and follow the simple on-screen instructions.

Your FastCode for this guide’s data is: OL342

By default, the data files will be downloaded to the DATA FILES \ Open Learning \ Access 2010 Introductory folder in the Documents library/folder (or My Documents in Windows XP).

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 2010.

Important Notes For All Users

The accompanying downloaded data contains files, enabling the user to practise new techniques without the need for data entry.

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.

Contents

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 20101Access 2010

Open LearningAccess 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. Click onAll 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 Access 2010 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 are some basic options on the left, a selection of AvailableTemplates in the centre and a CreateBlank databasepanel on the right. From this screen, existing databases can be opened or new databases created. Within the options on the left there is a list of recently opened databases which can be opened with a click.

  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. Below the Title Bar is a series of Tabs. The File tab, , is selected by default and shows a list of basic database functions on the left, such as Open, Close Database, Save and Print (not all will be available yet).
  4. 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. A database consists of tables of data together with various other related items used for viewing and working with the data. Each table, form, report, query, etc., in a database is called an object.When a database is opened, each individual object may be accessed.

Actions:

  1. The File tab, , should be already selected, if not, click it now. Click Open, from the list of options to display the Open dialog box.
  1. The default location for files is the Documents library. If the data files have been installed correctly there will be a DATA FILES folder in the View pane on the right. Double click DATA FILES, then the Open Learning folder, then the Access 2010 Introductory folder. A list of supplied databases in the folder is now visible.

  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. The basic layout of the screen when a database is open is always the same. The content will vary depending on the objects that are open and the task that is being performed

Actions:

  1. Look at the Access screen for the Staff database. The main structure is a Ribbon area across the top containing commands, the Navigation Pane at the left listing database objects, and an Object Area where the selected objects are created, displayed or edited.

  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. This is the default format for new databases created in Access 2010.

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

continued over

Exercise 4 - Continued

Note:Depending on the set-up of your computer there may be a SecurityWarning message below the Ribbon. Ignore it in this guide unless specifically instructed.Close it if you wish by clicking on the right.

  1. At the left of the Title bar is the Quick AccessToolbar, , which contains a few popular command buttons such as Save and Undo. Some buttons will not yet be available. The 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.
  3. The leftmost tab on the Ribbon is the File tab, which has been used already. Click this tab to display the list of options.
  4. Near 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 with the Home tab displayed.
  5. 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.
  6. 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. At present it should show the names of the two tables, Course List and Personnel List, in the Staff database.
  7. Leave this window open for the next exercise.

Exercise 5 - The Ribbon

Guidelines:

The operation of Access 2010 is controlled by buttons and drop down lists contained in a Ribbon area just below the Title Bar. The Ribbon contains buttons and drop down lists to control the operation of Access. It is divided into a series of 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 only appear when certain operations are active, for example open a Report in Design view and three new Report Design tabs will appear.

This Ribbon replaces the menus and toolbars used in some earlier versions of Microsoft Office.

Actions:

  1. The Staff database should be open from the previous exercise. On the Ribbon, the Home tab should be selected. Three other basic tabs, Create, External Data and Database Tools are available. Notice that many of the Ribbon functions will not be available until an object is open.
  1. Double click on Personnel List in the Navigation Pane to open this table and show its contents in the Object Area. Two extra Table Tools tabs are available because 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, Sort & Filter, 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.

continued over

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. Some buttons produce immediate effects. Click the New button, ,which is found in the Records group. The cursor moves to a blank new record at the end of the table.
  3. 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.
  4. Click the option. The last record in the table is highlighted.
  5. 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.
  6. 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, andReports.
  7. Select the Home tab again.
  8. 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.
  9. 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.
  10. 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.
  2. If the Table of Contents panel is not displayed on the left, as shown below, click the Show Table of Contents button, , on the Help toolbar.

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