Design Databases

& Drive Microsoft Access

 

A course for adult learners.

 

Foreword

Databases are an essential management tool. They organise information that is in daily use, and provide facts for planning and analysis. All managers need database skills.

 

This plain English guide will help you to manage and profit from data. Concise information - on the essential features that interest serious beginners - is given. It is intended that this guide be used in conjunction with classroom instruction. It is not a stand-alone course.

 

The guide is based on the questions asked by night-school learners at Tawa College, Wellington. These adult students had no experience with Microsoft Access but wanted to build databases to enhance business management. The guide is now made available in the hope that it will help others with practical needs.

 

The focus is on “design”. If you master the skills of design the rest follows. Driving Microsoft Access is easy.

 

 

Objectives

Students who complete this course will:

 

• Understand the basic concepts of database design

 

• Be able to design simple flat field databases

 

• Be able to design simple relational databases

 

• Be able to build simple databases using Microsoft Access.
  

 

Contents

What you can achieve 6

Control information 6

Uses for databases 6

Learning 6

Why Microsoft Access? 6

Using this guide 7

Data management 8

Data 8

Databases 8

Flat-field and relational databases 8

Microsoft Access database 9

An existing database 9

Database Design 9

Approach 9

Process 10

Step 1:       Decide what must be stored in your database 10

Think about inputs: 11

Think about outputs: 11

Think about logical form and structure: 11

A possible guide: 12

Step 2:       Decide the tables and fields 12

Tables proposed: 12

Smallest required unit: 12

Utility tables: 14

Step 3:       Decide the relationships between tables 14

Step 4:       Get the tables working 14

Step 5:       Get data input organised 15

Step 6:       Plan for the extraction of information 15

Step 7:       Get the queries and reports working 15

Step 8:       Ongoing developments 16

Drive Microsoft Access 16

Versions of Microsoft Access 16

Aids built into Microsoft Access 16

Tables 17

Forms 18

Queries 18

Reports 18

Tables 18

Navigation in Tables 19

Find data 20

Enter, edit and delete data 20

Add, delete or move a field 20

Format fields 20

Construct tables version 2.0 20

Primary key 21

Field properties 21

Data types 21

Number fields 22

Telephone numbers 22

Add and delete fields 22

Import data 23

Export data 23

Relationships 23

Data integrity 24

Create relationships 24

Keeping data consistent in different tables 24

Construct tables versions 7 and 8 25

Forms 25

Produce forms 25

Modify forms 25

Combo and list boxes 25

Fonts and colours 26

Create forms, versions 7.0 and 8.0 26

Lists taken from other tables, versions 7.0 and 8.0 26

Queries 26

Use 26

Kinds of queries 27

To construct a select query 28

Query wizard, versions 7.0 and 8.0 30

Parameter query 30

Find unmatched query 30

Make-table query 31

Crosstab query 31

To archive, delete, or change data 31

Reports 31

Use 31

Types of reports 32

Create report 32

Pictures in forms & reports 32

Embedded objects 32

Bound and unbound objects 32

Add pictures: bound object 33

Add pictures: unbound objects 33

Add a graph 33

Image control in version 7.0 34

Calculations 34

Expressions 34

Expression builder 34

Macros 35

Customise for user 36

Control panel 36

Retreating from control panels 36

Store obsolete data 36

 

 

What you can achieve

In this section you will find information to help you orient yourself to your course and to establish realistic expectations. This guide will help you to get control of the information you hold and use. It is a concise, no-frills do-it-yourself manual designed to guide your thinking.

 

 

Uses for databases

Businesses use databases for these main purposes:

 

·        To increase the efficiency of operations (stock control, invoices, orders, contacts, accounts, tax returns, cash flows, budgets)

 

·        To provide statistics, summaries and forecasts

 

·        To manage marketing and customer relationships

 

·        For marketing research

 

Leisure users want databases for two main reasons:

 

·        To delight in their collections (records held in magnificent order)

 

·        To ease management tasks (club/event organisation).

 

Learning

Adults learn to design and build databases in about 10 hours. This assumes familiarity with other Microsoft programs and good motivation. About half the time needed is to learn the skills of design.

 

Learning Microsoft Access is different from learning other applications. When you learn those skills your main task keyboard and mouse. With Microsoft Access the main task is to understand design concepts.

 

 

Why Microsoft Access?

Microsoft Access is a useful database program because it is:

 

·        Suited to businesses management, clubs and hobbies

 

·        Easy to obtain extensive assistance from help screens

 

·        Easy to operate with other programs

 

·        Efficient in computer terms - the advantages of a relational database

 

·        Amateurs can build sophisticated databases

 

 

Using this guide

The approach taken in this book is to:

 

 

 

 

Set yourself specific projects for practice. Suitable projects include:

 

·        A "little-black-book" to record your friends

 

·        Household accounts

 

·        A contact database for suppliers, customers and business people

 

·        Sole operator accounts with GST/sales tax returns

 

·        Customers, orders (dates, products, quantities, customers), and invoices.

 

Wrestle with such design-and-build problems and you will gain practical and marketable skills.

 

Data management

Data