Chapter 1

Module

Similar to a macro, as it is an object that adds functionality to a database, but modules are written using the VBA (Visual for Basic Applications) programming language.

Descending

Sorts a list of text data in reverse alphabetical order or a numeric list highest to lowest order.

Access

The database management system included in the Office 2013 Professional suite and the Office 2013 Professional Academic Suite. It is a valuable decision-making tool that many organizations are using.

All of the following statements are true about creating a database except: a. Creating a custom Web app requires that you use a host server. b. When creating a blank desktop database, Access opens to a blank table in Datasheet view. c. Using a template to create a database saves time because it includes predefined objects. d. The objects provided in a template cannot be modified.

d. The objects provided in a template cannot be modified.

When to use Access over Excel?

1.) Require multiple related tables to store your data. 2.) Have a large amount of data. 3.) Need to connect to and retrieve data from external databases, such as Microsoft & SQL Server. 4.) Need to group, sort, and total data based on various parameters. 5.) Have an application that requires multiple users to connect to one data source at the same time.

Advantages of using Filter by Form

1.) You can specify AND and OR logical operators. 2.) You can use a comparison operator.

Database

A collection of data organized as meaningful information that can be accessed, stored, queried, sorted, and reported.

Foreign key

A field in one table that is also the primary key of another table.

Datasheet view

A grid containing fields (columns) and records (rows), similar to an Excel spread sheet. You can view, add, edit, and delete records in Datasheet view.

Macro

A macro object is a stored series of commands that carry out an action. You can create a macro to automate simple tasks by selecting an action from a list of macro actions.

Filter by Form

A more versatile method of selecting data because it enables you to display table records based on multiple criteria. All of the records are hidden and Access creates a blank form in a design grid. You see only field names with an arrow in the first field.

Criterion

A number, a text phrase, or an expression used to select records from a table.

Query

A question that you ask about the data in your database. It can be used to display only records that meet certain conditions and only the fields that you require. In addition to helping you find and retrieve data that meets the conditions that you specify, you can use a query to update or delete records and to perform predefined or custom calculations with your data.

Enforce referential integrity

A relationship option (one of three) that ensures that data cannot be entered into a related table unless it first exists in a primary table.

Database Management System (DBMS)

A software system that provides the tools needed to create, maintain, and use a database. DBMS make it possible to access and control data and display the information in a variety of formats, such as lists, forms, and reports.

Objects

An access database is a structured collection of objects, the main components that are created and used to make the database function. The main objects are: Tables Forms Queries Reports Macros Modules

Form

An object that gives a user a way of entering and modifying data in databases. It enables you to enter, modify, or delete table data and to manipulate data in the same manner you would in a table. The difference is that you can create a form that will limit the user to viewing only one record at a time. This helps users to focus on the data being entered or modified and also provides for more reliable data entry. You create and edit the form structure.

How is data converted to information?

By selecting, calculating, sorting, or summarizing records.

Record selector

Click the record selector at the beginning of a row to select the record. Each column represents a field or one attribute about a customer.

You have finished an Access assignment and wish to turn it in to your instructor for evaluation. As you prepare to transfer the file, you discover that it has more than doubled in size. What should you do?

Compact and repair the database before sending it to your instructor.

External data tab

Contains all of the operations used to facilitate data import and export.

Create tab

Contains all the tools used to create new objects in a database.

Report

Contains professional-looking formatted information from underlying tables or queries. Reports enable you to print the information in your database and are an effective way to present database information. You have control over the size and the appearance in everything in the report. Access provides different views for designing, modifying, and running reports.

Database tools tab

Contains the feature that enables users to create relationships between tables and enables use of the more advanced features of Access, such as setting relationships between tables, analyzing a table or query, and migrating data to SharePoint.

Field properties

Define characteristics of the fields in more detail.

Data types

Define the type of data that will be stored in a field, such as short text, long text, numeric, currency, etc.

Record

Displayed in each row in a table. It is a complete set of all the fields (data elements) about one person, place, event, or concept. Each record contains multiple fields, with the field name displaying at the top of each column.

Filter

Displays a subset of records based on specified criteria.

Filter by Selection

Displays only the records that match a criterion you select.

Custom Web App

Enables you to create a database that you an build and then use and share with others through the web. A database that can be built, used, and shared with others through the use of a host server (i.e. SharePoint or Office 365).

Save Object As

Enables you to make a copy of the current Access object or publish a copy of the object as a PDF or XPS file.

Save Database As

Enables you to select whether you want to save the database in default database format (Access 2007-2013 file format), in one of the earlier Access formats, or as a template.

An Employees table is open in Datasheet view. You want to sort the names alphabetically by last name and then by first name. To do this, you must:

First sort ascending on first name and then on last name. ???

Design view

Gives you added detailed view of the table's structure and is used to create and modify a table's design by specifying the fields it will contain, the field's data types, and their associated properties.

Decisions in an organization are usually based on...

Information produced by an organization, rather than raw data.

File tab

Leads to backstage view, which gives you access to a variety of database tools such as Save, Save As, Compact and Repair, Backup Database, and Print.

Blank desktop database

Lets you create a database specific to your needs.

Find command

Located in the Find group on the Home tab. Used to locate specific records within a table, form, or query. You can search for a single field or the entire record, match all or part of the selected field(s), move forward or back in a table, or specify a case-sensitive search.

Back Up Database

Makes a copy of the entire database to protect your database from loss or damage. Making backups is especially important when you have multiple users working on the database. When using this, Access provides a file name for the backup that uses the same file name as the database you are backing up, an underscore, and the current date.

What sequence represents the hierarchy of terms, from smallest to largest?

Record, field, table, database.

You edit several records in an Access table. When should you execute the save command?

Records are saved automatically; the save command is not required.

Compact & Repair

Reduces the size of the database. Entering data, creating queries, running reports, and adding and deleting objects will all cause a database file to expand. This growth may increase storage requirements and may also impact database performance. When you run Compact & Repair utility, it creates a new database file behind the scenes and copies all the objects into a new database. As it copies deleted objects into the new file, Access removes temporary objects and unclaimed space due to deleted objects, which results in a smaller database file. It will also de-fragment if needed. When it is finished copying the file, it deletes the original and saves the new one using the same name.

Ascending

Sorts a list of text data in alphabetical order or a numeric list in lowest to highest order.

Database from a template

Template: objects that you can use to jumpstart the creation of your database Saves a great deal of time.

Home tab

The default Access tab, contains basic editing functions, such as cut and paste, filtering, find and replace, and most formatting actions. This tab also contains the features that enable you to work with record creation and deletion, totals, and spelling.

Primary key

The field (or combination of fields) that uniquely identifies each record in a table. It ensures that each record in the table can be distinguished from every other record. It also helps prevent the occurrence of duplicate records. Primary key fields may be numbers, letters, or a combination of both.

Information

The finished product that is produced by the database.

Normalization

The formal process of deciding which fields should be grouped together into which tables.

Table

The foundation of every database. It is the object in which data is stored. The other objects in a database are based on one or more underlying tables.

Navigation Pane

The objects that make up an Access database are available from the Navigation Pane. It is an access interface element that organizes and lists the database objects.

AutoNumber

The primary key has an AutoNumber data type (a number that is generated by Access and is automatically incremented each time a record is added.

Field

The smallest data element of a table. The columns display the fields. Fields may be required or optional.

Navigation bar

Use to move through the records in a table, query, or form. The buttons enable you to go to the first record, the previous record, the next record, or the last record. The button with the yellow asterisk is used to add a new (blank) record. You can also type a number directly into the current record field, and Access will take you to that record. The navigation bar enables you to find a record based on a single word. Type the word in the search box, and Access will locate the first record that contains the word.

Comparison operator

Used to evaluate the relationship between two quantities. Comparison operator symbols include: equal (=), not equal (<>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=).

Field selector

Used to select a column.

Replace command

Used to substitute one value for another. Select Replace All if you want Access to automatically search for and replace every instance of a value without first checking with you.

Data

What is entered into a database.

Sort

You can change the order of information by sorting one or more fields. A sort lists records in a specific sequence.

Relational database management system (RDBMS)

You can manage groups of data (tables) and set rules (relationships) between tables. When relational databases are designed properly, users can easily combine data from multiple tables to create queries, forms, and reports.

Which of the following is true regarding the record selector box? a. An orange border surrounds the record selector box and the active record. b. A pencil symbol indicates that the current record already has been saved. c. An asterisk indicates the first record in the table. d. An empty square indicates the current record is selected.

a. An orange border surrounds the record selector box and the active record.

Which of the following is available through Filter by Selection? a. Equals condition. b. Delete condition. c. AND condition. d. OR condition.

b. Delete condition.

Which of the following is not true when creating relationships between tables? a. Join lines create a relationship between two tables. b. The common fields used to create a relationship must be both primary keys. c. The data types of common fields must be the same. d. Enforcing referential integrity ensures that data cannot be entered into a related table unless it first exists in the primary table.

b. The common fields used to create a relationship must be both primary keys.

Which of the following will be accepted as valid during data entry? a. Adding a record with a duplicate primary key. b. Entering text into a numeric field. c. Entering numbers into a text field. d. Omitting an entry in a required field.

c. Entering numbers into a text field.

Which of the following is not true of an Access database? a. Short Text, Number, AutoNumber, and Currency are valid data types. b. Every record in a table has the same fields as every other record. c. Every table in a database contains the same number of records as every other table. d. Each table should contain a primary key; however, it is not required.

c. Every table in a database contains the same number of records as every other table.

Three methods for creating a new database:

1.) Creating a custom Web App 2.) Creating a blank desktop database 3.) Creating a database from a template.

Relationship

A connection between two tables using a field that is common to the two tables. The benefit of a relationship is the ability to efficiently combine data from related tables for the purpose of creating queries, forms, and reports. They are the reason Access is referred to as a relational database.