Your shopping cart is empty!
A table is a database object that you use to store data about a particular subject, such as employees or products. A table consists of records and fields.
Each record contains data about one instance of the table subject, such as a particular employee. A record is also commonly called a row or an instance.
Each field contains data about one aspect of the table subject, such as first name or e-mail address. A field is also commonly called a column or an attribute.
A record consists of field values, such as Contoso, Ltd. or firstname.lastname@example.org. A field value is also commonly called a fact.
A database can contain many tables, each storing information about a different subject. Each table can contain many fields of different types of data, such as text, numbers, dates, and hyperlinks.
You can link to a variety of external data sources, such as other databases, text files, and Excel workbooks. When you link to external data, Access can use the link as if it were a table. Depending on the external data source and the way that you create the link, you can edit the data in the linked table, and can create relationships that involve the linked table. However, you cannot change the design of the external data by using the link.
Tables and fields have properties that you can set to control their characteristics or behavior.
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.
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.
A data type is a field property, but it differs from other field properties as follows:
Note 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, part of the new Microsoft Office Fluent user interface.
Although each table stores data about a different subject, tables in a database usually store data about subjects that are related to each other. For example, a database might contain:
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.
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:
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.
Keeping data separated in related tables produces the following benefits:
Access creates relational databases, which means that data is stored in various separate tables by subject or task, but the data is related and can be brought together in ways that you specify. Even though a club's database might store member contact information separately from its lists of recycling volunteers or holiday planning data, the database can pull all this information together whenever you want. So, you could quickly print a list of who's volunteered to recycle newspapers this Saturday, along with their up-to-date addresses and phone numbers. The two sets of data are relational, so that information in one set of data (such as Nancy Davolio's name on the recycling list) is associated with, or "knows about," the applicable information in the other set of data (Nancy Davolio's contact information). To make the most of your database, you'll want to set up the tables of data to reflect the subjects and tasks associated with your data. While planning your database, consider the scenarios in which people will be entering data, looking up data, or reporting data. A little forethought can go a long way.
Objects are the most important parts of a database. Access databases consist of objects. Later in this course, we'll describe the following four important objects in more detail: Tables store your data in rows and columns. All databases contain one or more tables. Queries retrieve and process your data. They can combine data from different tables, update your data, and perform calculations on your data. Forms control data entry and data views. They provide visual cues that make data easier to work with. Reports summarize and print your data. They turn the data in your tables and queries into documents for communicating ideas.
All databases in Access contain at least one table. In this lesson, we'll show you what a table is made of, and how you can structure tables to fit your data.