Access Chapter 1: Creating a Database and Tables

Hierarchy of Data: Record

- A collection of related data fields. - Examples: All of the fields for one student stored together

Primary Key

- A field that contains a unique value for each record. - This is the way that one record can be distinguished from another. - For example, the InstructorID field was designated as the primary key in the Instructors table in the CollegeOfSciencesClasses-01 database, because each instructor has a unique ID number. - When you enter values into a record, Access requires that you enter a value in any field designated as a primary key.

Hierarchy of Data: Database

- An organized collection of integrated and related tables. - The collection of the student, faculty, and course tables for the entire university

Gridlines

- Help visually frame the rows and columns in a datasheet with a border. - The gridline display options include: - Both vertical and horizontal gridlines - Only horizontal gridlines - Only vertical gridlines - No gridlines

Access Contains Two Additional Objects

- Macros - Modules

Relational Database

- Means that the data are organized into a collection of related tables. - Each table stores data about one type, or grouping, of information represented in the system. - For example, a database application used by a retail store contains a table with details about its customers, a table with details about the products it sells, and a table with details about the orders its customers have made for the various products. - The different tables are related, or connected, to one another through common fields. - Access

Major Access Objects: Table

- Purpose: Stores data records. - Additional Information: Once a table is created, you can begin entering data. Create a separate table for each type or grouping of information. When you view data in a table, it appears in a tabular format, very much like a spreadsheet.

Major Access Objects: Form

- Purpose: Used to create an interface to view, add, update, and delete data in a table. - Additional Information: Although you can do these tasks directly in a table, a form usually provides an easier way to interact with the data. Forms also allow you to view only one record at a time and to view fields from multiple tables at the same time.

Major Access Objects: Query

- Purpose: Used to find data in your database. - Additional Information: Queries allow you to specify criteria to locate specific records. Queries can also be used to perform actions such as updating or deleting records.

Major Access Objects: Report

- Purpose: Used to view and print the data in your database. - Additional Information: Reports allow you to create a formatted, and more professional, way to view and print the contents of your database.

Hierarchy of Data: Table

- Shows data in an organized, easy-to-read format with information arranged in columns and rows; in Access, database object that stores a collection of related records. - Examples: A record for each of the students at the university stored together

Database Properties (Details)

-Include information about the date the database was created and its author. - Common: View and edit database, Size, or Modified - Cannot edit on many different tabs.

The file default save is

.accdb file.

Hierarchy of Data: Field

A collection of characters that describes one aspect of a business object or activity—a single unit of data. - Examples: Date Enrolled, Student First Name, Student Last Name, Student ID

Template

A` predesigned database that contains various pre-built objects.

Filter by Selection

Allows you to display only the records that match the value in the fields you have selected.

Database

An organized collection of integrated and related tables.

Navigation Bar

Area at the bottom left corner of a table that contains buttons used to move among table records in Datasheet view.

Active Cell

Cell that is ready for editing, surrounded with a solid border.

Blank Desktop Database

Command used to create a new database from scratch. - Access automatically creates one new table and opens that table in DataSheet View.

Navigation Menu

Contains category and group settings that control the display of objects in the Navigation Pane.

Access' Additional Objects: Macros

Database object used to attach code to objects and controls in a database.

The First step in Creating a Database

Determine the best way to organize the data.

Navigation Pane

Displays all objects contained in a database.

DataSheet Properties: Name

Each field in a table must be given a unique name.

DataSheet Properties: Field Size

Enter the smallest, sufficient field size to ensure best performance.

Search

Feature that allows you to look for specific data values.

Sort

Feature that arranges text, table rows, or records in alphabetical or numerical order.

Filter

Feature that limits the data records that display in a datasheet.

Find

Feature that searches a file to locate specific text and/or formatting.

Data Type

Field property that determines the type of data that can be entered in a field, what properties can be manipulated, and how that field can be used.

Alternate Row Colors

Formatting option to define alternating colors for adjacent rows.

Navigation Pane Category: Tables and Related Views

Groups different objects according to the tables on which they are based.

Navigation Pane Category: Custom

Groups objects based on custom categories that you create.

Navigation Pane Category: Created Date

Groups objects on the basis of the week associated with the date they were created.

Navigation Pane Category: Modified Date

Groups objects on the basis of the week associated with the date they were last modified.

Navigation Pane Category: Object Type

Groups objects on the basis of their type.

Append Row

Last row in a table, indicated by an asterisk; enter data values into the fields in the append row to add a new record into the table.

Objects

Major component in an Access database.

Right Aligned Data Types

Number, Date/Time, Currency, and Calculated.

Shutter Bar Open/Closed Button

Opens and closes the Navigation Pane in Access.

Hierarchy of Data

Organization of data in a database from the smallest to largest grouping of data.

Contextual Tabs

Ribbon tabs that becomes available when working on different tasks.

DataSheet Properties: Data Type

Select a data type that matches the characteristics of the stored data.

Left Aligned Data Types

Short Text, Long Text, and Hyperlink.

Database Management System (DBMS)

Software that allows you to create a database; manage the data in the database by adding, deleting, and updating records; sort and retrieve data; and create queries and reports relating to that data.

Access Data Type: Lookup Wizard

Technically, this is not a data type. It is used to convert your field into a combo box that allows you to choose a value from another table or from a list of values.

DataSheet Properties: Caption

The caption is an alternative to the field name that displays in the datasheet. Captions often include spaces and multiple words.

DataSheet Properties: Description

The description describes the purpose or meaning of a field if the field name by itself does not clearly convey the purpose or intended content. It displays in the lower, left corner of the datasheet.

Access Data Type: Attachment

Used to attach pictures, images, and Office files.

Access Data Type: OLE Object

Used to display OLE objects (e.g., spreadsheets, documents, pictures, sounds) that were created in other programs.

Access Data Type: AutoNumber

Used to instruct Access to insert a unique, sequential (e.g., increments by 1) number each time a new record is added.

Access Data Type: Hyperlink

Used to store a hyperlink to another file or web page.

Access Data Type: Short Text

Used to store alphanumeric data (text, or combinations of text and numbers). Also used to store numbers that are not used in calculations, such as phone numbers. The maximum length is 255 characters.

Access Data Type: Date/Time

Used to store dates and times.

Access Data Type: Yes/No

Used to store fields with two possible values, such as Yes/No, True/False, or On/Off. Displays as a check box.

Access Data Type: Currency

Used to store numbers representing currency values. This type does not allow rounding off during calculations.

Access Data Type: Number

Used to store numbers that are used in mathematical calculations. The number can be positive or negative. There are seven field size choices for the number data type which determine the range of possible values and amount of storage space required.

Access Data Type: Long Text

Used to store text longer than 255 characters, or text containing rich-text formatting. The maximum length that displays is limited to the first 64,000 characters.

Access Data Type: Calculated

Used to store the results of a calculation. Typically the equation references fields in the same table.

Center-Aligned Data Types

Yes/No and Attachment. - You are not able to change the alignment of fields with these Yes/No and Attachment data types.