Access because that 365 accessibility 2021 access 2019 access 2016 access 2013 accessibility 2010 accessibility 2007 More...Less

Tables are crucial objects in a database due to the fact that they hold all the details or data. Because that example, a database for a company can have actually a contact table the stores the name of their suppliers, e-mail addresses, and telephone numbers. Because other database objects rely so greatly on tables, girlfriend should always start your architecture of a database by creating all of its tables and also then creating any other objects. Before you develop tables, think about your requirements and determine all the tables the you can need. For an development to planning and designing a database, watch Database architecture basics.

You are watching: What is the difference between a record and a field

In this article


A relational database like accessibility usually has several associated tables. In a well-designed database, every table shop data about a specific subject, such as employees or products. A table has actually records (rows) and also fields (columns). Areas have different types of data, such as text, numbers, dates, and also hyperlinks.


A record: Contains specific data, prefer information about a details employee or a product.

A field: has data about one aspect of the table subject, such as an initial name or e-mail address.

A ar value: each record has actually a ar value. For example, Contoso, Ltd. Or someone

peak of web page

Table and also field properties

Tables and also fields additionally have properties the you can collection to regulate their characteristics or behavior.


1. Table properties

2. Field properties

In an accessibility database, table nature are attributes of a table that affect the figure or habits of the table together a whole. Table nature are set in the table"s home sheet, in style view. Because that example, friend can collection a table"s Default View home to specify just how the table is shown by default.

A ar property applies to a specific field in a table and also defines among the field"s characteristics or an aspect of the field"s behavior. Friend can set some field properties in Datasheet view. You have the right to also set any field property in architecture view by making use of the Field Properties pane.

Data types

Every field has a data type. A field"s data kind indicates the kind of data that the field stores, such as big amounts of text or fastened files.


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

You collection a field"s data type in the table style grid, not in the Field Properties pane.

A field"s data type determines what various other properties the field has.

You must collection a field"s data type when you produce the field.

You can produce a new field in accessibility by start data in a new column in Datasheet view. When you create a ar by entering data in Datasheet view, accessibility automatically assigns a data kind for the field, based on the worth that you enter. If no other data kind is comprise by your input, access sets the data type to Text. If needed, girlfriend can readjust the data kind by using the Ribbon.

Examples of automatic data form detection

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

If friend enter:

accessibility creates a field with a data form of:



You deserve to use any type of valid web protocol prefix. For example, http://, https://, and mailto: space valid prefixes.



Number, lengthy Integer


Number, long Integer


Number, Double


Number, Double


The date and also time layouts recognized room those of your user locale.


December 31, 2016




10:50 am





The money symbol recognized is the of your user locale.



Number, Double


Number, Double


Number, Double

height of web page

Table relationships

Although every table shop data about a various subject, tables in an accessibility database usually save data about subjects that are pertained to each other. For example, a database might contain:

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

A commodities table the lists the commodities that friend sell, including prices and also pictures because that each item.

An assignment table the tracks customer orders.

Because you save data about different topics in different tables, you need some method to tie the data with each other so that you have the right to easily incorporate related data native those different tables. To affix the data stored in various tables, you create relationships. A connection is a reasonable connection between two tables that specifies fields that the tables have actually in common. For an ext information, see overview to table relationships.

height of web page


Fields the are part of a table connection are called keys. A an essential usually is composed of one field, but may consist of an ext than one field. There are two kinds of keys:

Foreign key A table can additionally have one or more foreign keys. A foreign key contains worths that exchange mail to values in the primary vital of one more table. For example, you might have an assignment table in which every order has actually a customer ID number that synchronizes to a document in a client table. The customer ID ar is a foreign crucial of the assignment table.

The post of worths between key fields forms the basis of a table relationship. You usage a table connection to combine data from related tables. For example, mean that you have actually a customers table and also an orders table. In your Customers table, each document is figured out by the primary crucial field, ID.

To associate every order through a customer, you include a foreign an essential field to the assignment table that coincides to the ID field of the client table, and also then produce a relationship in between the 2 keys. When you include a record to the orders table, you use a worth for customer ID that comes from the client table. At any time you want to view any kind of information around an order"s customer, you use the relationship to identify which data indigenous the client table corresponds to which documents in the order table.


1. A major key, figured out by the key icon next to the field name.

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

Do not include a field if you mean that each distinctive entity stood for in the table could require an ext than value for the field. Proceeding the preceding example, if you want to begin tracking orders inserted by her customers, you do not add a field to the table, since each customer will have much more than one order. Instead, you create a brand-new table to store orders, and also then create a relationship between the two tables.

top of page

Benefits of using relationships

Keeping data separated in related tables produces the complying with benefits:

Consistency due to the fact that each items of data is recorded just once, in one table, there is less opportunity for ambiguity or inconsistency. Because that example, you save a customer"s name only once, in a table about customers, fairly than storing it repeatedly (and possibly inconsistently) in a table that consists of order data.

Efficiency record data in just one place means you use less disk space. Moreover, smaller tables tend to carry out data an ext quickly than larger tables. Finally, if friend don"t use separate tables for different subjects, friend will introduce null worths (the absence of data) and redundancy into your tables, both that which have the right to waste room and impede performance.

Comprehensibility The style of a database is less complicated to understand if the subjects are appropriately separated into tables.

See more: How Many Ounces Are In 1 Ton S To Ounces Conversion, Convert Ton To Oz

Plan her tables v relationships in mind. You can use the Lookup magician to develop a foreign vital field if the table that includes the equivalent primary crucial already exists. The Lookup Wizard creates the connection for you. For much more information, see produce or delete a lookup field.