Microsoft Access Databases: A Comprehensive Overview

Posted on Nov 14, 2024 in Computers

Microsoft Access: Databases and Information Management

Access is a software application used to create databases and manage information. It allows you to manipulate data in tables, create relationships between them, design queries, create forms for data entry, and generate reports for presenting information. The file extension for Access databases is .accdb.

Key Components of the Access Interface

  • Title Bar: Displays the program name and the open file. Includes minimize, maximize, and close buttons.
  • Quick Access Toolbar: Provides shortcuts to common operations like saving, redoing, or undoing actions.
  • Ribbon: Organizes program options and tabs by category, with buttons and menus.
  • File Tab: Offers program options and immediate commands.
  • Status Bar: Located at the bottom, it provides information about the current window or view.

Database Objects

  • Tables: Store information on a specific topic. Tables contain fields, which store data such as names, addresses, etc. A data set for the same object in a table is called a record (or row).
  • Queries: Provide customized views of data from tables, allowing you to extract information that meets specific conditions.
  • Forms: Used to view, enter, and modify data in tables.
  • Reports: Format, calculate, and summarize data, typically for printing.

Design View

Design View allows you to define the columns (fields) of tables and other properties. It displays the table name and a grid where columns are defined. The “General” and “Lookup” tabs define field properties, with a help box on the right. Row 1 enters the field name, row 2 the data type (default is Text), and row 3 an optional description.

Field Types

  • Text: Stores characters, digits, and special characters (e.g., names, addresses).
  • Memo: For text longer than 255 characters (e.g., explanations, comments).
  • Number: For numerical data used in mathematical calculations.
  • Date/Time: For entering dates and times.
  • Currency: For monetary values and numerical data used in calculations, with up to 4 decimal places.
  • Auto Number: A sequential number automatically assigned by Access for each new record.
  • Yes/No: For fields with two possible values (e.g., yes/no, true/false, on/off).
  • OLE Object: For binary data (e.g., Excel spreadsheet, Word document).
  • Hyperlink: For web addresses or links to other files.
  • Attachment: For files, images, graphics, spreadsheets. Similar to email attachments.
  • Calculated: Its value is the result of an arithmetic or logic operation performed with data from other fields.

Query By Example (QBE) Grid

  • Field: Specifies the field to use in the query.
  • Table: Shows the table the field belongs to.
  • Sort: Specifies the sort order for rows.
  • Show: Controls whether the field is displayed in the query results.
  • Criteria: Specifies the conditions that records must meet to be included in the query results.
  • Or: Used to combine multiple criteria.

Forms

Forms are used to create user-friendly interfaces for viewing and editing records in a table or query. The Form Wizard simplifies form creation.

Field Properties

  • Primary Key: Uniquely identifies each record in a table.
  • Field Size: Determines the maximum number of characters for text fields or the data type for numeric fields.
  • Format: Customizes the way data is displayed.
  • Decimal Places: Specifies the number of decimal places for numeric fields.
  • Input Mask: Controls the values users can enter.
  • Caption: Specifies the field label.
  • Default Value: A value automatically entered if no other value is provided.
  • Validation Rule: Specifies criteria for data entry.
  • Validation Text: Displays a message if the validation rule is violated.
  • Required: Specifies whether the field is mandatory.
  • Allow Zero Length: Controls whether empty values are allowed.
  • Indexed: Creates an index for faster searching.

Relationships

Relationships link tables together, enabling simultaneous use of data from multiple tables. Types of relationships include:

  • One-to-One: One record in a table relates to only one record in another table.
  • One-to-Many: One record in a table can relate to multiple records in another table, but each record in the related table can only relate to one record in the first table.
  • Many-to-Many: Records in both tables can relate to multiple records in the other table.

Types of Queries

  • Select Query: Extracts data meeting specific criteria.
  • Action Query: Makes changes to records (Delete, Update, Append, Make-Table).
  • SQL Query: Written in SQL language.
  • Parameter Query: Prompts the user for input when run.
  • Crosstab Query: Summarizes data in a grid format.

Reports

Reports present data from tables or queries, usually for printing. The Report Wizard simplifies report creation.