Open Learning Access Intermediate
Open Learning Guide
Microsoft®Access 2010
Intermediate
Note: Microsoft, Access and Windows are registered trademarks of the Microsoft Corporation
Release OL343v1
Published by:
CiA Training Ltd
Business & Innovation Centre
Sunderland Enterprise Park
Sunderland
SR5 2TA
United Kingdom
Tel: +44 (0)191 549 5002
Fax: +44 (0)191 549 9005
E-mail:
Web: www.ciatraining.co.uk
ISBN: 978-1-86005-884-4
Important Note
This guide was written for Microsoft Office 2010 running on Windows 7. If using earlier versions of Windows some dialog boxes may look and function slightly differently to that described.
A screen resolution of 1024x768 is assumed. Working at a different resolution (or with an application window which is not maximised) may change the look of the dynamic Office 2010 Ribbon, which changes to fit the space available.
For example, the Editing Group on a full Ribbon will 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 INTERMEDIATE - The second guide in the Access series contains exercises covering the following topics:
· Replacing Text / · Calculated Fields· Relationships / · Lookup Fields
· Default Values / · Validation Rules
· Input Masks / · Subforms
· Linking Forms / · Combo Boxes
· List Boxes / · Option Groups
· Tab Control / · Form Properties
· Grouped Reports / · Label Wizard
· Chart Wizard / · Importing/Exporting Data
· Database Objects / · Advanced Queries
This Open Learning guide is suitable for:
· Any individual wishing to improve their knowledge of Microsoft 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 development and manipulation of an Access database.
After completing the guide the user will be able to:
· use advanced queries
· use Wizards
· create forms and subforms
· create command buttons and combo boxes
· produce advanced reports
Downloading the Data Files
The data files associated with this guide must be downloaded from our website. To do this, go to www.ciatraining.co.uk/data and follow the simple on-screen instructions.
Your FastCode for this guide’s data is: OL343
By default, the data files will be downloaded to the DATA FILES \ Open Learning \ Access 2010 Intermediate 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 Note For All Users
The accompanying downloaded data contains files, enabling the user to practise new techniques without the need for data entry.
This guide cannot be copied without the permission of CiA Training Ltd.
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 Advanced Editing 7
1 - Copy and Paste 8
2 - Cut and Paste 9
3 - The Clipboard 10
4 - Replacing Text 12
5 - Revision: Advanced Editing 14
Section 2 Advanced Queries 15
6 - Group By 16
7 - Count 17
8 - Sum 18
9 - Average Values 19
10 - Maximum and Minimum Values 20
11 - Calculated Fields in Queries 21
12 - Parameter Queries 23
13 - Revision: Advanced Queries 25
Section 3 Relationships 26
14 - Applying a Primary Key 27
15 - Applying Relationships 29
16 - Referential Integrity 32
17 - Updating and Deleting Records 33
18 - Viewing a Subdatasheet 35
19 - Querying Related Tables 36
20 - Revision: Relationships 37
Section 4 Table Field Properties 38
21 - Lookup Fields 39
22 - Format Field Property 41
23 - Default Values 43
24 - Validation Rules/Text 44
25 - Input Masks 46
26 - Other Field Properties 48
27 - AutoNumber 50
28 - Revision: Field Properties 51
Section 5 Advanced Forms 51
29 - Main/Subform: Form Wizard 51
30 - Main/Subform: Subform Wizard 51
31 - Main/Subform: Manual 51
32 - Linking Forms 51
33 - Multiple Table Forms 51
34 - Revision: Advanced Forms 51
Section 6 Form Controls 51
35 - Calculated Fields 51
36 - Command Button: Wizard 51
37 - Combo Box 1: Wizard 51
SECTION 6 CONTINUED
38 - Combo Box 2: Wizard 51
39 - Combo Box 3: Wizard 51
40 - Combo Box: Manual 51
41 - List Boxes 51
42 - Option Groups 51
43 - Tab Control 51
44 - Revision: Form Controls 51
Section 7 Form Properties 51
45 - Form Titles 51
46 - Form Views 51
47 - Form Data Properties 51
48 - Form Cycle Properties 51
49 - Shortcut Menu Properties 51
50 - Revision: Form Properties 51
Section 8 Advanced Reports 51
51 - Grouped Report: Wizard 51
52 - Grouped Report: Manual 51
53 - Label Wizard 51
54 - Chart Wizard 51
55 - Calculated Fields 51
56 - Revision: Advanced Reports 51
Section 9 Tools 51
57 - Importing Data 51
58 - Exporting Data 51
59 - Database Objects 51
60 - Revision: Tools 51
Section 10 Action Queries 51
61 - Append Query 51
62 - Delete Query 51
63 - Make-Table Query 51
64 - Update Query 51
65 - Revision: Action Queries 51
Section 11 Query Wizards 51
66 - Crosstab Query 51
67 - Find Duplicates Query 51
68 - Find Unmatched Query 51
69 - Revision: Query Wizards 51
Answers 51
Glossary 51
Index 51
Other Products from CiA Training 128
Section 1
Advanced Editing
By the end of this Section you should be able to:
Understand and use Cut, Copy and Paste
Understand and use the Clipboard
Find and Replace Text
Exercise 1 - Copy and Paste
Guidelines:
Selected data, entire fields, or entire records can be copied from one location and pasted to another. This helps when repeatedly typing the same data. Copy will copy selected data and place it in an area of Windows known as the Clipboard. Paste will take a copy of data from the Clipboard and paste it to a specified location, overwriting any existing content.
Actions:
1. Start Access and open the Pets database from the supplied data files. Open the Pet Details table in Datasheet View.
2. The text Rabbit in the first field should be highlighted, if not click and drag to highlight it. Click the Copy button , on the Clipboard group of the Home tab. This places the word Rabbit into the Clipboard.
Note: If the Clipboard task pane appears now, ignore it for the moment.
3. Click the New Record button from the Navigation buttons to place the cursor in the first field of a new record (record 7), then click Paste from the Home tab. Rabbit is pasted into the field.
4. Enter today’s date in the Date Sold field of the new record.
5. Click and drag over the price from the first record and copy it to into the new record. Enter the Number Sold as 5.
6. Change the first field of the record to read Black Rabbit.
7. Move the mouse pointer to the left edge of the Black Rabbit field until the cursor changes to . Click to select the entire field. Select Copy.
8. Click in the first field for the next new record (record 8) so that the cursor is flashing. The Paste option is not available. Because an entire field was copied, an entire field will need to be selected for Paste.
9. Select the entire blank field by moving the mouse pointer to the left edge and clicking when it becomes . The Paste option is available. Click it.
10. Move the mouse pointer to the left of the Fish record until the mouse pointer changes to . Click to select the entire record. Select Copy.
11. Click in the Black Rabbit field in record 8. Paste is unavailable. Because an entire record was copied, an entire record will need to be pasted.
12. Select the entire record 8 then click Paste. The copy of the Fish record overwrites the Black Rabbit record.
13. Leave the table open for the next exercise.
Exercise 2 - Cut and Paste
Guidelines:
Selected data, entire fields, or entire records can be moved from one location to another. This helps when data has been entered in the wrong place. Cut will remove selected data from its original location and place it in the Clipboard. Paste will take the data from the Clipboard and insert it in a specified location, overwriting any existing content.
Actions:
1. With the Pet Details table open from the previous exercise, click and drag to highlight the word Fish in record 8. Click the Cut button . Fish has now been removed from its original location and placed in the Clipboard.
2. Type Cricket in the blank field and change the Number Sold to 20.
3. Click the New Record button.
4. Click Paste. Fish has been placed into the first field of the new record.
5. Enter the Date Sold as 30/04/10, Price £0.80 and Number Sold as 3.
6. Press Tab to create another new record. In the new Animal field click Paste.
7. Note that because Fish is still the last item to be placed on the Clipboard, it is pasted again. Pasting does not remove cut or copied items from the Clipboard.
8. Enter any details to complete the record.
9. Leave the table open for the next exercise.
Exercise 3 - The Clipboard
Guidelines:
When Cut or Copy has been used, the objects are placed in a temporary storage area known as the Clipboard. Up to 24 items can be held on the Clipboard, and it can be viewed and manipulated in a Task Pane.
The Clipboard is shared between all Office applications and can contain anything cut or copied from any applications, i.e. text, cells, slides, images, etc.
Actions:
1. The Pet Details table should be open. If the Clipboard is not displayed, select the Clipboard launcher from the Ribbon, . The Clipboard Task Pane will appear on the left of the screen. The last item cut or copied will be at the top of the pane.
2. Items cut and copied in the previous exercises may be displayed, and because the Clipboard is shared between all Office applications, there may already be some other items on it. Click the Clear All button, , to delete them.
3. Select the entire Dog record (record 5) and click Copy.
4. Select the Date field from the Toad record using the mouse pointer and click Copy.
5. View the Clipboard to see that the two copied items have appeared.
6. Notice that Clipboard entries copied as entire fields or records contain not only content but also the relevant field names.
continued over
Exercise 3 - Continued
7. Move the mouse over one of the entries to display a down arrow on the right. Click the arrow to see the options for this entry. It can be either Pasted or Deleted from the Clipboard. Click away from the Clipboard to hide the options.
Note: Clicking on the entry (not the arrow) will also Paste the item into the table.
8. Select the new record at the bottom of the table using the mouse pointer.
9. Click the Clipboard entry that shows the Dog record that was copied earlier. The record will now be pasted into the new record.