Introduction to tables - Microsoft Support (2023)

Tables are essential objects in a database because they hold all the information or data. For example, a database for a business can have a Contacts table that stores the names of their suppliers, e-mail addresses, and telephone numbers. Because other database objects depend so heavily on tables, you should always start your design of a database by creating all of its tables and then creating any other objects. Before you create tables, consider your requirements and determine all the tables that you might need. For an introduction to planning and designing a database, see Database design basics.

In this article

  • Overview

  • Table and field properties

  • Data Types

  • Table relationships

  • Keys

  • Benefits of using relationships

Overview

A relational database like Access usually has several related tables. In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks.

Introduction to tables - Microsoft Support (1)

  1. A record: Contains specific data, like information about a particular employee or a product.

  2. A field: Contains data about one aspect of the table subject, such as first name or e-mail address.

    (Video) Microsoft Word Tutorial: Using Tables in Word

  3. A field value: Each record has a field value. For example, Contoso, Ltd. or someone@example.com.

Top of Page

Table and field properties

Tables and fields also have properties that you can set to control their characteristics or behavior.

Introduction to tables - Microsoft Support (2)

1. Table properties

2. Field properties

In an Access database, table properties are attributes of a table that affect the appearance or behavior of the table as a whole. Table properties are set in the table's property sheet, in Design view. For example, you can set a table's Default View property to specify how the table is displayed by default.

A field property applies to a particular field in a table and defines one of the field's characteristics or an aspect of the field's behavior. You can set some field properties in Datasheet view. You can also set any field property in Design view by using the Field Properties pane.

Data types

Every field has a data type. A field's data type indicates the kind of data that the field stores, such as large amounts of text or attached files.

Introduction to tables - Microsoft Support (3)

A data type is a field property, but it differs from other field properties as follows:

(Video) Introduction to Microsoft Excel Tables

  • You set a field's data type in the table design grid, not in the Field Properties pane.

  • A field's data type determines what other properties the field has.

  • You must set a field's data type when you create the field.

    You can create a new field in Access by entering data in a new column in Datasheet view. When you create a field by entering data in Datasheet view, Access automatically assigns a data type for the field, based on the value that you enter. If no other data type is implied by your input, Access sets the data type to Text. If needed, you can change the data type by using the Ribbon.

Examples of automatic data type detection

The following table shows how automatic data type detection works in Datasheet view.

If you enter:

Access creates a field with a data type of:

John

Text

http://www.contoso.com

You can use any valid Internet protocol prefix. For example, http://, https://, and mailto: are valid prefixes.

Hyperlink

1

Number, Long Integer

50,000

Number, Long Integer

50,000.99

Number, Double

50000.389

Number, Double

12/67

The date and time formats recognized are those of your user locale.

Date/Time

December 31, 2016

Date/Time

10:50:23

Date/Time

10:50 am

Date/Time

17:50

Date/Time

$12.50

The currency symbol recognized is that of your user locale.

Currency

21.75

Number, Double

123.00%

Number, Double

3.46E+03

Number, Double

Top of Page

Table relationships

Although each table stores data about a different subject, tables in an Access database usually store data about subjects that are related to each other. For example, a database might contain:

  • A customers table that lists your company’s customers and their addresses.

    (Video) Introduction to Microsoft Dataverse in Power Apps | Build Tables & Relationships | Beginners Guide

  • A products table that lists the products that you sell, including prices and pictures for each item.

  • An orders table that tracks customer orders.

Because you store data about different subjects in separate tables, you need some way to tie the data together so that you can easily combine related data from those separate tables. To connect the data stored in different tables, you create relationships. A relationship is a logical connection between two tables that specifies fields that the tables have in common. For more information, see Guide to table relationships.

Top of Page

Keys

Fields that are part of a table relationship are called keys. A key usually consists of one field, but may consist of more than one field. There are two kinds of keys:

  • Primary keyA table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people. For more information, see adding or changing a table’s primary key.

  • Foreign keyA table can also have one or more foreign keys. A foreign key contains values that correspond to values in the primary key of another table. For example, you might have an Orders table in which each order has a customer ID number that corresponds to a record in a Customers table. The customer ID field is a foreign key of the Orders table.

The correspondence of values between key fields forms the basis of a table relationship. You use a table relationship to combine data from related tables. For example, suppose that you have a Customers table and an Orders table. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a foreign key field to the Orders table that corresponds to the ID field of the Customers table, and then create a relationship between the two keys. When you add a record to the Orders table, you use a value for customer ID that comes from the Customers table. Whenever you want to view any information about an order's customer, you use the relationship to identify which data from the Customers table corresponds to which records in the Orders table.

Introduction to tables - Microsoft Support (4)

1. A primary key, identified by the key icon next to the field name.

(Video) The Beginner's Guide to Excel - Excel Basics Tutorial

2. A foreign key— note the absence of the key icon.

Do not add a field if you expect that each unique entity represented in the table might require more than value for the field. Continuing the preceding example, if you want to start tracking orders placed by your customers, you do not add a field to the table, because each customer will have more than one order. Instead, you create a new table to store orders, and then create a relationship between the two tables.

Top of Page

Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

  • ConsistencyBecause each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer's name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.

  • EfficiencyRecording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don't use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.

  • ComprehensibilityThe design of a database is easier to understand if the subjects are properly separated into tables.

Plan your tables with relationships in mind. You can use the Lookup Wizard to create a foreign key field if the table that contains the corresponding primary key already exists. The Lookup Wizard creates the relationship for you. For more information, see Create or delete a lookup field.

Top of Page

Create a table and add fields

(Video) How to use Microsoft Dataverse - Create your first table and columns

FAQs

What is the role of tables in database answer briefly? ›

Tables are used to organize data that is too detailed or complicated to be described adequately in the text, allowing the reader to quickly see the results. They can be used to highlight trends or patterns in the data and to make a manuscript more readable by removing numeric data from the text.

What are the 4 main objects of a database? ›

A database is a collection of information that is related. Access allows you to manage your information in one database file. Within Access there are four major objects: Tables, Queries, Forms and Reports.

What are the two views you can use to create a table in Access? ›

The two most important views are:
  • Datasheet View allows you to enter information into your database. It is in a table format similar to Excel.
  • Design View allows you to setup and edit the fields of your database.

What is the primary key? ›

A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table. Optim uses primary keys that are defined to the database.

What is the main function of tables in Microsoft Access? ›

Tables and relationships

To store your data, you create one table for each type of information that you track. Types of information might include customer information, products, and order details. To bring the data from multiple tables together in a query, form, or report, you define relationships between the tables.

What are three uses of table? ›

A table is an item of furniture with a raised flat top and is supported most commonly by 1 or 4 legs (although some can have more), used as a surface for working at, eating from or on which to place things.

Videos

1. Microsoft Access 2016 Tutorial: Linking the Tables and Defining the Relationships
(Kaceli TechTraining)
2. Learn Pivot Tables in Microsoft Excel | Full Introduction
(The Office Lab)
3. Microsoft Access 2007 2010 part 1 (Tables)
(Amir Parmar)
4. Learn Pivot Tables in 6 Minutes (Microsoft Excel)
(Cody Baldwin)
5. Microsoft Word for Beginners - The Complete Course
(Technology for Teachers and Students)
6. Creating Tables | Microsoft Access | Study Help For All
(Study Help For All)
Top Articles
Latest Posts
Article information

Author: Carlyn Walter

Last Updated: 11/27/2022

Views: 6172

Rating: 5 / 5 (50 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Carlyn Walter

Birthday: 1996-01-03

Address: Suite 452 40815 Denyse Extensions, Sengermouth, OR 42374

Phone: +8501809515404

Job: Manufacturing Technician

Hobby: Table tennis, Archery, Vacation, Metal detecting, Yo-yoing, Crocheting, Creative writing

Introduction: My name is Carlyn Walter, I am a lively, glamorous, healthy, clean, powerful, calm, combative person who loves writing and wants to share my knowledge and understanding with you.