Where can I find data records 1

Introduction to tables

Tables are important objects in a database because they contain any information or data. For example, a company's database may have a table of contacts that contains the names, email addresses, and phone numbers of suppliers. Because other database objects are so dependent on tables, when designing a database you should always create all tables first, and then all other objects. Before you start creating tables, you should have a thorough understanding of the requirements and determine which tables will be needed. For an introduction to planning and designing a database, see Database Design Basics.

Content of this article

Overview

In a relational database like Access, there are usually several related tables. In a well-designed database, each table contains data on a specific topic, such as: B. Employees or Articles. A table consists of records (rows) and fields (columns). The fields contain different data types, e.g. B. Text, numbers, dates and hyperlinks.

  1. 1. A data record: contains certain data, e.g. B. Information about a specific employee or product.

  2. 2. A field: Contains information about an aspect of the table entry, e.g. B. the first name or the e-mail address.

  3. A field value: Each record has a field value; B. "Contoso, Ltd." or [email protected]

Top of page

Table and field properties

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

1. Table properties

2. Field properties

In an Access database, table properties are attributes of a table that influence 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 use the Standard view a table to specify how the table is displayed by default.

A field property applies to a specific field in a table and defines one of the field's characteristics or an aspect of the field's behavior. Some field properties can be set in the datasheet view. You can also define any field properties in the design view in the Field properties area.

Data types

Each field has a data type. The data type of a field indicates the types of data that will be stored in the field, such as: B. large amounts of text or files as attachments.

A data type is a field property that differs from other field properties as follows:

  • You put the data type of a field in the table design grid, not in the scope Field properties firmly.

  • The data type of a field determines which other properties the field has.

  • You must specify the data type of a field when you create it.

    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 to the field based on the value you entered. If no other data type can be derived from your input, Access sets the data type to "Text". If necessary, you can change the data type using the ribbon.

Examples of automatic data type recognition

The following table shows how the automatic data type recognition works in the data sheet view.

input

Access creates a field with this data type:

Johann

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 recognized date and time formats correspond to those of your user locale.

Date / time

December 31, 2016

Date / time

10:50:23

Date / time

10:50

Date / time

17:50

Date / time

12,50 €

The recognized currency symbol corresponds to 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 on a different topic, most of the time tables in an Access database contain data on topics that are related to one another. A database can e.g. B. Include:

  • The table "Customers", in which the customers of the company are listed with their addresses

  • The Items table, listing the items you're selling, including prices and pictures of each item

  • The Orders table, where sales orders are tracked

Because you are storing data on different subjects in separate tables, you need a way to join the data so that you can easily combine related data in these separate tables. To do this, you set up 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

key

Fields that are part of a table relationship are called keys. A key usually consists of one field, but it can also consist of several fields. There are two types of keys:

  • Primary key A table can only have one primary key. A primary key consists of one or more fields that uniquely identify each record stored in the table. Often a unique identification number is used, e.g. B. an ID number, a serial number or a code, as a primary key. For example, you can have a table for customers in which each customer has a unique customer number. The field with the customer number is the primary key of the table "Customers". When a primary key contains multiple fields, it usually consists of pre-existing fields that collectively provide unique values. For example, you can use a combination of last name, first name, and date of birth as the primary key for a table of people. For more information, see Adding or Changing the Primary Key of a Table.

  • Foreign key A 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, suppose you have the Orders table, in which each order has a customer ID number that corresponds to a record in the Customers table. The Customer ID field is a foreign key to the Orders table.

Matching values ​​between key fields forms the basis of a table relationship that you can use to combine data in related tables. For example, suppose you are working with the Customers and Orders tables. In the "Customers" table, each record is identified by the primary key field "ID".

To associate each order with a customer, add a foreign key field to the Orders table that corresponds to the ID field in the Customers table, and then establish a relationship between the two keys. When adding a record to the Orders table, use a value for the customer ID that comes from the Customers table. To view information about a customer's order, use the relationship to determine which data in the Customers table corresponds to which records in the Orders table.

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

2. A foreign key - recognizable by the missing key symbol

Do not add a field if you expect each unique entity represented in the table to require more than one value for the field. Continuing with the previous example, if you want to start tracking your customers' orders, don't add a field to the table because multiple orders are associated with each customer. Instead, you create a new table to store the orders and then create a relationship between the two tables.

Top of page

Benefits of Relationships

Storing data separately in related tables has the following advantages:

  • consistency Since each data element is only stored once, and only in one table, the risk of ambiguities and inconsistencies is reduced. You save e.g. For example, the name of a customer only once (in the "Customers" table) instead of being repeatedly (and possibly inconsistently) stored in a table containing order information.

  • Efficiency Storing data in one place means less hard drive space is used. In addition, smaller tables usually offer faster data access than larger tables. Finally, if you are not working with separate tables for separate topics, you are introducing nulls (with missing data) and redundancies in your tables, which wastes disk space and degrades system performance.

  • Comprehensibility The design of a database is easier to understand when the topics are properly located in separate tables.

Take relationships into account when planning your tables. You can use the Lookup Wizard to create a foreign key field if the table that contains the associated primary key already exists. The lookup wizard sets up the relationship for you. For more information, see Create or Delete a Lookup Field.

Top of page

See also

Create a table and add fields