3.1.1 Overview of Access Objects: Tables

In Microsoft Access, the concept of objects is central to how data is organized and interacted with in a database. Tables are one of the most fundamental objects in Access. They are used to store data in a structured format and serve as the backbone for any database system. Understanding how tables work is key to using Access effectively, as they hold the actual data that other objects like queries, forms, and reports interact with.

What Are Tables in Access?

A table in Access is a collection of data organized into rows and columns, where each row represents a record (an individual entry), and each column represents a field (a specific data attribute).

Access tables allow you to manage and store data in a relational database format, ensuring that information is stored efficiently and can be retrieved for further analysis or reporting. Tables serve as the primary storage location for all data in the database and are related to each other through keys (Primary Keys, Foreign Keys).

Key Components of a Table:

1. Fields (Columns):
   • A field is a single data element in a table. It represents a specific type of information about each record.
   • Each field in a table is given a name and a data type (e.g., Text, Number, Date/Time).
   • Example: In a Customers table, fields could include CustomerID, FirstName, LastName, Email, etc.
2. Records (Rows):
   • A record is a complete set of related data that belongs to a particular entity.
   • A record consists of values that correspond to the fields in the table.
   • Example: In the Customers table, a record would store data for a specific customer, such as CustomerID = 101, FirstName = John, LastName = Doe, Email = johndoe@example.com.
3. Primary Key:
   • A Primary Key is a field (or a combination of fields) that uniquely identifies each record in a table.
   • It ensures that no two records in the table can have the same value in this field.
   • Example: In the Customers table, CustomerID might be the primary key.
4. Foreign Key:
   • A Foreign Key is a field in a table that creates a link between two tables.
   • It refers to the Primary Key in another table, establishing a relationship between the tables.
   • Example: In an Orders table, the field CustomerID could be a foreign key referring to the CustomerID field in the Customers table.
5. Data Types:
   • Tables in Access can store different types of data, and each field has a defined data type that determines the kind of data it can store.
   • Some common data types in Access include:
     • Text: Stores alphanumeric data (e.g., names, addresses).
     • Memo: Stores larger text data (e.g., descriptions, comments).
     • Number: Stores numeric data (e.g., prices, quantities).
     • Date/Time: Stores date and time values.
     • Currency: Stores monetary values.
     • Yes/No: Stores Boolean data (True/False).
     • AutoNumber: Automatically generates a unique number for each record (commonly used for Primary Keys).

Creating and Designing Tables:

1. Creating a Table:
   • You can create tables in Access by either using the Table Design view or the Datasheet view.
     • Table Design View: Allows you to manually define the table structure, including specifying field names, data types, and setting a primary key.
     • Datasheet View: Allows you to quickly enter data directly into a grid format (like an Excel spreadsheet), but Access automatically creates fields based on the data entered.
2. Table Design View:
   • In Design View, you can define each field’s name, data type, and set additional properties such as Field Size, Format, and Default Value.
   • You can also define the Primary Key and other field properties (e.g., if a field is required or indexed).
3. Datasheet View:
   • In Datasheet View, you can add records directly and quickly enter data, but the table structure is automatically determined by the first row of data entered.
   • This view is ideal for data entry but lacks the full control available in Design View.

Table Relationships:

• Relationships between Tables allow Access to maintain the integrity of the data and link data from multiple tables together. A common practice is to design tables in a relational format where:
  • One table holds the data (e.g., Customers table).
  • Another table holds related data (e.g., Orders table).
  • The related data is linked by a Foreign Key.
• Types of Relationships:
  1. One-to-One (1:1): Each record in Table A has one related record in Table B.
  2. One-to-Many (1:M): One record in Table A is related to multiple records in Table B (most common relationship).
  3. Many-to-Many (M:M): Multiple records in Table A are related to multiple records in Table B, typically implemented through a third table (junction table).

Managing Tables:

• Sorting and Filtering: You can sort and filter data in a table to display only the records that meet specific criteria. This is especially useful when working with large datasets.
• Updating and Deleting Records: You can update or delete records directly in a table using the datasheet or through queries.
• Indexing: Indexes are used to speed up searches on a table, especially for fields that are frequently used in search queries.

Normalization:

• Normalization is the process of organizing data into tables to minimize redundancy and improve data integrity.
  • The goal is to divide large tables into smaller ones, ensuring that each table represents a single concept (e.g., Customers, Orders).
  • There are several normal forms (1NF, 2NF, 3NF) that guide the process of normalization, ensuring that data is stored efficiently and avoids inconsistencies.

Conclusion:

Tables are the core structure for storing data in Access. They organize data in rows and columns and are the foundation for building relationships between different data entities. By creating and managing tables effectively, you ensure that the database is structured in a way that allows for efficient querying, reporting, and data management. Understanding tables and their properties is essential for building a robust Access database.