MS-ACCESS



Microsoft Access Tutorial

Lesson 1: General Introduction 3

Introduction 3

Database Terminology 3

Starting Ms Access 5

Designing a Database 5

Creating a New Database 6

Exiting Ms Access 7

Lesson 2: Databases and Tables 8

Opening an Existing Database 8

Creating Tables 9

Creating relationships between tables 16

Adding Records to a Table 20

Deleting Data in a Record 21

Changing the width of a column/Moving a column 22

Finding Records 23

Filtering and Sorting Records 25

Sorting 26

Exercise 28

Lesson 3: Queries 32

Introduction to Queries 32

Creating and Saving Queries 32

Complex Queries 34

Creating Complex Queries 35

Print the Resulting Data in a Query 37

Query Types 38

Creating Total Queries 38

Exercise 41

Lesson 4: Forms 42

Features of a Form 42

Creating Forms Using the Form Wizard 42

Creating Forms Using Auto form 43

Creating a Form Using Design View 45

Opening a Data Entry Form 45

Customising a Form 45

Object Linking and Embedding (Ole) 46

Creating Unbound Controls 47

Calculated Unbound Controls 48

Data Protection 53

Editing Data in a Form 53

Exercise 54

Lesson 5: Reports 56

Creating Reports Using a Wizard 56

Creating Reports Using Design View 62

Sections of the Report 64

Previewing and Printing Reports 65

Reports Based In Multiple Tables 68

Report Printing 71

Exercise 71

Ms Access Project 72

Introduction to Ms-Access

Lesson 1: General Introduction

1.0 INTRODUCTION

Microsoft Access is a Windows based application. It helps in storing information about different subjects in separate tables. It allows for creation of forms, which automate tasks, queries and reports that help analyse data.

Benefits

1. Faster Retrieval of information

2. Analysing data using queries and producing reports for management purposes

3. Security using a Login password to prevent unauthorised access of data

4. Consistency because data is stored in a central place and is not scattered in different places

5. Data sharing by people different departments through the Local area network(LAN)

The main focus of Ms Access is the Tables otherwise known as files. Tables Consist of Fields (attributes) and are used to store data e.g An Employees Table.

Database Terminology

Data

The material (Stuff) that Access stores, organises and manages for you

Fields

The place where data is placed within the database is called a field. One field holds one piece of data. If you are storing student details, possible fields would include Name, Admission number, Age…

Records

All the fields for one student constitute a record, Records ensure which name relates to which admission number, age

Table

A collection of records that describe similar data is called a table. A database for student details could have tables for admission information, fees, and exams…

Index

An index speeds up queries on the indexed fields as well as sorting and grouping operations. For example, if you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name.

Internally, an index is implemented as a look up list much like the Index you use to find information about a particular item in a book.

Primary Key

A field created in a table to UNIQUELY identify records. For example the NationalID number field has a unique value for every employee and therefore can be used as a primary key.

Primary keys are needed to aid in searching for records. This is because a field such as the Employees Name is likely to contain similar names for different people. This means that it is more prudent to invent a series of codes (Usually Numerical) to identify the employees uniquely.

1.01 Starting MS Access

Start windows and then click Microsoft Access icon to run the application or Select the program from the Program listing in the Start menu as shown below

[pic]

Designing a database

A database is a collection of related data.

1. Determine the purpose of the database. This helps in deciding the facts to be stored.

1. Determine the tables. Divide the information into separate subjects, such as employees or orders. Each subject will be a table in the database.

3. Determine the fields. Decide what information to store in each table. Each field is displayed as a column in the table.

NOTE

When you create a Microsoft Access database, you create one file that contains the data and table structures as well as the queries, forms, reports, macros and modules.

1.02 CREATING A NEW DATABASE

Once you have loaded the Ms Access application, you will see a dialog similar to the following: (If it is not visible: From ‘View’ Choose ‘Tool Bars’ then ‘Task Pane’)

[pic]

The Part Marked ‘C’ enables you to create a blank new database

NB: A database file must be saved before it can be used and you will be prompted to save the file first i.e

To create a new database:

a) Choose ‘Blank Database’ as shown in ‘B’ on the Picture above.

Alternatively:

b) You may click on the ‘New’ Icon ([pic])

Either way you will get the ‘Save’ dialog box as shown below. Type the name of the Database and click on the ‘Create’ Button.

[pic]

NB

The database window is named after the name of the database that you have created. Thus:

[pic]

03. Exiting Ms Access

1. Before exiting Ms-Access, ensure that all your tables and other database objects (new or ones to which you have made cahges) are saved, if they are not saved you will get a dialog similar to the one below

[pic]

2. You need to be sure of the choice that you will make since it may have implications on the data that you have stored in the database

3. Once all database objects are closed, you may close the database window by clicking on the exit ([pic]) button

4. Exit ms-Access by choosing “Exit” from the “File” menu

Lesson 2: Databases and Tables

2.01 Opening an Existing Database

The part marked ‘A’ (See Picture Above) shows a list of existing databases that you opened in the recent past. This list will be different for different system users (If you are using Windows XP or 2000). By clicking on either of the names in the list, you will be able to open the chosen database.

To open a database that is not listed, you may choose ‘Open’ from the ‘File’ menu i.e.

You may also use the ‘Open’ Icon ([pic])

Either way you will get the ‘Open’ dialog box from where you must choose the source of the file i.e.

[pic]

Choose the file you want to open and click on the ‘Open’ button

2.02 creating tables

TABLES

Creating a new table

1. Determine the fields you would like to include in the table

2. Select the Tables option and click on the New button

1. Select Design view

2. Tables are created when you want to store information that is not stored in an already existing table e.g. when you want to customers and you only have a ‘Products’ table.

[pic]

Click on the ‘OK’ Button as shown above.

This results in the display of a design window for the new table. It is here that you define the fields associated with this table; their names, data types and descriptions. Each field within a table has a name. This name can be up to 64 characters long, and can include letters, numbers and spaces.

[pic]

The following is a summary of all the field data types available in Microsoft Access, their uses, and their storage sizes.

Field Properties

Text

Stores text or combinations of text and numbers, such as addresses, numbers that do not require calculations, such as phone numbers, part numbers, or postal codes of size up to 255 characters.

Memo

Stores lengthy text and numbers, such as general notes or detailed descriptions. Memo fields hold up to 64000 characters of information.

Number

Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). Set the Field Size property to define the specific Number type.

Date/Time

Stores Dates and times

Currency

Currency values. Use the Currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right.

AutoNumber

Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added.

Yes/No

Fields that will contain only one of two values, such as Yes/No, True/False, On/Off.

OLE Object

Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol, that can be linked to or embedded in a Microsoft Access table. You must use a bound object frame in a form or report to display the OLE object.

Field properties

Depending on the nature of the contents of your field, a certain level of control can be achieved such that a field will accept what it has been programmed to have. The following options are offered:

FIELD SIZE

Allows entry of field size for Text data type For example, if the standard length of names is 25 characters, you may use this property to limit the number of characters entered into such a field to 25. In this case, if there are existing names longer than 25 characters then they will be truncated to 25.

For numeric data types, you choose the field size by selecting from a drop-down list. Field size does not apply to the Date/Time, Yes/No, Currency, Memo or OLE object data type.

FORMAT

Allows selection of a predefined format in which to display the values in the field from the drop-down combo list applicable to the data type that you chose (Except Text). One can also customise a form of presentation of data. E.g. to have a code appearing before every telephone number you wish to enter, select the relevant field and set the format:

“02-”000000 (This gives the code for Nairobi)

DECIMAL PLACES

Can select specific number of decimal places from the drop-down combo list, and applies only to number and currency fields.

INPUT MASK

Helps put a mask for controlling the entry e.g. setting a limit especially to the entry of numeric fields:

000000 Will allow a six digit number no more than that or less.

999999 Will allow six or less digits.

CAPTION

This is a name used (other than the field name) to appear in the field name header button in Table datasheet view.

DEFAULT VALUE

Allows setting of a value, which will automatically be entered into the field when a new record is added to a field. E.g. Setting default date as current date: =Date()

VALIDATION RULE

Validation rules test the value entered in a field against set criteria. E.g.

Like “Jordan” : Will only accept the word Jordan

=5000 AND ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download