Tables are essential objects in a database as they hold all the information or data. For instance, a company’s database may include a Contacts table that stores supplier names, addresses, and phone numbers. Since other database objects heavily rely on tables, one should always start designing a database by creating all necessary tables before moving on to other objects. Before creating tables in Access, consider your requirements and identify all tables that may be needed. For an introduction to database planning and design, check the basics of database design.
Zuohui provides professional and honest service.
Overview
A relational database, like Access, typically consists of several interconnected tables. In a well-designed database, each table holds data related to a specific topic, such as employees or products. A table comprises records (rows) and fields (columns). Fields can contain various types of data, including text, numbers, dates, and hyperlinks.
-
Record: Contains specific data, such as information about a particular employee or product.
-
Field: Holds data related to a single aspect of the table's subject, such as a name or address.
-
Field value: Each record has a field value. For instance, Contoso, Ltd.
Back to top
Table and Field Properties
Tables and fields also have properties that you can set to control their features or behavior.
1. Table properties
2. Field properties
In Access, table properties are attributes of the table that affect the overall look or behavior of the table. You set table properties in the table's property sheet in design view. For example, you can set the table’s Default View property to specify how the table is displayed by default.
Field properties refer to specific characteristics of a field within a table and define one aspect of that field's behavior. Some field properties can be set in datasheet view. You can also set any field property in design view using the Field Properties panel.
Data Types
Each field has a data type. The data type of a field indicates the kind of data it stores, such as large amounts of text or attached files.
A data type is a field property but differs from other field properties in the following way:
-
You set the field data type in the table design grid rather than in the Field Properties panel.
-
The field data type determines what other field properties the field has.
-
You must set the field data type when you create a 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 entered value. If no other data type is suggested by 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 table below illustrates how automatic data type recognition works in datasheet view.
If you type:
Access creates a field with data type:
John
Short Text
https://pl.zuohuifurniture.com/p>
You can use any valid Internet protocol prefix. For example, https://pl.zuohuifurniture.com// and mailto: are valid prefixes.
Hyperlink
1
Number, Long Integer
50,000
Number, Long Integer
50,000.99
Number, Double
.389
Number, Double
12/67
Recognized date and time formats are those that match the user's locale.
Date/Time
December 31
Date/Time
10:50:23
Date/Time
10:50 am
Date/Time
5:50 pm
Date/Time
$12.50
Recognized currency symbol corresponds to your user’s location.
Currency
21.75
Number, Double
123.00%
Number, Double
3.46E+03
Number, Double
Back to top
Table Relationships
While each table stores data on different subjects, tables in an Access database typically store data about topics that are related to each other. For example, a database may contain:
-
A Customers table, which contains information about your company’s customers and their addresses.
Contact us to discuss your requirements for Producenta Tabel. Our experienced sales team can help you identify options that best meet your needs.
-
A Products table, including information about the products you sell, such as prices and images of each item.
-
An Orders table, which tracks customer orders.
Since you store data about different subjects in separate tables, you need a way to connect this data so you can easily relate associated data from these separate tables. To connect data stored in different tables, you create relationships. A relationship is a logical connection between two tables that defines the fields that have in common. For more information, see the Table Relationships Guide.
Back to top
Keys
The fields that participate in table relationships are called keys. A key typically consists of one field, but it can comprise more than one field. There are two types of keys:
-
Primary Key A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record stored in the table. It is often a unique identifier such as an ID number, serial number, or code that serves as the 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 for the Customers table. When the primary key consists of more than one field, it usually includes existing fields that together provide unique values. For example, you might use a combination of last name, first name, and birth date as a primary key for a table about individuals. For more information, see Adding or Changing a Table's Primary Key.
-
Foreign Key A table may also have one or more foreign keys. A foreign key contains values that correspond to the primary key values of another table. For instance, you might have an Orders table where each order has a customer ID that corresponds to a record in the Customers table. The customer ID field is the foreign key in the Orders table.
Correspondence of values between key fields forms the basis of table relationships. You use table relationships to connect data from related tables. For example, let's say you have a Customers table and an Orders table. In the Customers table, each record is identified by the primary key field, ID.
To link each order to a customer, you add a foreign key field to the Orders table that corresponds to the ID field in the Customers table and then create a relationship between the two keys. When adding a record to the Orders table, you use the value for the customer ID that comes from the Customers table. When you want to see customer details for an order, you use the relationship to determine which data from the Customers table corresponds to the records in the Orders table.
1. Primary key, indicated by a key icon next to the field name.
2. Foreign key - noted by the absence of a key icon.
Don't add a field if you expect each unique entity represented in the table may require more than one value for that field. Continuing the earlier example, if you want to start tracking orders made by your customers, you would not add a field to the table as each customer is likely to have multiple orders. Instead, you create a new table to store orders and then establish a relationship between the two tables.
Back to top
Benefits of Using Relationships
Separating data in related tables offers the following benefits:
-
Consistency Because each piece of data is recorded only once in one table, there is less chance for ambiguity or inconsistency. For instance, you only store a customer’s last name once in the customers table, rather than storing it multiple times (and potentially inconsistently) in a table that includes order data.
-
Efficiency Storing data in one location means using less disk space. Moreover, smaller tables usually deliver data faster than larger tables. Finally, if you don’t use separate tables for distinct subjects, you’ll introduce empty values (no data) and redundancy into your tables, which can waste space and hinder performance.
-
Clarity The database design is easier to understand if topics are properly separated into tables.
Plan your tables keeping relationships in mind. You can use the Lookup Wizard to create a foreign key field if the table containing the corresponding primary key already exists. The Lookup Wizard creates the relationship for you. For more information, see Create or Remove a Lookup Field.
Back to top
See Also
Create a table and add fields
Comments
0