Open Learning GuideAccess 2007 Intermediate

Open Learning Guide

Microsoft®

Access 2007

Intermediate

Note: Microsoft is a registered trademark and Access and Windows are trademarks of the Microsoft Corporation in the United States and other countries.

Release OL319v1

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-538-6

Important Note

This guide was written using Windows Vista with a screen resolution of 1024 x 768.

Using Windows XP will result in many dialog boxes looking different, although the content is the same.

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'sOpen Learningguides are a collection of structured exercises building into acomplete 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 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 Intermediate 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.

Important Note For All Users

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

Note: 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.

Section 10 Advanced Editing......

68 - Copy and Paste......

69 - Cut and Paste......

70 - The Clipboard......

71 - Replacing Text......

72 - Revision: Advanced Editing......

Section 11 Advanced Queries......

73 - Sum......

74 - Count......

75 - Group By......

76 - Average Values......

77 - Maximum and Minimum Values......

78 - Calculated Fields in Queries......

79 - Parameter Queries......

80 - Revision: Advanced Queries......

Section 12 Relationships......

81 - Applying a Primary Key......

82 - Applying Relationships......

83 - Referential Integrity......

84 - Updating and Deleting Records......

85 - Viewing a Subdatasheet......

86 - Querying Related Tables......

87 - Revision: Relationships......

Section 13 Table Field Properties......

88 - Lookup Fields......

89 - Format Field Property......

90 - Default Values......

91 - Validation Rules/Text......

92 - Input Masks......

93 - Other Field Properties......

94 - AutoNumber......

95 - Revision: Field Properties......

Section 14 Advanced Forms......

96 - Main/Subform: Form Wizard......

97 - Main/Subform: Subform Wizard......

98 - Main/Subform: Manual......

99 - Linking Forms......

100 - Multiple Table Forms......

101 - Revision: Advanced Forms......

Section 15 Form Controls......

102 - Calculated Fields......

103 - Command Button: Wizard......

SECTION 15...... CONTINUED

104 - Combo Box 1: Wizard......

105 - Combo Box 2: Wizard......

106 - Combo Box 3: Wizard......

107 - Combo Box: Manual......

108 - List Boxes......

109 - Option Groups......

110 - Tab Control......

111 - Revision: Form Controls......

Section 16 Form Properties......

112 - Form Titles......

113 - Form Views......

114 - Form Data Properties......

115 - Form Cycle Properties......

116 - Shortcut Menu Properties......

117 - Revision: Form Properties......

Section 17 Advanced Reports......

118 - Grouped Report: Wizard......

119 - Grouped Report: Manual......

120 - Label Wizard......

121 - Chart Wizard......

122 - Calculated Fields......

123 - Revision: Advanced Reports......

Section 18 Tools......

124 - Importing Data......

125 - Exporting Data......

126 - Database Objects......

127 - Revision: Tools......

Section 19 Action Queries......

128 - Append Query......

129 - Delete Query......

130 - Make-Table Query......

131 - Update Query......

132 - Revision: Action Queries......

Section 20 Query Wizards......

133 - Crosstab Query......

134 - Find Duplicates Query......

135 - Find Unmatched Query......

136 - Revision: Query Wizards......

Answers......

Glossary......

Index......

Other Products from CiA Training......

Section 10
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 68 - 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.Open the Pets database from the supplied data files and open the PetDetails table in DatasheetView.

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 NumberSold 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 8then click Paste. The copy of the Fish record overwrites the Black Rabbit record.

13.Leave the table open for the next exercise.

Exercise 69 - 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 record8. 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 NumberSold to 20.

3.Click the NewRecord button.

4.Click Paste. Fish has been placed into the first field of the new record.

5.Enter the Date Sold as 30/08/03, 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 70 - 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.With the Pet Details table open, look at the Clipboard Task Pane on the left of the screen. If the Clipboard is not displayed, select the Clipboard launcher from the Ribbon,

Note:The current content of the Clipboard may vary from this picture.

2.Items cut and copied in the previous exercises should 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 Datefield 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.

Exercise 70 - 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.

10.Use to select the entire DateSold field of the new Dog record.

11.Click the button on the Clipboard that shows the DateSold field that was copied earlier. The Date Sold field from the Clipboard will Paste over the DateSold field in the new Dog record.

Note:Filling the Clipboard with several items, possibly from different Office applications, before inserting them is known as Collect and Paste.

12.Click the Options button at the bottom of the Task Pane.

13.If the Show Office Clipboard Automatically option does not have a check mark, click it once to display one. When this option is selected, the Clipboard Task Pane will appear automatically when more than one item is added to it (Cut or Copy).

Note:The ClipboardTask Pane takes up space in the Access window and the Automatic option is often not selected, in which case, the Clipboard will always have to be manually displayed when needed. Uncheck the Show Office Clipboard Automatically option now if required.

14.Close the Clipboard and leave the table open for the next exercise.

Note:When using the Paste function from Ribbonbutton, (without using the Clipboard) it is always the last item copied or cut which is inserted.

Exercise 71 - Replacing Text

Guidelines:

Replace will search a table for a particular piece of text, and replace it with different text. The search can be restricted to one field or the whole table.

Replacements can be made one at a time as the table is searched or a global replacement can be made of all occurrences of the text.

Actions:

1.With the Pet Details table open click in the Animal field of the first record.

2.From the Home tabclick the Replace button, .The Find and Replace dialog box appears with the Replacefunction selected.

Note:Alternatively click the Find button then select the Replace tab in the dialog box.

3.In the Find What box enter Fish and in the Replace With box enter Goldfish.

4.Click the arrow in the Look In box to see the options. The search can either be in the selected field (Animal), or the whole table. Make sure that the setting is Animal.

5.Click the arrow in the Match box to see the options. The search can be made to look for the text anywhere in a field, at the start of a field, or only if it is the whole field. Make sure that the setting is Whole Field.

6.Click Find Next to find the first occurrence of Fish after the cursor position.

Exercise 71 - Continued

7.Click Replace to replace this occurrence with Goldfish and automatically find the next occurrence.

8.Continue clicking Replace to substitute all occurrences of Fish with Goldfish.

9.When Access has replaced all occurrences a dialog box appears notifying you that no further occurrences of the search text can be found. Click OK to remove the dialog box then Cancel to remove the Find and Replace dialog box.

10.Look through the Animal field. All occurrences of Fish have been replaced with Goldfish.

11.Position the cursor at the top of the Animal field. Click the Replacebutton from the Find group on the Ribbon.

12.Enter Goldfish in Find What and Golden Orfe in Replace With.

13.Click the Replace All button to replace all occurrences immediately. A confirmation prompt is displayed.

14.Select Yes to confirm the changes.

15.Click Cancel to remove the Find and Replace dialog box.

16.Look through the table to view the changes.

17.Close the Pet Details table. If the only changes to the table have been data changes there will be no prompt to save the table at this time, as data changes are saved automatically. Only if there have been potential changes to design features will there be a prompt to save. Click No to any save prompt that may appear.

18.Close the Pets database.

Exercise 72 - Revision: Advanced Editing

1.Open the Pets database and the PetDetails table.

2.View the Clipboard Task Pane and make sure it is cleared.

3.Copy the first 3 records, one entire record at a time.

4.Paste each of three records back as new records.

Note:This is only possible because the records do not contain a unique key field.

5.Delete the DateSold field from each of these pasted records in turn.

6.Enter today’s date in the first empty DateSold field then Copy and Paste to the other blank fields.

7.Find and replace all occurrences of Rabbit with WhiteRabbit.

8.Clear all Clipboard entries and close the Clipboard Task Pane.

9.Close the Pet Details table and Pets database. There should be no need to save any changes.

Section 11
Advanced Queries

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

Create Sum, Count, Average, Min, Max and Group By Queries

Create Calculated Fields

Use Parameter Queries

Exercise 73 - Group By

Guidelines:

Instead of the normal Select Query which list individual records, it is possible to create aquery in Summary mode. These queries do not list individual records, they only show summary results such as counts, totals and averages. These values can be produced for all selected records or for defined groups of records.

The defining of record groups is itself a summary function called Group By.

Actions:

1.Open the Premises database. Create a new query using the QueryDesign button on the Create tab. Add the Commercial table to the query and place the Location field on to the query grid.