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

Tables are important objects in a database due to the fact that they hold all the details or data. Because that example, a database for a organization can have a contact table the stores the names of your suppliers, e-mail addresses, and telephone numbers. Because other database objects count so greatly on tables, you should constantly start your design of a database through creating every one of its tables and then creating any type of other objects. Before you develop tables, consider your requirements and also determine all the tables that you can need. Because that an development to planning and designing a database, check out 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 actually several associated tables. In a well-designed database, each table shop data around a certain subject, such together employees or products. A table has records (rows) and fields (columns). Areas have different types of data, such as text, numbers, dates, and hyperlinks.


A record: Contains certain data, choose information about a specific employee or a product.

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

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

height of web page

Table and field properties

Tables and fields likewise have properties the you can collection to control their attributes or behavior.


1. Table properties

2. Field properties

In an access database, table properties are characteristics of a table that affect the illustration or behavior of the table as a whole. Table properties are collection in the table"s home sheet, in design view. For example, girlfriend can set a table"s Default View residential or commercial property to specify how the table is presented by default.

A ar property uses to a details field in a table and defines among the field"s attributes or an facet of the field"s behavior. Girlfriend can set some ar properties in Datasheet view. You can also set any field property in style view by using the Field Properties pane.

Data types

Every ar has a data type. A field"s data form indicates the type of data that the field stores, such as huge amounts of message or fastened files.


A data kind is a ar property, yet it different from other field properties as follows:

You collection a field"s data kind in the table architecture grid, no in the Field Properties pane.

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

You must set a field"s data form when you create the field.

You can produce a brand-new field in access by beginning data in a brand-new column in Datasheet view. Once you produce a field by beginning data in Datasheet view, access automatically assigns a data type for the field, based upon the value that friend enter. If no other data form is comprise by your input, access sets the data form to Text. If needed, you can adjust the data form by using the Ribbon.

Examples of automatically data type detection

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

If girlfriend enter:

access creates a field with a data type of:



You have the right to use any valid net protocol prefix. Because that example, http://, https://, and mailto: room valid prefixes.



Number, lengthy Integer


Number, long Integer


Number, Double


Number, Double


The date and also time styles recognized room those of her user locale.


December 31, 2016




10:50 am





The currency symbol recognized is that of her user locale.



Number, Double


Number, Double


Number, Double

optimal of page

Table relationships

Although each table stores data about a various subject, tables in an accessibility database usually keep data around subjects the are pertained to each other. Because that example, a database can contain:

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

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

An assignment table that tracks customer orders.

Because you keep data around different subjects in different tables, you require some means to tie the data together so the you deserve to easily integrate related data native those separate tables. To affix the data save in various tables, you create relationships. A relationship is a logical connection in between two tables that specifies areas that the tables have actually in common. For an ext information, see overview to table relationships.

peak of web page


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

Foreign key A table can also have one or more foreign keys. A foreign key contains worths that exchange mail to values in the primary key of an additional table. For example, you could have an orders table in which each order has actually a client ID number that coincides to a record in a client table. The client ID field is a foreign an essential of the assignment table.

The post of worths between vital fields develops the basis of a table relationship. You usage a table connection to integrate data from associated tables. Because that example, mean that you have actually a client table and also an orders table. In her Customers table, each document is determined by the primary key field, ID.

To associate every order v a customer, you include a foreign crucial field to the orders table that coincides to the ID field of the customers table, and then create a relationship between the two keys. Once you include a record to the orders table, you use a worth for customer ID that originates from the customers table. Anytime you want to view any kind of information around an order"s customer, you use the relationship to recognize which data from the customers table coincides to which documents in the assignment table.


1. A main key, identified by the crucial icon alongside the ar name.

2. A foreign key — note the lack of the vital icon.

Do not add a field if you mean that each distinctive entity stood for in the table might require more than worth for the field. Proceeding the preceding example, if you desire to begin tracking orders placed by your customers, you perform not add a field to the table, since each customer will certainly have much more than one order. Instead, you create a new table to save orders, and then produce a relationship in between the two tables.

optimal of web page

Benefits of making use of relationships

Keeping data be separated in associated tables to produce the following benefits:

Consistency since each article of data is recorded just once, in one table, there is much less opportunity for ambiguity or inconsistency. Because that example, you store a customer"s name only once, in a table around customers, rather than storing it consistently (and perhaps inconsistently) in a table that consists of order data.

Efficiency recording data in only one place method you use much less disk space. Moreover, smaller sized tables tend to administer data an ext quickly than larger tables. Finally, if girlfriend don"t use separate tables for separate subjects, friend will introduce null values (the absence of data) and also redundancy into your tables, both that which can waste space and impede performance.

Comprehensibility The architecture of a database is simpler to know if the topics are properly separated into tables.

See more: Junkyard Gem: 2001 Pontiac Grand Prix Gtp Supercharged Sedan

Plan her tables v relationships in mind. You deserve to use the Lookup sorcerer’s to produce a foreign vital field if the table that has the corresponding primary crucial already exists. The Lookup Wizard create the connection for you. For more information, see produce or delete a lookup field.