Splash Screen

1
Splash Screen
INTRODUCTION TO MS ACCESS
Credits to Mr. Bright (0208285388) for the
preparation of this tutorial
2
Getting StartedLesson 1 Access BasicsLesson
2 Creating Forms, Queries, and ReportsLesson
3 Transforming Data Into Information
3
Objectives

• Explain the purpose of the Access program.

• Start and exit Microsoft Access.
• Name the main components of the Access window and
  display and hide toolbars.

• Scroll in a window to display elements that are
  hidden from view.

• Open, close, and reopen files.
• Get help from Accesss Office Assistant,
  ScreenTips, Ask a Question box, Answer Wizard,
  Contents, and Index.
• Identify buttons on the Web toolbar and get help
  on the Web.
• Connect to and disconnect from the Internet.

4
What Is Access?

• Access is a powerful database management program
  used to organize, track, and retrieve data.

• You can use forms to enter information into a
  database.
• You can create queries to extract information
  from a database.
• You can print professional-looking reports based
  on the information contained in a database.
• You can publish data stored in an Access database
  on the Web.
• You can purchase and use Access as a stand-alone
  application or as part of the Microsoft Office
  suite.

5
Starting the Access Program

• Before you can start Access, both Access and
  Windows must be installed on the computer you are
  using.

• Follow these steps to start Access
• Start your computer.
• Click the Start button on the Windows taskbar.
• Point to Programs.
• If necessary, click the double-arrow at the
  bottom of the menu to list all items on the
  Programs menu.
• Click Microsoft Access.

6
(No Transcript)
7
Exploring the Access Window

• The Access window contains many standard Windows
  elements.

• Title Bar
• The filename of the current file appears here,
  along with the name of the current application
  (in this case, Microsoft Access).
• Minimize, Restore Down, and Close buttons
• Menu bar
• Toolbars
• The taskbar is at the bottom of the screen.
• The taskbar includes the Start button, along with
  other buttons for navigating Windows.
• The Access menu bar appears below the title bar.

8
(No Transcript)
9
Working With Menus and Commands

• Menus list commands available in the application.

• Access provides more than one way to display the
  commands.
• Click a menu name on the menu bar.
• Press ALT on the keyboard plus the underlined
  letter in the menu name.
• When you open a menu, Access displays a short
  menu that lists the most basic commands.
• Arrows appear at the bottom of the menu.
• An expanded menu shows all of the commands
  available on the menu.

10
Working With Menus and Commands (cont.)

• There are several ways to make an expanded menu
  appear.

• Hold the pointer on the menu name.
• Point to the arrows at the bottom of the menu.
• Double-click the menu name.
• Menus can be personalized.
• After you use one of the commands from the
  expanded menu, Access adds that command to the
  short menu.
• Access automatically places the commands that you
  use most often on the short menu.

11
Working With Menus and Commands (cont.)

• Access provides more than one way to choose a
  command.

• Click the command on the menu.
• Press the underlined letter in the command name.
• An ellipsis () after a command indicates that
  more information is needed before the command can
  be executed.
• A dialog box will appear in which you may specify
  details about the command you are trying to use.
• Pointing to a command that has an arrow to the
  right of it displays a submenu.

12
(No Transcript)
13
Working With Toolbars and Buttons

• A toolbar contains buttons for frequently used
  commands.

• The icon on each toolbar button symbolizes the
  command.
• If you need help identifying a toolbar button,
  point to the button.
• A small text box, called a ScreenTip, appears.
• The ScreenTip identifies the name of the button.
• You can customize the toolbars.
• You can add buttons that you use frequently.
• You can remove buttons that you rarely use.
• You can show or hide toolbars depending on your
  needs.

14
Working With the Status Bar and Scroll Bars

• The status bar is the horizontal area that
  appears at the bottom of the application just
  above the taskbar.

• Information about a selected command or operation
  in progress is shown on the left side of the
  status bar.
• The right side of the status bar contains status
  indicators that turn special keys or modes on or
  off.
• You can use the scroll bars and scroll arrows to
  bring hidden portions of a window into view.
• Use the vertical scroll bar to move up and down
  in a window.
• If your mouse has a scroll wheel, you can use it
  to scroll vertically.
• Use the horizontal scroll bar to move from side
  to side.

15
Working With the Task Pane

• The task pane provides quick access to commonly
  used commands and features while you work with
  your file.

• Access includes three different task panes.
• The New File task pane appears by default.
• Click the Other Task Panes button to display a
  list of available task panes.
• Use the Back button and Forward button in the
  task pane to move to task panes you have recently
  accessed.

16
Working With the Task Pane (cont.)

• Task pane options that appear in colored text are
  hyperlinks.

• When you click a hyperlink, it activates a
  command or feature.
• You can resize or close the task pane to make
  more room for your work in the application window.

17
(No Transcript)
18
(No Transcript)
19
Working With Folders and Files

• You must open a file before you can use it.

• When you open a file, the file is copied from
  your disk to the computers memory.
• Access offers more than one way to open a file.
• Click the Open button on the Database toolbar.
• Click the Open command on the File menu.
• Click Files or More Files in the New File task
  pane.
• Use the Open dialog box to specify the drive,
  folder, and file you want to open.

20
(No Transcript)
21
Closing Files

• You should close a file when you are finished
  working with it.

• When you close a file, the file is removed from
  memory.
• You can keep several files open at one time, but
  its a good idea to close files that you arent
  using.
• This frees up memory for other tasks.
• When a file is open, two Close buttons are
  displayed on the screen.
• The Close button on the menu bar is used to close
  the file.
• The Close button on the title bar is used to
  close the application.
• When you want to close a file, make sure to use
  the correct Close button!

22
Reopening Files

• Access provides shortcuts that make it easy to
  reopen files that you recently used.

• A list of recently opened files appears in the
  Open a file area of the task pane.
• A similar list appears near the bottom of the
  File menu.
• In both cases, just click the file name to open
  the file.
• By default, Access lists the last four files you
  opened.
• You can change the default setting to meet your
  needs.

23
Getting Started 1-34
24
Getting Started 1-35
Getting Help With Access

• The Help system in Access is like an electronic
  reference manual.

• Several Help tools are available.
• Office Assistant
• ScreenTips
• Ask a Question box
• Answer Wizard
• Contents
• Index
• Much of the Help system is based on hyperlinks.
• In some cases, glossary terms are provided they
  appear as hyperlinks.

25
Getting Started 1-36
Using the Office Assistant

• The Office Assistant is an animated character
  that can answer specific questions, offer tips,
  and provide help.

• Sometimes, help or advice is provided even before
  you ask for it!
• To activate the Office Assistant, click one of
  the following screen elements or commands
• the animated character on your screen (if its
  visible)
• the Microsoft Access Help button on the Database
  toolbar
• the Microsoft Access Help command on the Help
  menu
• the Microsoft Access Help hyperlink in the New
  File task pane
• the Show the Office Assistant command on the Help
  menu

Press the Space Bar or click the mouse to display
the information.
26
Getting Started 1-37
Using ScreenTips

• Access includes ScreenTips text boxes that
  provide information about various program
  elements.

• To access the ScreenTips, click Whats This? on
  the Help menu.
• The pointer changes to a question mark pointer.
• Click an element on the screen a menu command,
  a toolbar button, or another element to see its
  description.
• Click anywhere on the screen to remove the
  ScreenTip.

Press the Space Bar or click the mouse to display
the information.
27
Getting Started 1-38
28
Getting Started 1-39
29
Getting Started 1-40
Using the Other Help Tools

• The Answer Wizard answers specific questions, but
  provides more topics than does the Office
  Assistant.

• Use the Contents to view a listing of general
  Help topics.
• This is useful if you dont know the name of a
  feature.
• If you want to search for specific words or
  phrases or choose from a list of keywords, use
  the Index.

Press the Space Bar or click the mouse to display
the information.
30
Getting Started 1-41
31
Getting Started 1-42
Exiting the Application Program

• When you are finished with the application
  program, you should exit the program properly.

• If you dont close the application, you could
  have problems the next time you want to start it.
• When you exit the program, two important
  activities take place.
• Open files are closed.
• The application is removed from the computers
  memory.
• Choose one of these methods to exit Access.
• Click the Close button in the title bar.
• Click the Exit command on the File menu.
• After you exit Access, you can shut down your
  computer.

Press the Space Bar or click the mouse to display
the information.
32
Getting Started 1-43
Self Check
1. Both toolbars and menus can be customized for
individual users. 2. To start Access, click the
Start button, point to Programs, and click
Microsoft Access. 3. The task pane is a window
that provides quick access to commonly used
commands and features. 4. The only way to access
the online Help system is through the Office
Assistant. 5. To exit Access, click the Close
Window button on the menu bar or click Close on
the File menu.
T
T
T
F
F
Press the Space Bar or click the mouse to display
the answers.
33
Getting Started 1-44
Press the Space Bar or click the mouse to display
the answers.
34
Section 1-1
Objectives

• Understand databases and how they work.

• Name the main features of the Database window.
• Identify the different types of objects an Access
  database can contain.
• Select an object using the Objects bar and switch
  between object views.
• Navigate a table and edit a record in a table.
• Enter a record using a form.

Press the Space Bar or click the mouse to display
the information.
35
Section 1-2
Objectives (cont.)

• Find specific records using a query.

• Preview and print a report.
• Plan a database.
• Create a database.
• Create tables using the Table Wizard or Design
  view.
• Understand data types.
• Set primary keys.
• Modify field properties.

36
Section 1-3
Objectives (cont.)

• Modify tables in Design view.

• Create an input mask.
• Enter records using a datasheet.
• Retrieve data from other sources.
• Navigate a datasheet and edit records.
• Define table relationships and understand
  referential integrity.
• Search the Internet from the Access program.

Press the Space Bar or click the mouse to display
the information.
37
Section 1-4
Introducing Microsoft Access

• Microsoft Access is a database management program
  that you can use to organize, track, and retrieve
  data in a database.

• A database is an organized collection of data
  from which specific information can be easily
  extracted.
• A database does not need to be stored on a
  computer.
• Some databases, like a telephone book, are stored
  on paper.

Press the Space Bar or click the mouse to display
the information.
38
Section 1-5
Introducing Microsoft Access (cont.)

• In an Access database, data is stored in tables.

• Each table contains data that pertains to one
  particular subject, such as employees or
  customers.
• Access is a relational database management system
  (RDBMS).
• In an RDBMS, a database may contain multiple
  tables that can be used together.
• You can establish relationships to connect the
  tables, provided the tables have one field in
  common.

Press the Space Bar or click the mouse to display
the information.
39
Section 1-6
40
Section 1-7
Understanding the Database Window

• The Database window allows you to gain access to
  all the data and objects stored in your database.

• The title bar displays the name and file format
  of the database.
• The toolbar provides shortcuts for working with
  objects.
• The Objects bar represents the types of objects
  available in Access.

Press the Space Bar or click the mouse to display
the information.
41
Section 1-8
42
Section 1-9
Objects The Components of Access

• In Access, the term database refers to the raw
  data as well as the collection of database
  objects.

• The objects provide the structure of an Access
  database.
• You use the objects to enter, manipulate, and
  extract data.
• Objects can be tailored to fit the data that will
  be stored in the database.
• Objects can be modified as the database grows and
  changes over time.

Press the Space Bar or click the mouse to display
the information.
43
Section 1-10
Views

• Access provides several different views for you
  to use as you work with the objects.

• The views vary depending on the type of object
  open and the kind of work you want to do.
• Some views let you work with the objects
  content, or data.
• Other views let you work with the objects
  design, or structure.

Press the Space Bar or click the mouse to display
the information.
44
Section 1-11
Views (cont.)

• The Datasheet view displays data in a
  column-and-row format, called a datasheet.

• Use the Datasheet view to add, delete, and edit
  the information in a database.
• In Design view, you work with the structure of an
  object, but not the data.
• Use the Design view to create new database
  objects and modify the design of existing objects.

Press the Space Bar or click the mouse to display
the information.
45
Section 1-12
Toolbars

• Access also provides different toolbars for you
  to use.

• The toolbars may seem similar, but each toolbar
  serves a specific purpose.
• The toolbars available vary depending on the
  object you are using and the view in which youre
  working.

Press the Space Bar or click the mouse to display
the information.
46
Section 1-13
Tables

• The table is the basic structure that holds all
  of the data in an Access database.

• Tables are made up of fields (columns) and
  records (rows).
• A field contains categories of data.
• A record contains the set of fields for one
  particular entity.

Press the Space Bar or click the mouse to display
the information.
47
Section 1-14
48
Section 1-15
49
Section 1-16
Queries

• A query is a question to the database.

• A query generally asks for a set of records from
  one or more tables that meets specific criteria.
• Queries let you view and manipulate selected
  subsets of your data.
• A query is a stored question rather than the
  stored answer to a question.
• This means that the results of the query will
  change if the underlying data in the database
  changes.

Press the Space Bar or click the mouse to display
the information.
50
Section 1-17
Queries (cont.)

• A Select query is used to retrieve data from one
  or more tables and display the results in a
  datasheet.

• A Parameter query prompts you for the information
  it needs to retrieve records.
• Use a Crosstab query to perform calculations on
  records and then group the results.
• An Action query is used to make changes to
  several records in just one operation.
• A SQL query is created from SQL statements that
  can be used with Access and other relational
  databases.

Press the Space Bar or click the mouse to display
the information.
51
Section 1-18
Queries (cont.)

• You can view a query using different views.

• Use Datasheet view to display the results of a
  query.
• Use Design view to display and modify the
  underlying structure of a query.
• Access also provides other query views.
• SQL view
• PivotTable view
• PivotChart view

Press the Space Bar or click the mouse to display
the information.
52
Section 1-19
53
Section 1-20
54
Section 1-21
Forms

• An Access form is the electronic equivalent of a
  paper form.

• You can use a form to create a custom layout for
  your data.
• Forms have three views.
• Use Design view to create a new form or change
  the structure of an existing form.
• Use either Datasheet view or Form view to view,
  enter, and edit data in forms.
• Datasheet view shows several records at one time,
  while Form view shows only one record at a time.

Press the Space Bar or click the mouse to display
the information.
55
Section 1-22
56
Section 1-23
Reports

• Access reports are based on data from tables or
  queries.

• Reports let you control the size and appearance
  of data in order to produce presentation-quality
  output.
• You can use three different views to work with
  reports.
• Use Design view to create a new report or change
  the structure of an existing report.
• Use Print Preview to display a WYSIWYG preview of
  all the data in a report.
• Use Layout Preview to view the layout of a
  report this view shows only a portion of the
  data contained in the report.

Press the Space Bar or click the mouse to display
the information.
57
Section 1-24
58
Section 1-25
Data Access Pages

• A data access page (sometimes called a page) is a
  Web page that is connected to an Access database.

• A data access page allows you to publish live
  data on the Web.
• You can work with data access pages in three
  views.
• Data is displayed in Page view by default when
  you open a data access page.
• Use Design view to change the structure of the
  page.
• Use Web Page Preview to see how the page will
  look when viewed through a Web browser.

Press the Space Bar or click the mouse to display
the information.
59
Section 1-26
60
Section 1-27
Macros and Modules

• A macro is a set of commands called actions that
  you can create to automate a task.

• Macros let you automatically perform a set of
  actions rather than issuing them separately.
• Macros are designed to save time and effort.
• Macros are best used for relatively simple tasks,
  such as opening forms or running reports.
• A module is a set of Microsoft Visual Basic
  program statements, declarations, and procedures
  that are stored together as a unit.
• Modules can be used for more complex tasks.

Press the Space Bar or click the mouse to display
the information.
61
Section 1-28
Planning Ahead

• Creating a database requires careful thought.

• Before beginning to build a database, you should
  answer several questions
• What is the database for and what should the
  database do?
• What categories of information (or fields) are
  needed to achieve the desired results?
• How should those fields be divided into separate
  tables?
• How might these tables relate to each other to
  use information from two or more of them
  simultaneously?

Press the Space Bar or click the mouse to display
the information.
62
Section 1-29
Determining the Purpose of the Database

• Before you can configure your database, you need
  to determine how it will be used.

• If one exists, investigate the manual system
  currently in use.
• Review reports and forms that youll need to
  duplicate, and note the items of information they
  must include.
• Talk to people who actually use the database to
  find out how they use the system and what they
  need.

Press the Space Bar or click the mouse to display
the information.
63
Section 1-30
Determining the Purpose of the Database (cont.)

• If a manual system does not exist, think
  carefully about what you want the system to do.

• Jot down the items of information the system
  should track.
• Sketch the forms and reports you think youll
  need.
• Consider how you want to sort and extract data.

Press the Space Bar or click the mouse to display
the information.
64
Section 1-31
Determining the Categories of Information

• Make a list of all of the categories of
  information the database should include.

• Make sure to break the categories down into their
  smallest parts.
• For example, instead of just one category for a
  customers name, include separate categories for
  first name and last name.
• The list does not need to be in any particular
  order.

Press the Space Bar or click the mouse to display
the information.
65
Section 1-32
Determining How Many Tables You Need

• Each table in an Access database should contain
  information on a single subject.

• If you try to include information on more than
  one subject in one table, you end up with
  duplicate data, or data redundancy.
• When you duplicate data, you do a lot more typing
  than necessary.
• You run the risk of more data entry errors.
• The duplicate data wastes storage space.
• When you use multiple tables in a database, you
  avoid data redundancy.

Press the Space Bar or click the mouse to display
the information.
66
Section 1-33
Determining How Tables Will Work Together

• In order to use two or more tables in
  combination, they must include a common field.

• A relationship is the association between common
  fields in two tables.
• The field that you use as a common field is known
  as a primary key.
• A primary key is a field or set of fields that
  uniquely identifies each record in a table.
• A field that holds information that is not
  unique, like a name field, shouldnt be used as
  the primary key.
• Instead, use a field that holds unique
  information about the record, like an ID field,
  as the primary key.

Press the Space Bar or click the mouse to display
the information.
67
Section 1-34
Creating a Database

• Access provides two ways to create a database.

• You can use the Database Wizard, which uses a
  template to help you create a database and its
  objects.
• You can create a blank database and then create
  each database object individually as you need it.
• Click New on the Database toolbar.
• Click Blank Database in the New File task pane.
• In the New Database dialog box, navigate to the
  drive and folder in which you want to save the
  database.
• Type a name in the File name text box.
• Click Create.

Press the Space Bar or click the mouse to display
the information.
68
Section 1-35
69
Section 1-36
Creating Tables

• To create a table, you must design its structure
  by identifying the fields in the table and
  defining the properties of those fields.

• Access provides three ways to create tables.
• You can use the Table Wizard to create the table
  from a template.
• You can use Design view to create the table from
  scratch.
• You can enter data into a blank datasheet.

Press the Space Bar or click the mouse to display
the information.
70
Section 1-37
Creating a Table With the Table Wizard

• When you use a wizard, Access displays a series
  of dialog boxes that prompts you for information.

• When you use the Table Wizard to create a table,
  the first step is to select the fields to be
  included.
• You can remove and rename fields later if you
  need to.
• You can also change the order of the fields.

Press the Space Bar or click the mouse to display
the information.
71
Section 1-38
72
Section 1-39
73
Section 1-40
Naming the Table and Selecting a Primary Key

• Once youve selected the fields for your table,
  you need to choose a name for the table.

• You also need to choose a field to act as the
  primary key.
• You complete both of these activities in the
  Table Wizard dialog box.
• Type a name for the table.
• Click the No, Ill set the primary key option.
• Click Next.
• Select the field to be used as the primary key.
• Click Next.

Press the Space Bar or click the mouse to display
the information.
74
Section 1-41
75
Section 1-42
76
Section 1-43
Creating Tables in Design View

• When you create tables in Design view, you have
  more flexibility over the field characteristics
  than when you use the Table Wizard.

• Double-click the Create table in Design view
  option in the Database window.
• Type the first field name and press Tab.
• Click the desired data type and press Tab.
• If desired, type a description for the field and
  press Tab.
• Repeat the steps for each field.
• You can also use Design view to modify existing
  tables, whether you created them in Design view
  or with the Table Wizard.

Press the Space Bar or click the mouse to display
the information.
77
Section 1-44
78
Section 1-45
Setting Field Properties

• After you assign a data type to a field, Access
  automatically sets default field properties for
  the field.

• The field properties control the way the field
  looks and behaves.
• You can modify the field property settings to fit
  the data your database will contain.
• Click the name of the field you wish to change in
  the top pane of the Design view window.
• Press F6 to jump to the Field Properties pane.
• Click the property box that you wish to change.
• Type or click a new property.

Press the Space Bar or click the mouse to display
the information.
79
Section 1-46
80
Section 1-47
Choosing a Primary Key

• After you create a table, you need to set a
  primary key for the table.

• In Design view, click the row selector (the small
  box to the left of a field) of the field you wish
  to designate as the primary key.
• Click the Primary Key button on the Table Design
  toolbar.

Press the Space Bar or click the mouse to display
the information.
81
Section 1-48
82
Section 1-49
Saving a Table

• When you create tables with the Table Wizard,
  Access automatically saves them.

• When you create or modify tables in Design view,
  you need to tell Access to save the table.
• To save a table, click the Save button or click
  Save on the File menu.
• The first time you save a table, Access requests
  a table name.
• The next time you save the table, Access uses the
  name you initially provided.
• You can use the Save As command on the File menu
  to save a copy of the table under a different
  name.

Press the Space Bar or click the mouse to display
the information.
83
Section 1-50
84
Section 1-51
Modifying Tables in Design View

• At some point, you may need to make changes to
  the structure of your table.

• You may need to add or delete fields.
• You might decide to change the fields name or
  data type.
• You may decide to reorder the fields.
• You can use Design view to change the structure
  of your table.

Press the Space Bar or click the mouse to display
the information.
85
Section 1-52
86
Section 1-53
87
Section 1-54
Creating an Input Mask

• Access provides several ways to help you control
  how information is entered in a field.

• You can control data entry by specifying a data
  type or field size for a field
• You can also use an input mask to control data
  entry.
• An input mask is a pattern that you create that
  specifies what kind of data to enter.
• It also specifies the number of characters
  allowed in a field.
• Data entry is easier, because the input mask
  shows you exactly how to enter the data.
• You can use the Input Mask Wizard to create an
  input mask.

Press the Space Bar or click the mouse to display
the information.
88
Section 1-55
89
Section 1-56
Entering Records Using a Datasheet

• When you are entering a large number of records,
  a form is usually the best object to use.

• For smaller jobs, you can use the datasheet to
  add, delete, or make minor modifications to
  records.
• Its easy to use the datasheet to add records.
• Type data in a field.
• Press Tab to move between fields.
• Press Tab after the last field of a record to
  create a new record, or press the New Record
  button to move to a new record.

Press the Space Bar or click the mouse to display
the information.
90
Section 1-57
91
Section 1-58
92
Section 1-59
Retrieving Data From Other Sources

• You can populate your database with data from
  other sources to avoid rekeying large amounts of
  data.

• You can copy records from another database and
  append them to a table in your database.
• For this to work, the order of the fields in the
  two databases must be the same.
• You can copy data to the Office Clipboard and
  then append it to a table in your database.
• The Office Clipboard is a temporary place to
  store items that you copy from other databases or
  even other applications.

Press the Space Bar or click the mouse to display
the information.
93
Section 1-60
94
Section 1-61
Retrieving Data From Other Sources (cont.)

• You can import data from another database or
  application.

• When you import data, you copy an entire object
  into your database.
• This means that you copy both the records (data)
  and the structure (design) of the object.
• After you import the data, you may find that you
  need to modify the table design.

Press the Space Bar or click the mouse to display
the information.
95
Section 1-62
96
Section 1-63
Navigating the Datasheet

• When you have only a few records in your
  database, you can always move to another record
  just by clicking.

• When you have larger amounts of data, you need a
  more efficient way to move among the records.
• Access provides keyboard and mouse techniques for
  navigating the datasheet.

Press the Space Bar or click the mouse to display
the information.
97
Section 1-64
98
Section 1-65
Editing the Records

• Once youve found the record you need, you can
  edit it.

• You can delete or replace the contents of a
  particular field.
• You can add to a field.
• You can change the contents of a field.
• You can delete one or more entire records.
• If you need to reverse an edit, you can use the
  Undo command.

Press the Space Bar or click the mouse to display
the information.
99
Section 1-66
100
Section 1-67
Defining Relationships Between Tables

• After you have created some tables in your
  database, you should establish relationships
  between them.

• Table relationships allow you to create queries
  and reports that include data from more than one
  table.
• After youve established the relationships, you
  can print the information for your reference.

Press the Space Bar or click the mouse to display
the information.
101
Section 1-68
Relationship Types

• Any two tables can have one of three types of
  relationships.

• In a one-to-one relationship, every record in one
  table can have one matching record in the other
  table.
• In a one-to-many relationship, every record in
  the primary table can have many matching records
  in the related table.
• Each record in the related table has just one
  associated record in the primary table.
• The related table links to the primary table
  through a foreign key.
• In a many-to-many relationship, a record in
  either table can relate to many records in
  another table.

Press the Space Bar or click the mouse to display
the information.
102
Section 1-69
Referential Integrity

• Access uses referential integrity to safeguard
  your data.

• Referential integrity refers to rules that Access
  enforces to make sure you dont accidentally
  delete or modify data that is related to another
  table.
• For example, when referential integrity is
  enforced, you cant delete a record in a primary
  table if there are matching records in the
  related table.

Press the Space Bar or click the mouse to display
the information.
103
Section 1-70
104
Section 1-71
105
Section 1-72
Displaying Related Records in a Subdatasheet

• A subdatasheet allows you to view and edit data
  in a related table, query, or form.

• To view a subdatasheet, click the plus sign in
  front of any record in a table.
• Click the minus sign in front of any record to
  close a subdatasheet.

Press the Space Bar or click the mouse to display
the information.
106
Section 1-73
107
Section 1-74
Self Check

• 1. In an Access database, information is
  organized in a series of columns called
  __________ that contain categories of data and
  rows called __________ that contain all the
  categories for one particular entity.
• 2. __________ are the various components of
  Access that you use to enter, display, print, and
  manipulate the information in a database.
• 3. To create or modify the structure of a table,
  you must use __________ view.
• 4. A ____________ is a field or set of fields
  that uniquely identifies each record in a table.
• To ensure that you dont accidentally modify or
  delete data that is related to another table,
  Access enforces __________________ to safeguard
  your data.

fields
records
Objects
Design
primary key
referential integrity
Press the Space Bar or click the mouse to display
the answers.
108
Section 1-75
Press the Space Bar or click the mouse to display
the answers.
109
Section 1-76
Sample Solution Skills Review 4
110
Section 1-77
Sample Solution Lesson Application 12
111
Section 1-78
Sample Solution Project 2
112
Section 2-1
Objectives

• Create an AutoForm.

• Add and delete records using forms and subforms.
• Use the Form Wizard to create a form based on
  multiple tables.
• Create a query using the Simple Query Wizard.
• Sort records in a query.
• Specify criteria in a query.
• Add, delete, and move fields in a query.
• Edit the data in the results of a query.

Press the Space Bar or click the mouse to display
the information.
113
Section 2-2
Objectives (cont.)

• Create and modify a multi-table query.

• Use the Expression Builder to create calculated
  fields in a query.
• Use aggregate functions and grouping in a query.
• Format and print a datasheet.
• Create various types of AutoReports.
• Use the Report Wizard to create a report based on
  a table and a query.

Press the Space Bar or click the mouse to display
the information.
114
Section 2-3
Objectives (cont.)

• Group and sort records in a report and include
  summary information.

• Preview and print reports.
• Use Microsoft Access Help to understand spacing
  and alignment of controls on forms and reports.
• Create a hyperlink to another object in a
  database and to a Web site.

Press the Space Bar or click the mouse to display
the information.
115
Section 2-4
Creating and Using Simple Forms

• Forms provide an efficient, user-friendly
  interface for data entry.

• You can build forms that display one record at a
  time.
• You can also build forms that reveal only
  selected fields from one or more tables.
• The quickest way to create a basic form is by
  using the AutoForm command.
• You can also use the Form Wizard to create a
  form.
• For maximum control over what a form contains and
  how it appears, use Design view to create the
  form.

Press the Space Bar or click the mouse to display
the information.
116
Section 2-5
117
Section 2-6
118
Section 2-7
119
Section 2-8
Designing Basic Queries

• A query allows you to get useful information out
  of your database quickly and reliably.

• You can use queries to sort data and to extract
  data that meets criteria you specify.
• You can also use queries to perform calculations
  and to work with data from multiple tables.
• Query results can be used in forms and reports.
• You can save queries for future use.

Press the Space Bar or click the mouse to display
the information.
120
Section 2-9
Designing Basic Queries (cont.)

• Access provides more than one way to create a
  query.

• You can use the Simple Query Wizard to quickly
  create a basic query.
• To create more complex queries, or to modify an
  existing query, use the Design view.

Press the Space Bar or click the mouse to display
the information.
121
Section 2-10
Using the Simple Query Wizard

• Click Queries in the Objects bar of the Database
  window.

• Double-click Create query by using wizard.
• Choose the table(s) you wish to use in the
  Tables/Queries text box.
• Double-click the available fields you wish to add
  to your query and click Next.
• Choose Detail or Summary and click Next.
• Enter a name for your query.
• Click Finish.

Press the Space Bar or click the mouse to display
the information.
122
Section 2-11
123
Section 2-12
Design View

• You can use Design view to create or modify
  queries.

• The Design view is divided into two panes.
• The upper pane contains the Field list from the
  table you are using to build the query.
• If you are using more than one table, a field
  list will appear for each table.
• The lower pane contains a design grid.
• Each column in the design grid contains
  information about one field in the query.

Press the Space Bar or click the mouse to display
the information.
124
Section 2-13
Design View (cont.)

• Use the design grid to specify the details of the
  query.

• The Field row contains the names of the fields in
  the query.
• The Table row contains the name(s) of the
  table(s) where the fields are located.
• Use the Sort row to tell Access how to sort the
  query results.
• Use the Show check box to specify whether or not
  the field will be displayed in the query results.
• Use the Criteria and Or rows to specify
  conditions that limit the scope of the query.

Press the Space Bar or click the mouse to display
the information.
125
Section 2-14
126
Section 2-15
Querying Multiple Tables

• You can create queries that extract data from
  multiple tables to put information together in
  new ways.

• When you include more than one table in a query,
  you must establish a join between the tables.
• When you establish a join between tables, you
  tell Access how the data in one table is related
  to the data in the other table.
• You can use either a wizard or Design view to
  create a multi-table query.
• Use the Query Properties dialog box in Design
  view to specify special properties of the query,
  such as whether or not the query should show
  duplicate values.

Press the Space Bar or click the mouse to display
the information.
127
Section 2-16
128
Section 2-17
129
Section 2-18
Building Complex Queries

• Access lets you easily build complex queries to
  extract specific information from your database.

• Complex queries may include expressions,
  calculated fields, and statistical functions.
• The criteria specified in a query can range from
  simple text or numeric data to complex
  calculations and expressions.

Press the Space Bar or click the mouse to display
the information.
130
Section 2-19
Using Expressions in a Query

• You can use expressions in your queries to
  extract information that falls between a range of
  values.

• An expression is a combination of field names,
  values, and operators that Access can evaluate.
• The comparison operator is an essential part of
  most expressions.
• A comparison operator is a symbol that is used to
  compare a value or text in the database to
  characters that you enter.

Press the Space Bar or click the mouse to display
the information.
131
Section 2-20
Using Expressions in a Query (cont.)
132
Section 2-21
Using Expressions in a Query (cont.)

• You can also use AND or OR in an expression.

• In an AND expression, all conditions in the
  expression must be met in order for the
  expression to be true.
• When you create an AND expression for a single
  field, you must place the entire expression on
  the same line in the Criteria row.
• When you enter values in the Criteria row for
  multiple fields, Access treats those criteria as
  an AND expression.

Press the Space Bar or click the mouse to display
the information.
133
Section 2-22
Using Expressions in a Query (cont.)

• In an OR expression, just one of the conditions
  in the expression must be met for the condition
  to be true.

• When you create an OR expression for a single
  field, you can place the entire expression in the
  Criteria row.
• You can also place each OR condition in a
  separate or row.
• To create an OR expression that involves multiple
  fields, enter one value in the Criteria row of
  one field and another value in the or row in
  another field.

Press the Space Bar or click the mouse to display
the information.
134
Section 2-23
Using Calculated Fields in a Query

• You should not include fields in your database
  that contain data you can calculate from other
  fields.

• Instead, use the values already entered in the
  database to create a calculated field.
• You can type the calculation in the field, or you
  can use the Expression Builder to create the
  calculation.

Press the Space Bar or click the mouse to display
the information.
135
Section 2-24
136
Section 2-25
Using Aggregate Functions

• You can use predefined calculations, called
  aggregate functions, to calculate totals and
  perform other computations.

• Access provides several different aggregate
  functions.
• AVG (Average)
• COUNT
• MAX (largest value)
• MIN (smallest value)
• SUM
• STDEV (standard deviation)
• VAR (variance)

Press the Space Bar or click the mouse to display
the information.
137
Section 2-26
138
Section 2-27
Grouping

• You can group certain records together and then
  use an aggregate function to calculate a total or
  average for the group.

• Use Group By in the Total row to indicate the
  fields on which you want to group records.
• Note that Group By doesnt perform any
  calculations.
• Instead, you use Group By to define a grouping of
  data on which you want to perform an aggregate
  function.

Press the Space Bar or click the mouse to display
the information.
139
Section 2-28
140
Section 2-29
Formatting and Printing a Datasheet

• The datasheet contains useful information, but it
  is not very attractive.

• Large datasheets can be difficult to navigate.
• You can format datasheets to make them more
  attractive and easier to navigate.
• You can hide or freeze columns.
• You can change the column widths to make the data
  easier to read.
• You can change the typeface, type size, and
  background colors for the datasheet.
• You can also print a datasheet if you dont want
  to take the time to create a report.

Press the Space Bar or click the mouse to display
the information.
141
Section 2-30
142
Section 2-31
Creating Simple Reports

• Reports provide a polished, professional-looking
  way to present the information in your database.

• An AutoReport can be generated by Access with
  little input from you, and is based on a single
  table or query.
• If you need to create a report based on more than
  one table or query, you can use the Report
  Wizard.
• If you need even more control over the layout and
  content of the report, use Design view to create
  the report.
• You can also use Design view to modify a report
  that was created as an AutoReport or by using the
  Report Wizard.

Press the Space Bar or click the mouse to display
the information.
143
Section 2-32
Creating Simple Reports (cont.)

• You can include a variety of report elements to
  make your reports easier to read and follow.

• A report header appears at the top of the first
  page of the report.
• A page header is printed at the top of every page
  of the report.
• A page footer appears at the bottom of every page
  of the report.
• You can show your data in either a columnar or
  tabular format.
• In a columnar report, each field in a record is
  printed on a separate line, with its label
  immediately to its left.
• In a tabular report, the labels are printed
  across the top of the page, with the
  corresponding fields below.

Press the Space Bar or click the mouse to display
the information.
144
Section 2-33
145
Section 2-34
Creating Simple Reports (cont.)

• After you have created a report, preview it to
  make sure it has the proper appearance.

• If necessary, you can use Design view to modify
  the layout or content of the report.
• When everything looks the way you want it to
  look, click Print on the File menu to print the
  report.

Press the Space Bar or click the mouse to display
the information.
146
Section 2-35
147
Section 2-36
Self Check

• 1. A(n) __________ is a form within a main form
  that contains fields from a subdatasheet.
• 2. In the query design grid, you use the
  _________ or ______ rows to specify conditions
  that limit the scope of a query.
• 3. When you include more than one table in a
  query, you must establish a(n) ________ between
  the tables so Access knows how the data in one
  table is related to the data in the other table.
• 4. A(n) ___________ is a combination of field
  names, values, and comparison operators that
  Access can evaluate.
• When you use the ______________, you can create a
  report that groups and sorts records, and
  includes summary information.

subform
Criteria
Or
join
expression
Report Wizard
Press the Space Bar or click the mouse to display
the answers.
148
Section 2-37
Press the Space Bar or click the mouse to display
the answers.
149
Section 2-38
Sample Solution Skills Review 10
150
Section 2-39
Sample Solution Lesson Application 1
151
Section 2-40
Sample Solution Project 2
152
Section 3-1
Objectives

• Sort records in a datasheet.

• Apply filters.
• Validate data.
• Create a lookup field.
• Export data from Access.
• Use query wizards.
• Customize forms and reports in Design view.
• Create a database using the Database Wizard.
• Create a data access page using the Page Wizard.

Press the Space Bar or click the mouse to display
the information.
153
Section 3-2
Sorting Records in a Datasheet

• You can use the Sort Ascending and Sort
  Descending commands to perform simple sorts in a
  datasheet.

• With a simple sort, you can sort records in
  either ascending or descending order on one or
  more fields.
• You cannot, however, sort records in both
  ascending and descending order on more than one
  field.
• You can sort data on any field in a datasheet
  except an OLE Object field.

Press the Space Bar or click the mouse to display
the information.
154
Section 3-3
Sorting Records in a Datasheet (cont.)

• Access sorts Text fields into alphabetic order.

• An ascending sort arranges the data from A to Z.
• A descending sort arranges the data from Z to A.
• Access sorts Numeric and Currency fields into
  numeric order.
• An ascending sort arranges the data from lowest
  to highest.
• A descending sort arranges the data from highest
  to lowest.
• Access sorts Date/Time fields into chronological
  order.
• An ascending sort places the earliest dates
  first.
• A descending sort places the most recent dates
  first.

Press the Space Bar or click the mouse to display
the information.
155
Section 3-4
156
Section 3-5
Filtering Data

• You can use a filter to display only the specific
  records that meet your criteria.

• A filter allows you to screen the records in a
  datasheet in order to more easily locate or
  extract information.
• A filter does not remove any records from the
  datasheet.
• A filter simply filters out the records you dont
  want to view and displays only those records you
  do want to view.

Press the Space Bar or click the mouse to display
the information.
157
Section 3-6
Filtering Data (cont.)

• A filter is very similar to a select query, but
  there are some key differences between the two.

• Filters only work on the data in a single table,
  but queries can manipulate data from multiple
  tables.
• When you use a filter, you cant choose which
  fields to display in the results.
• You cant include calculations in a filter.
• You can save a filter, but it wont be saved as a
  separate object in the database.
• Use a filter when you want to temporarily
  manipulate or rearrange a datasheet.
• If you find yourself filtering the datasheet for
  the same information over and over again, create
  a query instead.

Press the Space Bar or click the mouse to display
the information.
158
Section 3-7
Filtering Data (cont.)
159
Section 3-8
Filtering Data (cont.)

• You can use a wildcard character in filters when
  youre not sure of the spelling of the data.

• A wildcard character can be used to represent one
  or more unknown characters.
• Two common wildcard characters are the asterisk
  () and the question mark (?).
• The asterisk can represent any number of
  characters.
• Use the question mark to represent one specific
  character.

Press the Space Bar or click the mouse to display
the information.
160
Section 3-9
161
Section 3-10
162
Section 3-11
163
Section 3-12
164
Section 3-13
165
Section 3-14
Validating Data

• Access offers various ways to ensure that data
  entered into a database conforms to certain
  standards.

• When you assign a data type to a field, you place
  a general restriction on the type of data that
  can entered.
• An input mask further restricts the data that can
  be entered in a field.
• Another way to ensure that data conforms to
  certain standards is to establish validation
  rules.
• A validation rule is a property that narrowly
  defines the data that can be entered into a field
  or saved in a record.
• If you establish a validation rule to a table
  that already contains data, you can apply the
  rule to the existing data.

Press the Space Bar or click the mouse to display
the information.
166
Section 3-15
Validating Data (cont.)

• A field validation rule defines the data that
  will be accepted into a single field.

• For example, you could define gt0 as the
  validation rule for the Quantity field in an
  Orders table.
• If you enter a zero or any number less than zero
  in the field, the data will be rejected.

Press the Space Bar or click the mouse to display
the information.
167
Section 3-16
Validating Data (cont.)

• A record validation rule is used to determine
  whether an entire record should be saved.

• You could define the validation rule
  HireDategtBirthDate in an Employees table.
• If the date in the HireDate field is not greater
  than the date in the BirthDate field, the record
  will not be saved.

Press the Space Bar or click the mouse to display
the information.
168
Section 3-17
169
Section 3-18
Creating Lookup Fields

• When you are entering information into a table
  (or form), you may need to look up information
  from another table.

• You can create a Lookup field to look up
  information in another table.
• The resulting information is displayed in a
  Lookup list.
• You can also create a Lookup field to display a
  Value list instead of a Lookup list.
• Instead of containing values from a table, a
  Value list contains a fixed set of values that
  you create and store.
• Access provides a Lookup Wizard to guide you
  through the process of creating Lookup fields.

Press the Space Bar or click the mouse to display
the information.
170
Section 3-19
171
Section 3-20
172
Section 3-21
Exporting Data From Access

• You can export data from one Access database and
  use it in another Access database.

• You can also use the exported data in another
  Office application, in a non-Office application,
  or on a Web page.
• Its easy to export an Access object.
• Open the database that contains the object you