|
Ch. 1
|
Data Base Essentials
|
|
Access Data Base:
|
A tool for collection and organizing information also
called DBMS
|
|
DBMS
|
Data Base Management System as it enables to organize
large amount of complex data into related format.
|
|
Backstage View:
|
It appears when one starts MS Access.
|
|
Navigation Pane:
|
It appears on the left side of the Access screen listing
all the objects:
Including Tables, Queries, Forms and Reports.
|
|
Tables:
|
The most basic database object that stores data in
categories.
|
|
Queries:
|
It allows you to search and retrieve the data you have
stored.
|
|
Forms:
|
It controls data entry and provide easy visual data view.
|
|
Reports:
|
It presents information in a most useful ways.
|
|
Data Sheet:
|
It is a visual representation of a data contained in a
table or query.
|
|
Viewing Objects:
|
Each database object can be viewed in several different
ways, e.g. Data Sheet View, SQL View, Pivot Table view and Design View:
When you change the view, the commands on the Ribbon
change too so that one can perform the task via: Tab Groups, Quick Access
Toolbar and Dialog Box Launcher.
|
|
Record:
|
Each row in a table is called Record.
|
|
Field:
|
Each column in a table is called field.
|
|
Primary Key:
|
It is a column that uniquely identifies each row e.g.
Student Number.
|
|
Data Type:
|
It controls the type of data a field will contain i.e.
1.
Text – up to 255 characters of text
2.
Memo – up to 64000 character of text
3.
Number
4.
Date
5.
Currency
6.
AutoNumber
7.
Yes/No
8.
OLE Object -- photo
9.
Hyperlink
10. Attachment
11. Calculated
– combined two or more fields within the same table
|
|
Rational Data base
|
It stores information in a separate tables that are linked
by a defined relationship.
|
|
Duplicate Information
|
Redundant data
|
|
Normalization:
|
It is a process of applying rules to divide information
into appropriate tables
|
|
Guess What:
|
A row in a database table …. (Record)
|
|
Guess What:
|
A column in a database table… (Field)
|
|
Guess What:
|
Kind of information a field contains … (Data Type)
|
|
Guess What:
|
Most basic database Object, stores data in
categories…(Table)
|
|
Guess What:
|
Database Object that simplifies the process of entering,
editing, and displaying data… (Form)
|
|
Guess What:
|
Database Object that enables stored data to be searched
and retrieved… (Query)
|
|
Guess What:
|
Database Object that presents information in a format that
is easy to read and print… (Report)
|
|
Guess What:
|
A tool for collecting and organizing information… (Data
Base)
|
|
Guess What:
|
A column in a database that uniquely identifies each row(Primary
Key)
|
|
Guess What:
|
Duplicate information in a data…(Redundant Data)
|
|
|
|
|
True or False:
|
Any list you make for a specific purpose can be considered
a simple database, even a grocery list, phone list, business list etc…
|
|
True or False:
|
Forms, queries, and reports are examples of database
objects.
|
|
True or False:
|
The connection Status menu lets you choose between the
help topics that are available online and the help topics installed in your
computer offline.
|
|
True or False:
|
In a database table, data is stored in rows and columns –
similar in appearance to a spreadsheet.
|
|
True or False:
|
Each field in a table must be designated for a particular
data type.
|
|
True or False:
|
Normalization is the process of applying rules to your
database design to ensure that you have divided your information items into
the appropriate.
|
|
True or False:
|
By default, the Navigation Pane appears on the left side
of the Access Screen each time you create or open a database.
|
|
True or False:
|
The dialog box launcher contains the command that used
most often, such as Save, Undo, and Redo. (Appropriate to Tab Group)
|
|
True or False:
|
When you press the Shift key, small letters and numbers
called Key Tips appear on the Ribbon. (Alt key)
|
|
True or False:
|
An important principle to consider when planning a
database is to try to record each piece of information as many times as
possible for easy access. (Duplicate is Redundant ; not allowed)
|
|
OPEN Access:
|
RIBBON
|
|
File Tab:
|
Open, Recent, New, Help, Option and Exit on Left Pane
Templates available on the Centre Pane
File Name, folder and Create on the right pane.
|
|
Home Tab:
|
Groups: Views, Clipboard, Sort & Filter, Records, Find, Window and Text Formatting
|
|
Create Tab:
|
Groups: Application Parts Templates, Tables, Queries, Forms, Reports and Macros &
Code
|
|
External Data:
|
Import & Link, Export and Collect Data
|
|
Database Tools:
|
Tools, Macro, Relationships, Analyze, Move Data, Add-ins
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ch. 2
|
Data Base Essentials, Application
Parts, Quick Start and Templates
|
|
Application Parts Gallery:
|
Easy to create a new table by using Application Parts
gallery and Quick Start category of templates that contains a collection of
predefined objects arranged by parts of tracking things.
|
|
|
Search database files in the Search Office.com for
practice purpose:
Nutrition: Save as Nutrition A
North wind: Save it as North wind A
South ridge: Save it as South ridge A
|
|
True or False:
|
You can create a database using one of the many templates
available or by creating a new blank database.
|
|
True or False:
|
By default, Access creates a Primary Key field names ‘ID’
for all new datasheets.
|
|
True or False:
|
Entering data in Datasheet view is very similar to
entering data in an Excel worksheet.
|
|
True or False:
|
The Application Part Gallery contains predefined templates
included in two categories, Blank Forms and Quick Start.
|
|
True or False:
|
One way to create a table is to copy the Structure of an
existing table and past it into the database in Navigation Pane.
|
|
True or False:
|
When you add a new table to an existing database, that new
table stands alone until you relate it to your existing tables.
|
|
True or False:
|
You can use up to 64 Characters (letters or numbers),
including spaces, to name a database object.
|
|
True or False:
|
Separate options for creating a database are provided on
the New tab in the Backstage view.
|
|
True or False:
|
The quick start category in the Application Parts gallery
contains a collection of predefined database objects for tracking comments,
contacts, and issues.
|
|
True or False:
|
After you add fields to a table, you should save its
design.
|
|
True or False:
|
In MS Access, a temple is a ready-to-use database.
|
|
True or False:
|
When you create a new blank database, Access opens a
database that contains a table.
|
|
True or False:
|
To save a database file in a location other than the
default, click the Folder icon.
|
|
True or False:
|
The table structure is created when one enters data.
|
|
True or False:
|
The Templates group commands are located on Create Tab.
|
|
True or False:
|
To copy a table, it must be selected in the Navigation
pane.
|
|
True or False:
|
When table is pasted; Paste Table As dialog box is
displayed.
|
|
True or False:
|
After creating any database object, it must be saved with
descriptive name.
|
|
True or False:
|
When you quit the program, Access automatically saves the
data.
|
|
True or False:
|
To create a new database table, create a new blank
database, use quick start and copy the structure of another table.
|
|
|
|
|
Ch. 3
|
Working with Tables and Data Base
Records: Sort, Filter & Referential integrity.
|
|
Primary Key:
|
It can be defined in Design View by selecting the row
contains field.
|
|
Foreign Key:
|
When a primary key of a table is used in another table.
|
|
Composite Key:
|
Two or more primary keys in a table are called the
composite key
|
|
Wildcard Character:
|
It is a question mark or asterisk to find words that
contain specific letters or combination of letters.
For example: b?t or b*t will find bat, bet, bit and but.
|
|
Attaching Document:
|
It must be done in a field of a table having attachment
data type formatted. The file maximum
of 2 gig data, each file cannot exceed 256 megabytes in size.
|
|
Sorting Data:
|
It means to arrange it alphabetically, numerically or
chronologically.
Multiple Sort:
1.
Primary sort: also called outermost field, e.g. Last Name.
2.
Secondary sort: also called innermost field, e.g. First
Name.
|
|
Filtering:
|
A set of rules for determining which records will be
displayed.
Toggle Filter is to remove the filter.
|
|
Table Relationship:
|
Information stored in separate tables when connected by
defining relationship that ties the data together. It can be of three types:
One-to-one, One-to many and many-to-many.
|
|
Referential Integrity:
|
It is an option to Edit Relationship Dialog Box to prevent
orphan records.
|
|
Guess What?:
|
To arrange data alphabetically, numerically, or
chronologically - Sort
|
|
Guess What?:
|
A set of rules for determining which records will be
displayed - Filter
|
|
Guess What?:
|
Sorting data from beginning to end - ascending order
|
|
Guess What?:
|
Sorting data from end to beginning - descending order
|
|
Guess What?:
|
The primary sort field in a multi field sort – outermost
field
|
|
Guess What?:
|
The Secondary sort field in a multi field sort – innermost
field
|
|
Guess What?:
|
A primary key from one table that is used in another table
– foreign key.
|
|
Guess What?:
|
Two or more primary keys in a table – composite key
|
|
Guess What?:
|
Characters used to find words or phrases that contain
specific letters or combinations of letters – wildcards
|
|
Guess What?:
|
Prevents orphan records, to ensure that records do not
reference other records that no long exist – referential integrity
|
|
True or False
|
You can use the Navigation buttons to search for data in a
table.
|
|
True or False
|
After you enter data and move to a new field, Access
automatically saves the data for you in the table.
|
|
True or False
|
An auto number field will usually make a good primary key.
|
|
True or False
|
Before you can attach a document, there must be a field in
a table formatted with the attachment data type.
|
|
True or False
|
The outermost field is the primary sort field in a multi
field sort.
|
|
True or False
|
In one-to-many relationship, each record in the first
table can have many records in the second table.
|
|
True or False
|
You can enter any kind of data to any field (according to
field type)
|
|
True or False
|
After you delete a record you cannot undo it.
|
|
True or False
|
The Find and Replace dialog box searches all the tables in
a database at one time. (open table only)
|
|
True or False
|
The Toggle Filter button lets you permanently remove a
filter and switches you back to the original view. (Undo the filter)
|
|
Ch. 4
|
Modify Tables and Fields, properties, validation
rule.
|
|
Creating Field:
|
As a field has certain defining characteristics that
identifies the field within a table and a Data Type that is chosen to match
the information to be stored. So every field must have such field associated
setting called properties.
|
|
Properties:
|
It defines the appearance or behavior of the field.
|
|
Validation rule:
|
It is an expression that limits the values that can be
entered in the field 2048 characters
|
|
Validation text:
|
It specifies the text in the error message that appears
when a user violates the validation rules. It could be “Please enter a date
that is later than June 4, 2014.”
|
|
True or False
|
Renaming or deleting a table could possibly break the
functionality of the database.
|
|
True or False
|
If you want more space to enter or edit a setting in the
property box, press Shift+F2 to display the zoom box.
|
|
True or False
|
Using the multi value field allows you to select more than
one choice.
|
|
True or False
|
Validation text specifies the text in the error message
that appears when users violate a validation rule.
|
|
True or False
|
The caption property field specifies the text displayed by
default as column names in tables and in labels for forms, reports, and queries.
|
|
True or False
|
When creating fields, use the commands in the Add and
Delete group on the Table Tools Fields contextual tab.
|
|
True or False
|
A quick start field is a predefined set of characteristics
and properties that describes a field.
|
|
True or False
|
Creating multivalued fields can be accomplished by using
the Look up wizard.
|
|
True or False
|
The address Quick Start includes fields for city, state,
and zip
|
|
True or False
|
To create a new field, you would use the design view
group.
|
|
True or False
|
To rename an access database object, first close it.
|
|
True or False
|
If you delete a database table, you cannot undo it.
|
|
True or False
|
A complete list of field properties is available in Design
View.
|
|
True or False
|
Column Template is not a field property.
|
|
True or False
|
A name that is listed in the column is called caption.
|
|
True or False
|
The ‘append only property’ applies only to a field that is
set to memo.
|
|
True or False
|
Filtering is not a way to validate data.
|
|
True or False
|
Data type, Field sizes, Field properties are validate data
examples.
|
|
True or False
|
Text is used for the Caption field property.
|
|
True or False
|
Multivalued type of field allows you to select more than
one choice from a list.
|
|
True or False
|
An example of a validation rule is =M for male or F for
female.
|
|
|
|
|
Ch. 5
|
Create Forms: Blank Form, Filter by
Form, Tool, Wizard and Themes
|
|
Form:
|
It is a data Base Object that you can use to enter, edit,
or display data from a table or query.
|
|
Form tool:
|
It is a tool to create a form with a single
mouse-click.
|
|
Form Design Button:
|
It is a tool to create a new blank form in design view. When you click it; Access will show a pane
on right hand side with a heading of field list. It means it asks you to select the field
that you want to show in your form. If
more than one table, you can also create fields from different table of your
choice. It has two views: Form View (shows form) and Layout View (shows field
with yellow squares). By dragging
these yellow fields you can adjust your form height and width. By using this
tool; Form Layout Tools caption will pop up with three tabs: Design, Arrange
and Format:
Design Tab: View, Themes, Controls, Header/Footer, &
Tools Groups.
|
|
Blank Form Tool:
|
It creates new form in Layout view. This tool will use
automatically all the fields from the data source to place on the form in
Layout view. This shows yellow squares
for each field selected. However, if so desired; it can be modified in Design
View.
|
|
Form Wizard Tool:
|
This tool also used predefined style like Blank Form
Tool. It is very easy and logical way
of building a form using Form Wizard giving you the option to select the
fields that you want to appear on the form.
Special Note: In
this type of building form using Form Wizard Tool, do not click Finish,
repeat the steps to select additional field from multiple tables that you
want to include on the form.
|
|
Themes
|
The Themes command applies a predefined color and font
scheme to a form or report. Remember!
Themes can only be applied in Layout View.
|
|
Sorting within a Form:
|
Sorting within a form Option is available as it helps
users to review and locate the records they want without having to browse the
data.
|
|
Filtering within a Form:
|
Filter limits a view of data to specific records without
requiring you to alter the design of the form. After one applied filter; one should save
it, so that it will be available the next time one open the form otherwise it
will be removed.
|
|
Guess What?
|
Database object that you can use to enter, edit, or
display data from a table or query – form.
|
|
Guess What?
|
Quickly creates a new blank form in Layout view – Blank
form button
|
|
Guess What?
|
Quickly creates a new blank form in Design view – Form
design button
|
|
Guess What?
|
Allows you to select fields for the form, let you choose
the form lay-out, and also let you choose the predefined form layout – Form
wizard
|
|
Guess What?
|
Creates a simply form with a single mouse-click – form
tool
|
|
Guess What?
|
Applies a predefined combination of colors and fronts that
you select for a form or report – Theme Command
|
|
Guess What?
|
Helps users review and locate records without having to
browse the data – sorting
|
|
Guess What?
|
Limits a view of data to specific records without requiring
you to alter the design of the form – filter
|
|
Guess What?
|
Built into every view that displays data – common filters
|
|
Guess What?
|
Useful when you want to filter on several fields in a form
or if you are trying to find a specific record – filter by form
|
|
True or False
|
Forms can be used to control access to data, such as which
fields or rows of data are displayed.
|
|
True or False
|
After you save your form design, you can run the form as
often as you want.
|
|
True or False
|
Using the Blank Form tool is a very quick way to build a
form, especially if you plan to put only a few fields on your form.
|
|
True or False
|
You cannot remove a sort order from just a single field.
|
|
True or False
|
The filters available depend on the field’s data type and
values.
|
|
True or False
|
The Forms group is located on the Home Tab in the Ribbon.
(Create)
|
|
True or False
|
Layout view gives you a more detailed view of the
structure of your form that Design View (Design View is more structured)
|
|
True or False
|
To access the Theme options, first switch to Form View
(layout view)
|
|
True or False
|
To filter by form, first switch to Design View (Form View)
|
|
True or False
|
To filter by form, first switch to Layout View (Form View)
|
|
True or False
|
When using the Form Wizard, you can only include fields
from one table (more than one)
|
|
Ch. 6
|
Create Reports
|
|
Report
|
It is a data base Object
use to organize and display data pulled from tables and queries.
|
|
Record Source:
|
It is the table or query that provides the data used to
generate a report.
|
|
Report Wizard:
|
|
|
Apply a Theme:
|
|
|
Filtering Data:
|
|
|
Finding Data:
|
|
|
|
|
|
|
|
|
Ch. 7
|
Create and Modify Queries
|
|
Query:
|
It is a set of instructions used for working with data. It is like asking a data base a question.
|
|
Field List
|
|
|
Query Criterion
|
|
|
Parameter Query
|
|
|
|
|
