Microsoft Access 2016

135 Pages·2016·19.83 MB·English

by  Andrew Krupa

Microsoft Access 2016
Level 2
INFOCUS COURSEWARE
Designed to fast-track you through the process of learning
about computers and information technology, the In Focus
range is a unique and innovative concept in learning.
A quick reference summary of key procedures is provided at
the bottom of each page together with handy tips and
additional information.
Each title in the In Focus series can be used as:
 a classroom workbook for instructor-led teaching and
training;
 a self-study guide for self-paced learning;
 a tutorial guide for distance education programs;
 a resource collection of just-in-time support and
information for help desk users and support staff;
 a handy, desk-side reference for computer users.

This publication has been created using EngineRoom
Desktop™ document management and publishing software
developed by Watsonia Publishing.

MICROSOFT ACCESS 2016
LEVEL 2

Product Code: INF1631
ISBN: 978-1-925349-35-1
© 2016 Watsonia Publishing
MICROSOFT ACCESS 2016
LEVEL 2
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Copyright  2016 by Watsonia Software Pty Ltd (ABN 64 060 335 748)
Published by Watsonia Publishing

47 Greenaway Street
Bulleen VIC 3105
Australia
Phone: [phone]
Fax: [phone]
Web: www.watsoniapublishing.com
Email: [email protected]
Product Code: INF1631
ISBN: 978-1-925349-35-1
Build: 14/10/16

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Production Acknowledgments
Microsoft Access 2016 - Level 2 is produced with the assistance, hard work, advice
and recommendations of a number of people including Cara Hemphill (authoring),
and Alison Koster. We thank you all.
Trademark Acknowledgments
All terms mentioned in this manual that are known to be trademarks or service marks
have been appropriately acknowledged or capitalised. Watsonia Software cannot
attest to the accuracy of this information. Use of a term in this manual should not be
regarded as affecting the validity of any trademark or service mark.
Screen Shots © 1983-2016 Microsoft. All rights reserved.
Disclaimer
Every effort has been made to provide accurate and complete information. However,
Watsonia Software assumes no responsibility for any direct, indirect, incidental, or
consequential damages arising from the use of information in this document. Data
and case study examples are intended to be fictional. Any resemblance to real
persons or companies is coincidental.
Copyright Notice
This publication is protected in accordance with the provisions of the Copyright Act.
Apart from permissions expressed in the Copyright Act pertaining to copying for
study, review, or research, no part of this publication may be reproduced in any form,
or stored in a database or retrieval system, or transmitted or distributed in any form
by any means, electronic, mechanical photocopying, recording, or otherwise without
written permission from Watsonia Software Pty Ltd.
Microsoft Access 2016 - Level 2
R M F
EAD E IRST
In case you're not familiar with the terminology, This section contains some important information to
Read Me First is quite often the name given to a help you use this book so we thought we'd start
computer file that contains important information with a Read Me First section.
for people to know prior to using an application.

What skills and knowledge The aim of this course is to build on the introductory skills of Level 1. The
you will acquire... student learns how to ensure that data is entered correctly, how to create
more sophisticated and complex queries, and is introduced to some basic

automation techniques involving macros.

What you'll need to know Microsoft Access 2016 - Level 2 assumes some knowledge of the

before beginning this software to create basic databases and generate reports. It would be
course... beneficial to have a general understanding of personal computers and the
Windows operating system environment.

The objectives of this guide… At the completion of this course you should be able to:

 use various data validation features in Access to protect data
 format the data in a table
 perform more advanced queries using a variety of querying
techniques
1
 create and use parameter queries
 create calculated queries
 modify and adapt an existing form according to specific needs
 create a navigation form for a database in Access 2016
 create forms using a combination of wizards and manual
techniques 4
 create and use macros in Access
 work with a number of macro techniques
 create and work with macros in forms
 create and manage custom categories and groups in the
Navigation Pane
What you get in a chapter... Each chapter begins with a summary page listing the topics covered in
that chapter. The chapter then consists of single-page topic sheets
pertaining to the theme of the chapter.
What you'll need to have Many of the topics in this learning guide require you to open an existing
before commencing this file with data in it. These files can be downloaded free of charge from our
course... website at www.watsoniapublishing.com. Simply follow the student files
link on the home page. You will need the product code for this course
which is INF1631.
As you work through this It is strongly recommended that you close all open files, if any, prior to
guide… commencing each new chapter in this learning guide. Each chapter,
where relevant, has its own set of exercise files and any from a previous
chapter are no longer required.
Where to from here... Have a look at the next page which explains how a topic page works,
ensure that you have access to the exercise files (see above), and you're
ready to make a start.

© Watsonia Publishing - i - Preface
Microsoft Access 2016 - Level 2
W W T S
ORKING ITH OPIC HEETS

The majority of this book comprises single-page additional reference (optional) material at the
topic sheets. There are two types of topic sheets: bottom. Task sheets contain a Try This Yourself
task and reference. The layout of both is similar step-by-step exercise panel in the detail area as
– an overview at the top, detail in the centre and shown below.

1

2
3
4
5 6
 Topic name
 General topic overview provides an introduction to the topic
 Try This Yourself (task-based topic sheets) is a detailed step-by-step practice
exercise for you to work through. In Reference topic sheets this is usually replaced
by a box with reference information.
 In Task topic sheets screen shots and graphics provide a visual clue as to what will
happen when you work through the Try This Yourself practice exercise. In
Reference topic sheets the screen shots and graphics are used to visually represent
information and concepts.
 The For Your Reference (optional) element provides a quick summary of the steps
required to perform a task. These usually only appear in task-based topic sheets.
 The Handy To Know (optional) element provides additional information such as
alternative ways of accomplishing a task or further information providing handy tips.

© Watsonia Publishing - ii - Preface
Microsoft Access 2016 - Level 2

C
ONTENTS

Chapter 1 Data Validation ....................................................................................................... 1
Assigning Default Values .................................................................................................... 2
Validation Rules And Text .................................................................................................. 3
Validating Numbers ............................................................................................................. 4
Setting Required Fields....................................................................................................... 5
Working With Validations .................................................................................................... 6
Chapter 2 Formatting Tables ................................................................................................. 7
Changing Column Widths ................................................................................................... 8
Formatting Cells In A Table ................................................................................................ 9
Changing Fonts ................................................................................................................. 10
Moving Columns In A Table .............................................................................................. 11
Freezing Columns In A Table ........................................................................................... 12
Hiding Columns In A Table ............................................................................................... 13
Unhiding Columns ............................................................................................................. 14
Chapter 3 Querying Techniques .......................................................................................... 15
Modifying A Saved Query ................................................................................................. 16
Creating AND Queries ...................................................................................................... 17
Creating OR Queries ........................................................................................................ 18
Querying Numeric Data .................................................................................................... 19
Querying Dates ................................................................................................................. 20
Using A Range Expression ............................................................................................... 21
Querying Opposite Values ................................................................................................ 22
Moving Fields In A Query ................................................................................................. 23
Sorting Query Data ........................................................................................................... 24
Removing Fields From A Query ....................................................................................... 25
Querying Using Wildcards ................................................................................................ 26
Problem Characters .......................................................................................................... 27
Querying With A Lookup Table ......................................................................................... 28
Sorting Query Data Numerically ....................................................................................... 29
Displaying NULL Values ................................................................................................... 30
Querying For Uniqueness ................................................................................................. 31
Chapter 4 Parameter Queries............................................................................................... 33
Creating A Parameter Query ............................................................................................ 34
Displaying All Records ...................................................................................................... 35
Using Parameters To Display A Range ............................................................................ 36
Using Parameters In Expressions .................................................................................... 37
Using Parameters With Wildcards .................................................................................... 38
Chapter 5 Calculations In Queries ...................................................................................... 39
Creating A Calculated Field .............................................................................................. 40
Formatting Calculated Fields ............................................................................................ 41
Summarising Data Using A Query .................................................................................... 42
Changing The Grouping ................................................................................................... 43
Calculating With Dates...................................................................................................... 44
Using Criteria In Calculations ........................................................................................... 45
Concatenating String Fields .............................................................................................. 46
Chapter 6 Modifying Forms .................................................................................................. 47
Understanding Form Design And Layout.......................................................................... 48
Switching Between Form Views ....................................................................................... 49
© Watsonia Publishing - iii - Contents
Microsoft Access 2016 - Level 2

Selecting Form Objects..................................................................................................... 50
Working With A Control Stack .......................................................................................... 51
Changing Control Widths .................................................................................................. 52
Moving Controls On A Form ............................................................................................. 53
Aligning Controls ............................................................................................................... 54
Understanding Properties ................................................................................................. 55
Changing Label Captions ................................................................................................. 56
Adding An Unbound Control ............................................................................................. 57
Adding A Control Source .................................................................................................. 58
Formatting A Control ......................................................................................................... 59
Checking The Current Tab Order ..................................................................................... 60
Changing The Tab Order .................................................................................................. 61
Inserting The Date Into The Form Header ........................................................................ 62
Chapter 7 Using A Navigation Form ................................................................................... 63
Creating A Simple Navigation Form ................................................................................. 64
Creating A More Complex Navigation Form ..................................................................... 65
Creating The Navigation Form Page Tabs ....................................................................... 66
Creating Subsidiary Pages ............................................................................................... 67
Adding Additional Subsidiary Pages ................................................................................. 68
Editing Page Tab Names .................................................................................................. 69
Applying A Theme ............................................................................................................. 70
Modifying Properties ......................................................................................................... 71
Automatically Starting The Navigation Form .................................................................... 72
Chapter 8 DIY Forms ............................................................................................................. 73
Creating A Multiple Item Form .......................................................................................... 74
Adjusting The Layout Of A Form ...................................................................................... 75
Typing Into A Form Layout ............................................................................................... 76
Disabling Fields ................................................................................................................. 77
DIY Sub Forms ................................................................................................................. 78
Removing SubForm Redundancy ..................................................................................... 79
Splitting And Merging Layout Cells ................................................................................... 80
Adding An Easy Lookup Field ........................................................................................... 81
Adding Buttons To A Form ............................................................................................... 82
Adjusting Form Properties ................................................................................................ 83
Chapter 9 Creating And Using Macros ............................................................................... 85
Understanding Macros And VBA ...................................................................................... 86
Creating A Macro .............................................................................................................. 87
Running A Macro .............................................................................................................. 88
Modifying An Existing Macro ............................................................................................ 89
Interacting With The User ................................................................................................. 90
Stepping Through A Macro ............................................................................................... 91
Documenting Macros ........................................................................................................ 92
Chapter 10 Macro Techniques ............................................................................................. 93
Creating A Print Macro...................................................................................................... 94
Using Conditions To Enhance A Macro ............................................................................ 95
Creating A Sequence Of Conditions ................................................................................. 96
Understanding The Versatility Of MsgBox ........................................................................ 97
Using The MsgBox Function ............................................................................................. 98
Reconfiguring A Message Box ......................................................................................... 99
Using The InputBox Function ......................................................................................... 100
Chapter 11 Macros On Forms ............................................................................................ 101
Understanding Macros On Forms ................................................................................... 102
Creating Navigation Macros ........................................................................................... 103
© Watsonia Publishing - iv - Contents
Microsoft Access 2016 - Level 2

Accessing Event Macros................................................................................................. 104
Creating Unassigned Buttons ......................................................................................... 105
Programming An Event ................................................................................................... 106
Running An Event Macro ................................................................................................ 107
Modifying An Event Macro .............................................................................................. 108
Setting Echo Off .............................................................................................................. 109
Adding A Close Button .................................................................................................... 110
Creating A Search Macro ............................................................................................... 111
Running The Search Macro ............................................................................................ 112
Understanding The Search Macro .................................................................................. 113
Naming Macros ............................................................................................................... 114
Referencing Macro Sheet Macros .................................................................................. 115
Chapter 12 Navigation Pane Protection ............................................................................ 117
Understanding The Navigation Pane .............................................................................. 118
Creating Custom Categories .......................................................................................... 119
Creating Custom Groups ................................................................................................ 120
Adding Objects To A Group ............................................................................................ 121
Hiding Groups And Objects ............................................................................................ 122
Showing Hidden Groups And Objects ............................................................................ 123
© Watsonia Publishing - v - Contents
Microsoft Access 2016 - Level 2

N :
OTES

© Watsonia Publishing - vi - Contents
Microsoft Access 2016 - Level 2
D V
ATA ALIDATION
CHAPTER 1
INFOCUS

One of your primary concerns with your system should be to ensure
that, to the best of your ability, you'll always have accurate and

relevant information in the system. You won’t be able to produce

meaningful information from the database if the data that is entered
into it in the first place isn't accurate.
That is why in database software, such as Microsoft Access, there
are plenty of features and facilities that can help to reduce the
likelihood of incorrect data entering the system. For example,

Access allows you to make changes to the field properties of a

table. Properties are attributes that control the way that an object
either works or looks. There are several field properties that can be
used to check what has been typed and to restrict errors and
unwanted data being entered into the table.

Ensuring the accuracy of the data is known as validation and is an

important aspect of any system design.

In this session you will:

 learn how to assign default values to a field

 learn how to enter validation rules for a field

 learn how to validate numbers

 learn how to set fields as required
 learn how to work with validations.
© Watsonia Publishing Page 1 Chapter 1 - Data Validation
Microsoft Access 2016 - Level 2
A D V
SSIGNING EFAULT ALUES

With some fields it’s easy to anticipate what data new record is accessed. In our Employees table

would normally be entered into them. When this most new employees are full time and work a 35
occurs you can assign a default value that hour week. These values can be made the default
automatically appears in the field whenever a value when new records are created in the table.

1
Try This Yourself:

n Before starting this exercise
epOeliF yVoauli dMaUtioSnT_ 1o.paecnc dthbe.. .f ile Data

If the yellow Security Warning

appears, click on [Enable

Content]

 In the Navigation pane, right-

click on the Employees table
and select Design View to see
the table in Design View 3
 Click on WeeklyHours to
display the Field Properties
 Select the value (if any) in
Default Value, type 35 and
press
35 hours will now be the
default WeeklyHours value for

all new records…
 Click on the Fulltime field in
the top part of the window to
display the Field Properties 5
 Double-click on No in Default
Value, type Yes and press

All new employees will appear
as Fulltime as a default…
 Click on Save in the Quick
Access Toolbar (QAT) to
save the design changes
 Click on Close at the right of
the table to close it

For Your Reference… Handy to Know…
To assign default values to fields:  If you want to show the current date as a
1. Open the table in Design View Default Value in a date field, type the
function Date() into the Default Value
2. Click on the desired field
property of the field. This will show the
3. Click in the Default Value property and type
system date (that is, the current date) in all
the appropriate value
new records.
© Watsonia Publishing Page 2 Chapter 1 - Data Validation