Gerard Strong - How to Use Microsoft Access 2010

Contents

CHAPTER 1

CHAPTER 2

CHAPTER 3

CHAPTER 4

CHAPTER 5

CHAPTER 6

CHAPTER 7

CHAPTER 8

Copyright Gerard Strong 2012

Trademarks

All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalised. We cannot be attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of ay trademark or service mark. Access 2007 and Microsoft are registered trademarks of Microsoft Corporation.

Contact; mrgerardstrong@gmail.com

Chapter 1

INTRODUCTION TO DATABASES

This chapter is important to read, so that you understand how to plan the structure of a database. However, you may wish to skip this Chapter if you just want to get on with using Microsoft Access 2007

WHAT IS MICROSOFT ACCESS?

Microsoft Access is a data managements system. Databases are used to store large amounts of information, and allow you to store large amounts of data. One benefit of Microsoft Access is that it allows you to find data to provide more useful information.

A database is based on tables of data, and each table contains any number of records (rows).

A recorded has many fields .

See table below for a typical layout for a table of data;

Question 1 How many rows are there in the table? Question 2 How many fields are there in the table? Question 3 How many columns are there in the table? Question 4 How many fields are there in each record?

Customer Name	Date of birth	Location
Bob Harris	01/03/2001	London
Mary Reynolds	17/01/2002	London
Mark Smith	28/09/2000	New York
Lily Grant	21/06/2000	Madrid

Answer 1 There are 4 rows on the table.

Answer 2 Each row holds one record , so there are 4 records in this table.

Answer 3 There are 3 columns. The column headings are the field names .

Answer 4 There are 3 fields in each record the same as the number of columns

The business owner can use the database to find out information such as;

Whether or not he has a customer with a particular name? Which Customers are from London? Which location he has most customers? What customers have the earliest date of birth? How many customers were born in 2000?

Databases are not really used for small amounts of data, because the answers to the above questions can be answered easily just by looking at the table. However, if you had hundreds of customers, there would be hundreds of rows, and the table would be so big it would take a long time to answer the questions. This is where a database becomes helpful.

Before creating a database on the computer it is important to plan your database.

PLANNING A DATABASE

When planning a database you need to think about:

What is the purpose of the database?

What information will you want to look up in the database?What data will you store in the database?

THE SCHOOL TRIP DATABASE

You are going to design a database for a school that is planning various activity weekends for its pupils.

Purpose of the database:

The school trip organiser needs to be able to find out quickly and easily;

Who is going on a particular activity (for a register)How many of the people signed up for a particular activity want transport (so transport can be arranged);Contact name and number of a particular member.

After talking to the school trip organiser, you have found out:

The school is planning 4 activity weekends.Each weekend will involve a different activity, pupils can pick from:
o Cowboy Adventure o Cook & Create or o Mountain Biking The weekend each cost 50 in total.The school will provide optional transport from the school to wherever the activity is taking place.The school needs to have a contact name (e.g. parents name) telephone number for each member.

Your database should contain details about each pupil and their chosen activity. The next step is to decide which fields to have in your table. Based on the information given above, the table must contain the following information about each member:

o Name o Date of birth o Contact Name o Contact Number o Chosen activity o Amount Paid o Transport required?

This list will be used to decide what fields are needed in your table.

DATA TYPES

Before you can enter the fields into your database, you need to think about what format the data will be in. Access has different data types to pick, which you can see in the table below;

Text	Letters, symbols and numbers, i.e. Alphanumeric data.
Number	Numbers only (no letter at all). Included numbers with decimal points.
Date/Time	Dates and Times
Currency	For all monetary data. Access will insert a currency symbol before the amount (such as , $ or etc)
Yes/No	Used wherever the fields can only take 2 values, Y/N, True/False etc.
AutoNumber	This is a unique value generated by Access for each record

DECIDING ON DATA TYPES

You will need to pick a data type for each field from the table 1.2. You must be careful when you plan to pick the correct data type for the field as you will not be easy to change it once you start to enter data into the table.

For example, should you pick a telephone number as a text field or a Number field?

At first you may think that a Number field would be best but in practice this is a bad idea for two reasons;

Access will not record the first zero in a number field. So if the number is 035545300 it will be recorded as 35545300.Access will not allow you to have a space or bracket in a number field. So you will not able to record a number like this, 01355 599600.

SELECTING FIELDS

We already have a list that will form the basis of the fields in the database (Name, Date of Birth). You may now which to split some of the pieces of information, for example, Name will include a first name and a surname. Should you split this into two fields? There are two main reasons why you may wish to do this;

If you want to sort the members alphabetically by surname, this is much easier to do of surname is a separate field.You may want to search for a pupils record. It will be much easier to search on the surname field.

DESIGNING THE DATABASE

When you think of the structure of the database you should think of what the table will look like without any information in it at all (the design of the table). To describe the structure you need to know some things:

The number of fields (columns) in the table The field names (column headings) The data type of each field

The number of rows will increase as we enter more.

It is important to know the difference between the database structure (think of it as empty with no records) and the data entered into the database (the information that you put on the table).

For each of the following changes to the Database bellow, would you need to change the database structure or edit the database?

Question 1 You decide to add a new field name ContactInfo, to the database.

Question 2 Bob Harris is actually from Paris.

Customer Name	Date of birth	Location
Bob Harris	01/03/2001	London
Mary Reynolds	17/01/2002	London
Mark Smith	28/09/2000	New York
Lily Grant	21/06/2000	Madrid

Next page