Mis Final Exam - Access

Front 1

What is a database?

Back 1

A collection of data structured and stored with the goal of minimizing redundancies and facilitating manipulation and retrieval
Relational database is structured as a collection of tables
Columns are called fields – you’ve seen this before
Rows are called records

Front 2

Uses and Benefits of Relational Databases

Back 2

Data – pieces of information
Database Management System (DBMS) provides organizations with the means to store, maintain and gain access to their data.

Front 3

Planning a Database

Back 3

First: Is this the correct tool?
What are the required results?(output)
What information is available? (input)
Sketch the structure - including the data types

Front 4

Steps to create a new database

Back 4

Step 1: Create the database file.
Step 2: Create tables.
Step 3: Define the relationships between the tables.
Step 4: Create input forms (maybe).
Step 5: Populate the database.
Step 6: Create Reports.
Step 7: Query the database.

Front 5

Creating a Table

Back 5

From the table option, you select new
In design view you can
add/delete/change fields
add descriptions
modify properties
assign keys

Front 6

FIELD NAME: Identifies data that is entered into the field

Back 6

Rule 1: It should be descriptive of the data and can be up to 64 characters in length, including letters, numbers, and spaces.
Rule 2: It includes letters, numbers and some special characters
Rule 3: Do not use spaces.

Front 7

Properties of the Field

Back 7

It is a characteristic or attribute of an object that determines how the object looks and behaves.
Each field has its own set of properties that determine how the data in the field is stored and displayed.

Examples of properties:

Field Size, Format, Input Mask, Caption, Default Value, Validation Rule, Validation Text, Required, Allow Zero Length, Indexed

Front 8

Data Types

Back 8

DATA TYPE: Determines the type of data that can be entered and the operations that can be performed on that data

Front 9

Access recognizes the following data types:

Back 9

Number (can be used in calculations)
Text (alphanumeric characters up to 255 per field)
Memo (alphanumeric value of unlimited length)
Date/Time
AutoNumber (numeric value, automatically increases)
Yes/No
OLE Object
Currency Hyperlink

Front 10

PRIMARY KEY

Back 10

It is a field (or combination of fields) that makes each record in a table unique. Every table must have a primary key and each record must have a unique value as its primary key.

Front 11

Relationships

Back 11

PRIMARY KEY: It is a field (or combination of fields) that makes each record in a table unique. Every table must have a primary key and each record must have a unique value as its primary key
Tables and their relationships to each other are stored
Tables are related in a one-to-many (or one-to-one) relationship
A foreign key is required to establish the relationship – one table’s foreign key is another table’s primary key.

Front 12

Referential Integrity

Back 12

The tables in a database must be consistent with one another. For example, you can not delete a record in the Customer Table if it has related records in the Orders table.

Front 13

Database limitations
Access Limitations

Back 13

Database limitations
2 gigabytes in size
Maximum of 32,768 objects
64 character maximum in an object name
14 character maximum for passwords
Support up to 255 concurrent users

Front 14

Table limitations

Access Limitations

Back 14

Table limitations
have up to 64 characters in each field name
have up to 255 fields
be one of 2048 concurrent open tables
be up to 1 gigabyte
have 32 indexes
be sorted by up to 255 characters in one or more fields

Front 15

Relationships

Back 15

Access is a relational database
Tables and their relationships to each other are stored
Tables are related in a one-to-many (or one-to-one) relationship
A foreign key is required to establish the relationship

Front 16

One to Many
Relationships

Back 16

Table 1 can have many of table 2, table 2 can only have 1 of table 1
Ex. A father can have many biological children, but each child can have only one biological father.

Front 17

Many to Many
Relationships

Back 17

Table 1 can have many of table 2, and table 2 can have many of table 1
Ex. Each class can have many students, and each student can take many classes

Front 18

One to Many Example

Back 18

One to Many Example
Customer can have many orders, but each order can only belong to one customer

Front 19

Building Forms

Back 19

A form provides an easy way to enter, change, and display the data stored in a table.

Front 20

Methods for Building Forms

Back 20

Methods
Autoform wizards – 5, each with a different form layout. Allows use of one table only
Form Wizard – user specifies one or more tables and display format
Design View – user has control over fields, formats and calculations

Front 21

Forms

Back 21

Parts of a form or report
Header – top section that stays the same for all pages of the form / report
Might include the form / report title
Body – middle section that changes based on the data derived from the database
For example, if referencing a customer table, the body section would automatically change for each customer as one moved through the table
Footer - bottom section that stays the same for all pages of the form / report
Might include page numbers, date, etc.

Front 22

Controls
Forms

Back 22

Objects on a form or report that accept and display data

Front 23

Types of Controls
Forms

Back 23

Bound control
Has a data source
Unbound control
Has NO data source
Calculated control
Data source is an expression (e.g., addition of two fields)

Front 24

What is a query?

Queries

Back 24

“Let’s you see the data you want in the sequence you want it”

Front 25

Query components

Back 25

Dynaset – window that shows the results of a query

Selection Criteria – specifies how you want the data to be filtered

Sort – allows you to sort in ascending or descending sequence

Show checkbox – allows you to show or not show the field in the query results

Front 26

Form

Back 26

Provides an interface for interacting with data stored in tables
Used by individuals that are tasked with data entry / editing / deleting

Front 27

Report

Back 27

Provides a graphical view of data stored in tables
Often used by managers to provide a simple explanation of complex data

Front 28

Building Forms
A form provides an easy way to enter, change, and display the data stored in a table.

Back 28

Methods
Autoform wizards – 5, each with a different form layout. Allows use of one table only
Form Wizard – user specifies one or more tables and display format
Design View – user has control over fields, formats and calculations

Front 29

Report Header

Back 29

appears once at the beginning of a report

Front 30

Report Footer

Back 30

appears once at the end of a report

Front 31

Page Header

Back 31

Page Header appears at the top of every page in a report and can be used to display date and page numbers

Front 32

Page Footer

Back 32

Page Footer appears at the bottom of every page in a report and may contain date and page numbers

Front 33

Group Header

*** Group Header and group footer are used only when the records in a report are sorted (grouped) according to a common value in a specific field.

Back 33

Group Header – appears at the beginning of a group of records to identify the group

Front 34

Group Footer

*** Group Header and group footer are used only when the records in a report are sorted (grouped) according to a common value in a specific field.

Back 34

Group Footer - appears after the last record of a group and contains summary information about the group

Front 35

Detail Section

Back 35

Detail Section – Appears in the main body of a report and is printed once for every record in the table

Front 36

What is a Query ?

Back 36

A query lets us see the data we want in the sequence that we want it.

It lets us select specify records from table (or from several tables) and show some or all of the fields for the selected record.

It also lets us perform calculations to display data that is not explicitly stored in the underlying table. For example: customer’s age.

Front 37

Queries are more powerful than filters because:

Back 37

Can retrieve data from multiple tables
Can add calculations to the results
Can select records for display in a form or report

Front 38

Most common type of query

Back 38

Features:
Display selected rows of data
Display selected columns of data
Sort query results
Calculate within records (i.e., calculating gross pay for each employee record)
Group records and create subtotals (i.e., subtotaling expenses for each department)
Calculate totals such as sums, counts, and averages

Front 39

Simple Queries

Back 39

Add the tables you want (Query-> add table)
Double click on the fields you want to include in your query
Can show fields or not, sort them or not, you can hide several fields and sort by as many fields as you want
Can also use the criteria row to select only certain records

Front 40

advanced Queries

Back 40

Using the “and”, “or” and “not” conditions
Using relational operators ( <, >, =, <>)
Using “Is Null” – try with student address
Using “Between…And…”
Adding calculated fields
Using the expression builder

Front 41

Calculated field

Back 41

Calculated field – holds a mathematical expression whose results will be displayed in the field

Calculated fields can be entered directly into the design grid, or created using the Expression Builder. To access the builder, right click on any field in the query grid and choose “build”…

Front 42

Entering Expressions directly

Back 42

Expressions can be typed directly into a cell, rather than using the expression builder
Remember to separate the Column label from the expression using a colon :
Tables are referenced by name, with table name and field name separated by a !
SubTotal:Table1!Price * Table2!Quantity

Front 43

Aggregate Operations

Back 43

They perform calculations on a group of records using one of several aggregate (summary) functions available within Access
Used to calculate summary data
Use predefined functions to calculate the summary data. Examples: Sum, Count, Avg, Max, and Min

Front 44

Sum

Back 44

Totals the field values for selected records

AutoNumber,Currency, Date/Time, and Number

Front 45

Avg

Back 45

Averages the field values for the selected records

AutoNumber,Currency, Date/Time, Memo, Number, OLE Object, Text, and Yes/No

Front 46

Count

Back 46

Counts the number of selected records

AutoNumber,Currency, Date/Time, and Number and Text

Front 47

Max

Back 47

Returns the highest field value


AutoNumber,Currency, Date/Time, and Number and Text

Front 48

Minimum

Back 48

Returns the lowest field value

AutoNumber,Currency, Date/Time, and Number and Text

Front 49

Total Queries

Back 49

Total Queries perform calculations on a group of records
Total row – Contains either Group by or aggregate entry
Group By – Records in the dynaset are to be grouped according to the like values
Sum Function – Specifies math to be performed on that field for each group of records

Front 50

Summarizing data using Aggregate Functions

Back 50

Create query with fields to be summarized
Click Totals button to Add Total Row to QBE grid
Select Aggregate function for each column
Go to Datasheet view to see results

Front 51

Action Queries

Back 51

Enables table updates. Four types:
Update: Changes multiple records
Append: Adds records from one table to another
Delete: deletes records that meet a criteria
Make-table: Creates a new table from existing records

Front 52

Input Masks

Back 52

Input masks are used to improve data entry for Text, Date, Number and Currency fields, by providing a pattern of input for the user to follow

Input masks are strings of characters which specify what is displayed to the user, and what is accepted as data input

Input masks can either be created using the Input Mask Wizard (text and data fields) or typed in manually (numeric and currency)