Database Essentials: Understanding Tables, Fields, and Queries

Database Components: Fields and Records

What is a Table?

Tables are the most fundamental objects in a database. They are used to store data organized into specific categories, structured in rows and columns.

What is a Field?

A field is the most basic unit of a database, representing a single piece of data. The names of fields cannot start with a space or special characters. A collection of fields for a single item is called a record.

Common Database Field Types

Different types of fields are used to store various kinds of data:

• Text: For alphanumeric characters, names, and descriptions up to 255 characters.
• Memo (Long Text): Used to store text longer than 255 characters with rich formatting options. It is ideal for notes, long explanations, and formatted paragraphs.
• Number: For numeric data used in mathematical calculations.
• Currency: For monetary values to prevent rounding errors in calculations.
• AutoNumber: A unique sequential number (incrementing by one) or a random number that Microsoft Access assigns every time a new record is added.
• Yes/No: For boolean data that can only have one of two values (e.g., True/False, Yes/No).
• OLE Object: For linking or embedding objects like images, documents, or spreadsheets.
• Hyperlink: For storing links to websites, files, or email addresses.

Primary and Foreign Keys Explained

A foreign key is a field in one table that is used to reference the primary key of another table. This relationship is essential for linking data across tables and avoiding data redundancy.

Working with Database Queries

What is a Query?

A query is a request for data from a database. Unlike simple commands, queries can organize, filter, and extract data from multiple tables or other queries and can be saved for future use. A select query is the most common type in Access, used to retrieve and display data.

Query Criteria and Calculated Fields

• A query criterion is a rule or condition used to identify and filter the specific records you wish to include in the query results.
• A calculated field is a column in a query that displays the result of an expression rather than data stored directly in a table.

Database Templates and Setup

Using Predefined Templates

Templates are predefined databases designed with a user's needs in mind. They provide a ready-made structure for common elements like contacts, issues, and tasks.

Starting a New Database

When creating a new database, the "Getting Started" page typically provides a panel where you can name your database file and initiate its creation.

Database Management and Integration

Upgrading and Compacting Databases

Microsoft Access allows you to update databases from previous versions. Converting to a newer format offers several advantages to make the database safer, smaller, and easier to recover if needed:

• Creates a more secure database.
• Reduces the file size, making it more compact.
• Improves the reliability of data recovery.

Importing and Linking External Data

You can connect an Access database to data from various external sources, including:

• SharePoint lists
• Microsoft Word files
• Other Access databases
• Text files
• Microsoft Excel documents

Note: When you link to an external file, such as an Excel spreadsheet, any changes made to the data in the source file will be reflected in the linked table in Access.

Split Database Architecture

A database can be divided into two files for better performance and management:

• Back-End File (Server File): This file contains only the data tables.
• Front-End File (Client File): This file contains all other database objects, such as forms, reports, and queries, and links to the tables in the back-end file.