Ms Access Tutorial

Introduction to Microsoft Access 97

Microsoft ACCESS 97 Microsoft Access 97 is a Database Management System (DBMS), it consists of a database(s) and the information handling applications needed to access it. MS Access can help you create, maintain, and report on data and table relationships in a database.

An Overview of Database Database A database consists of one or more than one tables in which data are stored and managed. Inside the database, tables work together with queries and reports to produce the required output. Table A table consists of records, which is the data about a particular subject. E.g. a database might contain a table of data on students (names, student ID, address, telephone number) Field A field is a single unit of data, such as a person's name or an address or even a telephone number. A field generally appears as a single column in a table. Primary Key field A primary key field is one that uniquely identifies each record in a table. Your HKID number is an everyday example of a primary key field, because it uniquely identifies you on the government's database. Form A form is an interface, which display data or allow users to input and edit data. Query A query is a special database feature that allows a user to ask questions and execute commands. When performing a query, you can set conditions for selecting records. It is usually used to summarise or update data such as sorting, filtering, joining tables, and performing calculations.

1

Introduction to Microsoft Access 97

Create a database To create the database 1. Choose File -> New Database from the Access menu bar, or press Ctrl+N 2. In the file name text box, type Employee and then choose OK. You will see an empty Database window title Employee : Database, as in the figure shown below. Each database object that you create in these lessons will be added to the Database window.

Create a table To create a table 1. In the database window, click the Tables tab. 2. Click the New button in the database window. 3. In the New Table dialog box, click Table Wizards.

2

Introduction to Microsoft Access 97

Choosing Fields for your table 1. Click the Business option button 2. In the Sample Tables list, click Employees. 3. In the sample fields list, double-click the field names listed in the table below to add them to the table you are creating. Fields are: EmployeeID, FirstName, LastName, Title, Salary.

4. Click the Next button to continue to the next wizard screen. 5. When asked what you want to do after Access creates the table, choose Enter data directly into the table and then finish. This will take you to the design window shown in the figure below.

Adding, changing and deleting records To add new records to a table of a database 1. Click the Tables tab in the Database window, select a wanted table, and chick the Open button 2. A Datasheet view of the table will be shown

3

Introduction to Microsoft Access 97

3. Move the cursor to the last row and enter a new record

To change the contents of a record 1. Go to the required record 2. Click the required data field(s) to change the contents 3. Move the cursor out of the record will update it

To delete record 1. Position the mouse pointer on the row selector of the record to be deleted 2. Click the row selector to select the whole record

3. Press the DEL key to delete it

4

Introduction to Microsoft Access 97

4. Click the Yes button to complete the deletion, or click the No button to cancel deletion

Use a form to view and edit data To use Form View, a form that associates a table of a database must be created in advance. To create a form 1. By clicking the AutoForm button in the toolbar, or from menu Insert -> AutoForm, Access instantly creates the form for entering and editing Friends data. The form contains one control for each field in your table, though some of those might be scrolled off the bottom of the form window at the moment. Toolbar

Menu

2. Close and save the form as Employees.

Adding and editing data Open the form 1. Start from the database window, and click the Forms object tab. 5

Introduction to Microsoft Access 97

2. Double-click the Employees form name, or highlight the form name and choose Open. Your data will appear in form view.

Sorting and filtering Sort records by Last Name 1. In the database window, click the Tables tab. 2. Select table Employees and click the Open button in the database window. 3. Click any person's surname to move the cursor into the Last Name column. 4. Choose from menu Records -> Sort -> Sort Ascending

6

Introduction to Microsoft Access 97

Records are sorted by surnames. You can follow steps 1 through 4 to sort on any field in your table. To put records back in their original order, choose Records -> Remove Filter/Sort.

Filter records in table 1. In the database window, click the Tables tab. 2. Select table Employees and click the Open button in the database window. 3. Click any person's surname, which you want to filter, say "Chan", into the Last Name column. 4. Choose from menu Records -> Filter -> Filter By Selection.

7

Introduction to Microsoft Access 97

To show all records, choose Records -> Remove Filter/Sort.

Filter your records by form 1. In the database window, click the Forms tab. 2. Select form Employees and click the Open button in the database window. 3. Right click the field you want to filter such as the Last Name field. You should see a quick menu of options. 4. Type exactly the last name you are looking for in the Filter For box and press [Enter].

Querying a database To create a new query 1. In the database window, click the Query tab 2. Click the New button 3. With Design View highlighted, click OK button.

8

Introduction to Microsoft Access 97

4. Click Add button in the Show Table dialog box to add related table and click Close button.

5. The Query design window will appear.

9

Introduction to Microsoft Access 97

To include fields in the Design Grid •

Drag the field form the field list to a column in the Design Grid; or

•

Double-click the fields from the Field List Box;

or •

Click column in grid, click arrow and the click field you want.

•

You may click * to add all fields.

Using criteria in a query Exact value •

Click the Criteria entry in the Design Grid, type the required text or number without any dollar signs or commas.

To use comparison operators •

The comparison operators for numeric data comparison include: =

equal to

<>

not equal to

<

less than

10

Introduction to Microsoft Access 97

•

>

greater than

<=

less than or equal to

>=

greater than or equal to

Click the Criteria entry, type the comparison operator and number.

To use wildcards •

The following wildcard characters are used to search field values in queries:

Character

Usage

Example

*

Matches any number of characters. It can LIKE Ma* be used as the first or last character in the finds Ma, Mak, and Make character string.

?

Matches any single alphabetic character.

LIKE C?m finds Cam, and Com.

[]

Matches any single character within the LIKE n[iu]ll brackets. finds nill and null

!

Matches any character not in the brackets

LIKE b[!ae]ll finds bill and bull but not bell

-

Matches any one of a range of characters. LIKE c[a-c]ll You must specify the range in ascending finds call, cbll, and ccll order (A to Z, not Z to A)

#

Matches any single numeric character

LIKE B#20 finds B220, B320, and B420

•

Click the Criteria entry, type LIKE followed by the text with wildcard character.

11

Introduction to Microsoft Access 97

Using calculated fields 1. Save your query first 2. Click a new column for a calculated field 3. Right-click and then click the Build menu

4. The Expression Builder design window will appear 5. Entry your expression for computation and click OK. For example, in the following figure, the Expression Builder will return a value called Annual which is computed by multiplying Salary by 12

6. Expression will be shown in Field

12

Introduction to Microsoft Access 97

To run query •

Click the

•

Choose from the menu Query -> Run

button; or

Creating report A report is an effective way to present your data in a printed format. Because you have control over the size and appearance of everything on a report, you can display the information the way you want to see it. Create a report using AutoReport 1. In the Database window, click the Reports tab. 2. Click New. 3. In the New Report dialog box, click one of the following wizards: •

AutoReport: Columnar. Each field appears on a separate line with a label to its left.

•

AutoReport: Tabular. The fields in each record appear on one line, and the labels print once at the top of each page.

4. Click the table or query that contains the data you want to base your report on.

13

Introduction to Microsoft Access 97

5. Click OK. AutoReport: Columnar

AutoReport: Tabular

14

Introduction to Microsoft Access 97

Create a report using Report Wizard 1. In the Database window, click the Reports tab 2. Click New 3. In the New Report dialog box, click Report Wizard, select a table or query in the drop-down list box, and then click the OK buton

4. When the Report Wizard dialog box comes up, click on the button to include all the fields from Available Fields. Click on the Next button to continue

15

Introduction to Microsoft Access 97

5. Select a field for group level, then click the Next button to continue

6. Select the sort order on this screen

16

Introduction to Microsoft Access 97

7. Choose Layout and Orientation, a preview is shown on the left

8. Choose a style on this screen.

9. Finally, type a name for the report and click the Finish button to preview the report 17

Introduction to Microsoft Access 97

10. Preview of the report

18

Introduction to Microsoft Access 97

Exercise Exercise 1 1. Create a new database MyData.mdb and a new table Employees with the following fields EmployeeID, FirstName, LastName, Title, Salary 2. Close the table Exercise 2 1. Open MyData.mdb and the created table Employees 2. Input the following data Employee ID First Name Last Name

Title

Salary

1

Bruce

Lee

CEO

$100,000.00

2

Jackie

Chan

Marketing Manager

$50,000.00

3

Victor

Li

IT Manager

$50,000.00

4

Sandy

Lam

Personnel Manager

$50,000.00

5

Shirley

Kwan

Accountant

$40,000.00

6

Leon

Lai

Account Officer

$15,000.00

7

Linda

Wong

Clerk

$11,000.00

8

Alan

Tam

Clerk

$9,000.00

9

Danny

Chan

Clerk

$8,500.00

10

Jacky

Cheung

Office Assistant

$6,000.00

3. Close the table Exercise 3 4. Create a form by AutoForm and name it as Employees 5. Close the form Exercise 4 1. Open MyData.mdb 2. Delete record number 6 in the Employees table 3. Change Jacky Cheung's salary to $6,800 4. Add the following record Bill / Gays / Sanitary Engineer / $4,500 5. Close the table

19

Introduction to Microsoft Access 97

Exercise 5 1. Open MyData.mdb 2. Sort your records by Last Name 3. Return to the original order Exercise 6 1. Open MyData.mdb 2. Filter records with First Name starting with "S" 3. Show all records again Exercise 7 1. Create a query with fields FirstName, LastName, Salary 2. Only employees' first name starting with "S" and whose Salary are between $7,000 and $50,000 should be shown 3. Add a calculated field that computes employees annual payment

4. Save the query 5. Run the query

20

Introduction to Microsoft Access 97

Exercise 8 1. Create a report in your favourable format 2. Print the report

21