Table of Contents

Microsoft Access is a Database Management System (DBMS) that integrates the relational Microsoft Jet Database Engine with a graphical user interface (GUI) and various software-development tools. It is primarily used to develop application software and is a staple for data architects, software developers, and power users.

Major Components of MS Access

A database document in Access stores and accesses data through several key components:

  1. Tables: The core where all data is stored in rows (records) and columns (fields).
  2. Queries: Tools used to filter, calculate, sort, and update data to produce a specific calculated output.
  3. Relationships: The connections established between multiple tables (e.g., One-to-One, One-to-Many, or Many-to-Many).
  4. Forms: User interfaces created to make entering, modifying, and viewing data easier and more consistent.
  5. Reports: Formatted summaries of data used for review, analysis, and printing.
  6. Macros: Tools that automate repetitive tasks by assigning predefined actions.
  7. Modules: Collections of predefined instructions written by programmers that can be used throughout the database.

Understanding RDBMS

A Relational Database Management System (RDBMS) is a database type that stores data in tables so it can be used in relation to other datasets. Most business databases today are relational rather than flat-file or hierarchical. In an RDBMS, tables are often referred to as "relations". Data is organized into horizontal records (rows) and vertical fields (columns). RDBMS systems are capable of handling massive amounts of data and complex queries.

Basic Objects of a Database

Access databases are primarily composed of four basic objects that allow users to manage data effectively:

  1. Tables: The heart of the database where all raw data resides.
  2. Forms: Act as easy-to-use guides for entering data correctly into one or more related tables.
  3. Queries: Function like detailed questions asked of the database to find specific information based on search conditions.
  4. Reports: Used to present database components in a visually appealing, easy-to-read, and printable format.

Creating and Managing Tables

Methods for Creating a Table

Access offers several ways to initiate a new table:

  1. Datasheet View: Create a table by directly entering data into a blank grid.
  2. Design View: Allows for detailed definition of field names, data types, and descriptions before entering data.
  3. Table Wizard: A guided process for table creation.
  4. Import/Link Table: Create a table by bringing in data from external sources like Excel, XML files, or other Access databases.
    • Importing: Creates a local copy of the data; changes to the source do not affect the Access table.
    • Linking: Creates a live link; changes in either the source or the Access table are reflected in both.

Setting Data Types

Every field in a table must have a designated data type, which dictates what kind of information it can hold:

  • Text: The default for general text or numbers not used for math (e.g., phone numbers).
  • Number: For values requiring calculations (e.g., quantities).
  • Currency: Automatically formats numbers for regional currency.
  • Date & Time: Provides a calendar picker for easy entry.
  • Yes/No: Inserts a checkbox for boolean values.
  • Memo: For large amounts of formatted text (e.g., long descriptions).
  • Attachment: For attaching files like images.
  • Hyperlink: For website URLs or email addresses.

Naming and Renaming Fields

  • In Datasheet View: Right-click the column heading and select Rename Field.
  • In Design View: Click the cell in the Field Name column and edit the text.

Entering, Saving, and Closing Records

  • Entering Records: Open the table in Datasheet view, click the first cell of the first row, type your data, and use the Tab key to move to the next field.
  • Saving: Click the Save button on the Quick Access Toolbar; if it's a new table, you will be prompted to name it and define a Primary Key.
  • Closing: Go to File > Close; Access will prompt you to save any unsaved changes before closing the table window.

Adding Validation Rules

Validation ensures that the data entered meets specific criteria.

  1. Open the table in Design View.
  2. Select the field you wish to validate (e.g., "Price").
  3. In the Field Properties section below, locate the Validation Rule box and type the rule (e.g., >0 to prevent negative prices).
  4. In the Validation Text box, type the error message you want to appear if the rule is violated.
  5. Save the table to apply the validation.

Modifying a Table

Field Management

  • Rearranging Fields: In Datasheet view, hover over the bottom border of a field header until a four-sided arrow appears, then click and drag the field to its new position.
  • Adding Fields: Click the "Click to Add" header in Datasheet view, select the data type, and type the new field name.
  • Deleting Fields: In Design view, select the field and press DEL, or use the Delete Rows button on the Ribbon.

Layout Adjustments

  • Changing Column Width: Click and drag the right border of the column header, or right-click the header and select Column Width to enter a specific value.
  • Hiding/Unhiding: Right-click a column heading and select Hide Fields. To restore them, select Unhide Fields and check the desired columns in the popup.
  • Freezing Fields: Right-click the rightmost column you want to stay visible and select Freeze Fields. This keeps selected columns on the left while you scroll through others.

Editing and Selecting

  • Editing Records: Navigate to the record, click the cell (a pencil icon will appear), type the new info, and click outside the row to save. You can also use Find and Replace for bulk changes.
  • Selecting Multiple Fields: Click the first field header, hold the [Shift] key, and click the last field header to select a contiguous block.

Reports

Creating and Designing Reports

  • The Report Tool: This is the fastest method. Select the table or query in the Navigation Pane and click Create > Report. Access immediately generates a formatted summary of all fields.
  • Designing: While the tool creates a quick version, you can modify the appearance, layout, and sorting in Layout View or Design View to make it more professional.

Moving a Report to MS Word

You can export an Access report to Microsoft Word to perform further formatting or include it in a larger document.

  1. Select the report you wish to export in Access.
  2. Go to the External Data tab.
  3. In the Export group, click More and select Word from the drop-down.
  4. In the "Export - RTF File" window, browse to choose a save location.
  5. Click OK; the report will be saved in Rich Text Format (RTF), and Access will typically open the Word document automatically once finished.