Access

1
Access

• Jianhua Yang
• Department of Math and Computer Science
• Bennett College

2
Goals

• Know what Access is for.
• Create a database and a table
• How to create and use a form
• Operation on a database
• Work with queries

3
1. Introduction to Access

• Access and database
• Database Management System
• The database window
• Database, Table, Filed

4
What is Access?

• Access is the database management system in
  Microsoft office.
• It provides ways to store, search, sort, query,
  and report on the data in the database.

5
What is Database?

• A database is an organized collection of related
  data.
• To maintain simple databases kept by hand
  requires time and labor.

6
Access database

• It is the electronic equivalent of a manual
  database.
• It allows you organize and maintain the data
  electronically.

7
What is a Database Management System?

• It provides you the ways to operate on database.

8
How is a Database Organized?

• The access uses the database window to maintain
  database objects in one file.
• The database window contains an objects bar with
  a button for each type of object.
• In access, data is organized as tables.

9
Database Objects

• Database Objects are the tools you need to store,
  maintain, search, analyze, and report on the data
  in your database.
• We have
• Table
• Query
• Form
• Report

10
Table

• It is also called datasheet.
• Each row in a table represents one record in a
  database.
• With Table button, you can create a new table, or
  modify an existing table.

11
Query

• It is a structured way to tell Access to retrieve
  data that meets certain criteria from one or more
  database tables.
• The Query button can
• Display a list of all queries in the current
  database
• Open an existing query, and modify its design
• Create a new query.

12
Form

• It is a formatted data entry window that lets you
  enter, display, and edit data.
• The Forms button can
• Displays a list of all forms in the current
  database
• Open existing form, and modify its design
• Or create a new form.

13
Report

• It is a formatted way to display information
  retrieved from the database.
• The Report button can
• Display a list of all reports in the current
  database
• Preview the report, modify its design
• Or create a new report.

14
How is a Table structured?

• A table is a grid of rows and columns.
• Each row contains one record.
• Each record is broken into some fields.
• Each field has its name and contents.
• Each field has its data type.

15
How are Access Tables Related?

• Different tables are related by one or more
  common fields.

16
Guidelines to design a database

• Gather all your sources of information.
• Determine what types of information you will need
  to locate or report.
• Decide how much flexibility you need in locating
  and reporting the data.
• Write down everything you want to accomplish with
  your database.
• Break down information into its smallest
  practical units.

17
Exercise

• Show students the above objects.

18
2. Explore a Database

• Start Access and Open a database
• Explore a database
• Print a Database Object

19
Start Access and Open a database

• Show the students how to do.

20
Explore a Database

• Using the sample databases Northwind to show
  the students.

21
Print a Database Object

• Show the students how to do it.

22
3. Getting Started with Access

• Open an Existing Database
• Enter Data

23
1) Open Existing Database

• Using file 02Goodstuff to show the students how
  to open an existing database. (P369)

24
2) Enter Data(P369-371)

• Enter data for a table
• Enter invalid data

25
3) Exercise

• Do the exercise at Page 373.

26
4. Work with Table Design

• Modify a table design
• Change Column widths
• Summary of data entry aids

27
1) Modify a table design

• With datasheet view we can enter data with
  design view we can modify table structure.
• In design view, we can control
• 1. field names
• 2. type of data
• 3. acceptable entries

28
Exercise

• Page 375

29
2) Make Required Fields

• When a filed is required, Access does not allow
  the field to remain empty during data entry.
• Page 376

30
3) Create a Lookup list

• How to create a lookup list.
• Page 376

31
4) Summary of Data entry aids

• AutoNumber Data type
• Status Bar Messages
• Formats
• Input Mask
• Validation Rules and Validation Text
• Default Values
• Required Fields

32
5. Create a database and table

• Create a database
• Set file format option
• Create a table using table wizard
• Modify the table design

33
1) Create a Database

• Start a new database
• It will contain objects
• Tables
• Queries
• Forms
• Reports
• Show it (P383)

34
2)Set file format option

• Default format is Access 2000.
• You can change it to Access 2002-2003.
• Show the students how to do it (p384).

35
3) Convert database format

• Convert format 2000 to 2003.
• Can you convert format 2003 to 2000?
• Show the students how to do it (p385).

36
4) Create a table using the table wizard

• Create a table with fields names, addresses, and
  home telephone number.
• Select fields for the table
• Rename fields
• Name the table and set a primary key
• Show the students how to do (p385-387)

37
5) Modify the table design

• Show the students how to do it (p389).

38
6) Exercise

• Do the exercise at P390.

39
6. Create and use a Form

• 1) Create a Form using AutoForm
• 2) Add a record in Form View
• 3) Modify a Form.

40
1) Create a Form using AutoForm

• What is a Form of Access
• Another data entry aid
• Can be used to enter, display, and edit data
• If you enter data in a Form, you are also adding
  it to the table
• The table shows all data at once the form shows
  data one record at a time.

41
Exercise

• Show the students how to create a form by
  following the book at P392.

42
2) Add a record in Form View

• Add a record in a form view
• Verify if the record has been added to the table.

43
3) Modify a Form

• Modify the forms title bar
• Modify the forms background
• Delete a field from a form
• Reposition fields on a form.

44
Modify the forms title bar

• Using forms property to do it.
• Show the students how to do (P394).

45
Modify the forms background

• By using the design view button
• Show the students (P394-395).

46
Delete a Field from a form

• At design view, select a field, and to delete it
  by pressing delete key.
• It does not affect the table fields.
• Show the students (p395).

47
Reposition fields on a Form

• Rearrange the form appearance.
• Change window size
• Change each field size
• Change font size, color, and
• Show the students how to do (P395-396)

48
Exercise P397-398

• Do the exercise together with the students.

49
7. Filter and Sort a Database

• 1) Filter by Selection
• 2) Filter by Form
• 3) Advanced Filter/Sort.

50
1) Filter by Selection

• A filter lets you select records in a table or
  form based on criteria you specify.
• Show how to set filter
• Show how to remove a filter.

51
2) Filter by Form

• Using the function Filter by form
• Entering the criteria you want to use to find
  matching records.
• Show the students how to do.

52
3) Advanced filter (optional)

• Show the students how to do (P401-P403).

53
8. Work with Queries

• 1) Add Records in form view
• 2) Create a Query
• 3) Copy a Query
• 4) Edit a Query.

54
1) Add records in Form View

• Add two records in Form View as the book does.
  (P405)

55
2) Create a Query

• A Query lets you retrieve data from one or more
  tables and display it in a new table.
• Start a new query
• Select tables for the Query
• Complete the Query.
• Example (P406-407).

56
3) Copy a Query

• Very similar to copy a file.
• Show the students how to do.

57
4) Edit a Query

• We can edit the Query copied.
• Show the students how to do.

58
Exercise

• Do the exercise at Page 411.

59
9. Work with Reports

• 1) Create a Report using AutoReport
• 2) Create a Report using the Report Wizzard
• 3) Change the Report title
• 4) Resize and Move Fields.

60
1) Create a Report

• Using AutoReport
• Using the Report Wizzard

61
Report

• Access Reports offer a way to use the information
  in tables and queries and format it for printing.

62
Using AutoReport

• Show the students how to create a Report.(P414)

63
Using the Report Wizard

• Show the students how to do it.

64
Change the report title.

• Go to Report design first
• And then change it, and the fields.
• Resize, reposition.

65
Exercise

• At pages 422-424.

66
Summary

• Using Access to process data
• Creating database, table
• Creating and using a Form
• Using queries
• Outputting reports