MS Access 2003 Basic Features

1
MS Access 2003 Basic Features

• Distance Learning
• Chapman 633/632
• mxccedtech_at_gmail.com
• www.mxcc.commnet.edu/distance
• [phone]/5783
• Workshop Web Site
• www.mxcc.commnet.edu/workshops

2
Introduction to MS Access 2003

• Concepts of Database
• Access 2003 Basic Features
• Tables
• Forms
• Reports
• Queries
• Update Information on Tables, Forms, Reports, and
  Queries

3
Microsoft Access Window

• Open Access
• Start-Programs-Microsoft Access
• Double-click on an Access file (Address)

4
Concepts of Database

• Database is a collection of records and files
  that are organized for a particular purpose.
• A student record book with addresses and grades
• An inventory with software, books, and hardware
• A table in MS Access is a set of records with
  related information displayed in rows and
  columns.
• A database in MS Access consists of one or more
  tables. The tables may be related.
• A field is a basic fact of a record (or data
  element).
• Name, address,
• A primary key is a field that identify a single
  record.
• In the primary key field, every record has a
  unique number. (ID field)
• A record is a set of fields
• A person, a student, a book

5
Microsoft Access Objects

• Tables
• Source information with Columns (fields), rows
  (records)
• Queries
• Subset of information with certain criteria
  extracted from table (s).
• Forms
• Display one or more records in one page
• Convenient for entering and updating data.
• Reports
• Display records with selected fields in a
  formatted layout.
• Display multiple records in a page divided by a
  certain group.

6
Microsoft Access Objects

• Pages
• Display records in form of web pages.
• Macros
• A set of commands that are executed automatically
  one after another. Macros are used to automate
  the performance of any repetitive task.
• Modules
• Provides a greater degree of automation through
  programming in Visual Basics for Applications
  (VBA)

7
An Access Database File
Form1A Form1N
Report1A Report1N
Query1A Query1N
Form2A Form2N
Report2A Report2N
Query2A Query2N
Form3A Form3N
Report3A Report3N
Query3A QueryAN
8
An Access Database File
Form1 FormN
Report1 ReportN
Query1 QueryN
9
Table-Datasheet View

• Double-click a table, you are in datasheet view.
• Add, edit, or delete records

Field Caption
10
Table-Design View

• Define the table initially and to specify the
  fields it contains.

Define Field Name
Define Field Type
Design View Form, Query, and Report
11
Field Name and Caption

• Field Name
• Used in reference of other objects, calculations,
  or visual basics programming.
• Should be short and concise.
• Caption
• The column header in a table.
• Can be descriptive and meaningful.
• When it is blank, the Field Name is used as the
  column header.
• Advantage of using the Caption
• After the database is built with tables, forms,
  reports, and queries, if you would like to change
  a column header, you only change the Caption not
  the field name.

12
Print or Change Properties of Tables, Forms,
Reports, Queries

• In the database window
• Right-mouse click an object (report, form, query,
  page)
• Print
• Cut
• Copy
• Email
• Delete
• Rename

13
Plan and Create a Database

• The purpose of the database.
• Tables to create (address)
• Fields in each table (Name, phone,)
• Smallest meaningful value possible.
• An ID field may serve as the primary key field.
• The primary key for a table is a unique
  identifier for each record in the table.

14
Create a Database

• Start-Programs-Microsoft Access
• Select Create a New File from the Task Pane
  (right).
• From the Task Pane, click Blank Database.

15
Save the Database

• Type the name of the database file.
• If you need to save the file into a new folder,
  you click on and type in the folder name. Click
  on
• Type the file name.
• Click

16
Create a Table in Design View

• Double-click
• For each field
• Type the field name.
• Select a data type .
• Type the Caption.
• Select student ID field
• Click on to set a primary
• key
• Click on to save the table
• Type in the table name
• Click on OK.
• Click to enter data.

Use Tab or arrow keys to navigate.
17
Enter Data in the Table

• Click on to go to the datasheet view when
  you use Design View to create a new table.
• Type in information in the cells
• tab key, or to navigate to a
  different field.
• keys to a different record.
• Adjust the column width
• Move the mouse to the boarder between the two
  columns until a sign shows, and drag the
  boarder to adjust the column width.
• Double-click the boarder between the two columns
  and the column width will be adjusted
  automatically.
• Delete a record, select any cell of the record
  and click

18
Add a Field in a Table Created In Design View

• Add a parents name field before address
  field
• Select the address field, go to Insert and
  select rows
• Type the parents name for the field name.
• Click to update the table.

19
Reorder a Field

• In the Design View of a table
• Click the field and hold the mouse until a square
  shows. Drag the field and move it to a different
  row.

• In the Data View of a table
• Click the field and hold the mouse until a square
  shows. Drag the field header and move it to a
  different column.

20
Create a Table by Using Wizard

• Double-click on
• Select the category of the wizard, business
• Select Student in the sample table window.
• Select StudentID in the Sample Fields window,
  click on
• Click on , type in the new
  name ID. Click on
• Select Firstname, Lastname, and other fields
  individually and use to move the field to
  the selected field window.
• Click on

21
Select a Wizard and Fields
22
Name the Table

• Type in the name of the table
• Select , click
  on

23
Select a Primary Key Field

• Select StudentID as the primary key, Next

24
Set Up Data Input
25
Modify the Table

• You need to change StudentID from Number to Text
  data type if you use Banner ID.
• To add _at_ for the Banner ID, type _at_00000000 in
  the Input Mask box.

26
Create a Table by Entering Data

• In database window
• Double-click
• In the Datasheet view
• Double-click field1, type a field name ID.
• Double-click field2, type a field name First
  Name
• repeat the above steps until you type all the
  fields.
• You need to set up a primary key by going to
  Design View
• Click , select ID field cell and click
• Click to save the table.
• Click to Datasheet View to enter data.

27
Navigating and Updating Data in the Table
Save Data-Access automatically save a table as
soon as you move to the next record.
Previous Record
Next Record
Add a New Record
28
Updating Records in a Table

• Adding a record
• Type in the fields right next to
• Deleting a record
• Select a record by clicking , hit delete
  key or .
• Change a record
• Highlight or click the cell you want to change
  and type new information

29
Create a Form with AutoForm

• Form, easy to enter and update data. With the
  form, you can enter or update data used by
  tables, queries, and reports.
• AutoForm allows you to create a form including
  all fields of a table.
• Open the file Address. In the database window,
  select the table tbl_office_info, click
  AutoForm .

30
Modify the Form

• Click on to switch to Design View
• Drag the right border of the form box to the
  right to enlarge the size of the form window.
• Select the Mailing Address box, drag the right
  border to the right.
• Click on to switch back to Form View.

31
Save the Form

• Click on , type in the form name and
  click

32
Update Records in a Form

• In the Datasheet View of the form
• Use to select a
  record
• To update with new information, highlight a field
  and type in new information.
• To delete a record, select a record and click
• Use to add a new record. The record will
  be added to the table simultaneously.

33
Search for a Record in a Form

• In the Datasheet View of the form
• Edit-Find, type the last name Smith.
• In Look In box, select the form name.
• Click Find Next.

34
Create a Form with Form Wizard

• Create a form with selected fields from a table.
• In the database window, select
• Double-click
• Select the table you want to create a form from
  tbl_home_info.
• Select a field and click . Click
  to select all fields. Click Next.

35
Create a Form from Wizard

• Select a form layout such as
• Click
• Select a style such as Expedition
• Click
• Type a form title.
• Select Modify the forms design.
• Click
• Then you are in form design view and you may
  modify the form.

36
Turn the Field Scroll Bar Off

• The fields in a form created by Form Wizard might
  have scroll bars as default.
• Click Design View icon
• Select a field, press Shift key and select all
  the fields one by one.
• Click Property icon .
• Click Format tab.
• In Scroll Bar, select None.

37
Create a Report

• Report Display records in selected fields
• Create a college phone directory by campus with
  following fields
• First Name, Last Name, Title, Extension, Dept.,
  Campus
• In the Database Objects Window, select
• Double-click on Create report by using wizard

38
Select Fields and Set Up the Grouping Field

• To show the report by campus
• Select campus and click gt
• Click Next button.

• Select the table tbl_office_info.
• Select fields
• Double-click a field one at a time.
• Click Next.

39
Select Report Layout

• Skip the sort field step by clicking Next.
• Select a layout and click Next

40
Select a Report Style

• Select a style and click

41
Title the Report

• Type the title of the report, select modify the
  reports design, click .

42
Modify the Report

• Go to the Design View of the report by clicking
• Resize
• Click First Name box, drag the right border to
  the left to make it smaller.
• Move
• Click the Last Name title box, hold Shift and
  click the Last Name field box.
• Click the left arrow to move it closer to the
  First Name field

43
Sort the Report by Last Name

• Select the Last Name title box, press shift key
  and select the Last Name field box.
• Click sorting and grouping icon
• Select Last Name field in the Sorting and
  Grouping window. Close the window.
• Click to preview the report.

44
Make Address Labels

• You can create address labels with a Report Table
  Wizard.
• In the Table view, select the table
  tbl_home_info.
• Select Report wizard icon.

45
Label Wizard

• Select Label Wizard.
• Click OK button.
• Select a Product Number for the label (5160,
  English, Avery).
• Avery and English are default. You may need
  to change if your label is not Avery/English.
• Click Next

46
Select Fonts and Size

• Select text font and size (Arial, 10), click
  Next.

47
Select Fields

• Select First Name field, click gt button. Hit
  the space bar once.
• Select Last Name field, click gt button. Hit
  Enter key.
• Select Address field, click gt button. Hit
  Enter key.
• Select Town field, click gt button. Hit the
  space bar once.
• Select State field, click gt button. Hit the
  space bar once.
• Select Zip field, click gt button. Hit the
  Enter key.
• Click Next button.

48
Sort By

• If you would like the labels sorted by last name
  field, select Last Name and click gt.
• Click Next button.

49
Name, Column Width

• In the preview window, click Page Setup, select
  Column tab. Change the column width to 2.5.
• You may need to adjust column width

• Give a name for the label report. Click Finish
  button.
• In the next screen, you go to File, select
  Print to print the label.

50
Create a Query Using Wizard

• Running a query is to display selected fields
  with certain criteria.
• People from the LSD division with First Name,
  Last Name, Title, Office, Extension, and campus.
• In the Database Window, click
• Double-click
• Select tbl_office_info table.
• Select fields
• Double-clicking a field one by one.
• Click Next.

51
Name the Query

• Give a name for the query qry_LSD.
• Check Modify the Query design.
• Click Finish.

52
Set Up the Criteria and Run the Query

• Type LSD in the Dept. criteria cell.
• Hit Enter key.
• Click to run the query. Click to
  save.

53
Create a Query in Design View

• Display a phone directory for Business with First
  Name, Last Name, Title, Dept, Office, Extension.
• In the Objects window, click on
• Click
• Click Add to add the tbl_office_info table.
  Click Close.
• Double-click on a field to select the fields you
  need in the query.
• Type Business in the Dept. Criteria row.
• Click ! to run the query. Click the save icon
  to save the query with a name.

54
Show/Hide a Field in a Query

• You may hide a field in a query.
• In the Query Design view qry_LSD
• Uncheck the Show box of the Dept field.

55
Reorder a Query Field

• Click the field and hold the mouse until you see
  a small square box. Drag the field and place it
  in front of another field.

56
Sorting in a Table or a Query.

• Open a table or a query (tbl_home_info).
• Click any cell of the field that you would like
  to sort.
• Click the Descending / Ascending icon.

57
Freeze a Column in Table or Query

• You may freeze selected columns in a table if
  there are many columns.
• Select the columns.
• Go to Format, select Freeze Columns.
• To turn the Freeze off, go to Format, Select
  Unfreeze Columns.

58
Maintain a Database

• Compact a database
• Helps run a database efficiently and takes up
  less storage space.
• In the database window
• Go to Tools, select Database Utilities and
  Compact and Repair.

59
Connections between Tables, Forms, Reports and
Queries

• New information entered in a table will be
  reflected from a form, a report, or a query
  generated from the table.
• New information entered in a form or a query will
  be reflected from the table that generates the
  form and the query and a report generated from
  the table.
• Use Address file for this practice
• Change office number and the phone extension for
  a record.
• View the tbl_office_info table to see the
  update.
• View a query or report to see the update.