Microsoft Access 2016 Essentials

Overview of Database Objects in Access 2016

A database is a structured collection of related information. Microsoft Access 2016 allows users to manage and compile database objects within a single file through the Navigation Pane located on the left side of the window. The primary objects within Access include Tables, Queries, Forms, and Reports.

Tables store raw data within the database. Queries allow users to search, filter, and extract specific records across tables based on defined conditions. Forms serve as interactive interfaces to view, enter, and edit record data. Reports format and summarize table or query data into static documents intended for printing or exporting.

Table Relationships

A relationship connects fields in separate tables that contain matching data, enabling data retrieval across multiple tables. Relationships rely on key columns: a primary key uniquely identifies each record in a master table, while a foreign key in a child table references the primary key of the master table.

Access supports three primary types of table relationships:

  • One-to-One Relationship: Connects a single record in one table to a single record in another table via a primary key.
  • One-to-Many Relationship: The most common relationship type, linking one record in a master table to multiple records in another table.
  • Many-to-Many Relationship: Links multiple rows of one table to multiple rows in another table using a junction table containing the primary keys of both tables.

To establish a relationship, select Relationships under the Database Tools tab, add the target tables using the Show Table dialog box, and drag the primary key field (such as Student_ID) to the matching field in the child table before saving.

Database Queries

A query retrieves selective records and fields from one or more tables, displaying the results in a tabular row-and-column layout. The query window consists of two main sections: the top Design Area, which displays source tables, fields, or subqueries; and the lower Design Grid, where columns and operational rules are configured.

The Query Design Grid contains six essential field properties:

  • Field: Indicates the specific field selected from the table.
  • Table: Identifies the source table for the field.
  • Sort: Filters field values in Ascending or Descending order.
  • Show: Contains a checkbox indicating whether the field appears in the final query result.
  • Criteria: Defines specific condition filters applied to the dataset (e.g., '=Present').
  • Or: Enables the evaluation of multiple filter conditions.

Queries can be created using Query Design or the Query Wizard. The Query Wizard provides several wizard types, including the Simple Query Wizard, Crosstab Query Wizard, Find Duplicates Query Wizard, and Find Unmatched Query Wizard.

Database Forms

Forms serve as the primary user interface for entering, modifying, and displaying table records. They provide a structured layout for viewing individual records and perform actions like sorting and updating.

Forms offer three distinct views:

  • Form View: Used to enter, edit, and navigate through table records using the Record Navigation Bar.
  • Design View: Provides structural control over form layout, including header and footer sections.
  • Layout View: Allows real-time modification of control sizes and visual appearance while viewing live data.

To create a form, select a table in the Navigation Pane, click the Create tab, and select Form under the Forms group.

Database Reports

Reports present summarized database information on paper or formatted for external distribution. Unlike forms, reports are static objects that display data without allowing direct record editing.

Reports feature four operational views:

  • Report View: Displays generated output without permitting layout adjustments.
  • Print View: Previews the report layout as it will appear when printed.
  • Design View: Enables detailed visual and structural modifications without altering underlying table data.
  • Layout View: Displays real-time data while allowing manual adjustments to control dimensions.

To build a report, choose the source table in the Navigation Pane, navigate to the Create tab, and select Report under the Reports group.