4 Tables

OBJECTIVES

Organization of tables and relationships between tables.
The different ways to create tables.
Properties of fields.
Validation of data input through input masks and validation rules.
Understanding referential integrity.

4.1 About tables

In a database, data is stored in tables. The tables themselves consists of fields for different kind of data. So there could be a field for the last name of a person and another field for the first name. Data can be displayed in different ways but is always stored in tables. In practice, you start with making an information analysis to determine which tables and fields you need. For larger databases, this is done through a process called normalizing. Mostly you also need to establish relationships between the tables, so data from one table can be linked to data in another table.

With empty tables, you can’t do anything. So data has to be entered in tables. When these data are already available in other files, such as an Excel worksheet, then import from Excel is also an option.

4.2 Fields in tables

Fields are the design elements of a table. Data is stored in fields. The properties of a field define the characteristics and behavior of the data that is stored in the field. To create a field you need:

Field name (required)
Data type (required
Description (optional)

4.2.1 Field names

The design of the database determines what kind of data has to be stored in the field. Usually, the fields in the design have already been given a name and you are using that name. Always use meaningful names. Of course, you can give the field a description, but a clear name still works best.

Field names must meet the following requirements:

The name must be unique within the table.
The name may not start with a space.
The name may not contain the following characters: point, exclamation mark, and brackets [ and ].

4.2.2 Data types

The different data types, their possible values, and use.

The main characteristic of a field is the data type because it determines what type of data can be in the field. Data types can sometimes be confusing. For example, a field ’s data type Text can contain both text and numbers (numeric data ), but a field of data type Number can contain only numeric data. If you don’t know which of these two data types to use, think of the following rule of thumb.

When you have to make calculations with the content of the field, then use data type Number. Thus, the field for the price of an article is a numeric field and the field for an article number is a text field.

The data types in Access are:

Table 4.1: Data types in Access

Data Types	Values	Explanation
`Short Text`	Alphanumeric	Short, alphanumeric values with a maximum of 255 characters
`Long Text`	Alphanumeric	Long amounts of text, up to 1GB. In earlier versions named memo.
`Number`	Numeric	Range of -2³¹ tot 2³¹ - 1.
`Large Number`	Numeric	Range of -2⁶³ tot 2⁶³ - 1.
`Date/Time`	Dates and Times
`Currency`	Monetary values	Stored with 4 decimal places of precision.
`AutoNumber`	Numbers (unique)	Numbers that are automatically generated for each record. Default is the field length Long Integer. AutoNumber is often used as a key field.
`Yes/No`	Yes or No	Boolean values, only two values possible Yes or No. In the background, Access uses the value -1 for all Yes-values and the value 0 for all No-values. You also use this field for values as TRUE / FALSE, or ON / OFF.
`OLE Object`	Objects from other Windows applications	Photos, graphs, Excel worksheet, Word document, …
`Hyperlink`	Hyperlink	Hyperlinks (also e-mail addresses)
`Attachment`	Path and name of a file	Files, such as digital photos. Multiple files can be attached per record.
`Calculated`	different	An expression that uses data from one or more fields.

In Access, you can set a field’s data type to Lookup Wizard. Doing this starts the Lookup Wizard, which helps you create a Lookup field. A Lookup field displays either a list of values that is retrieved from a table or query, or it displays a static set of values that you specified when you created the field.

4.2.3 Field Properties

You can set additional field properties at a data type. The data type of the field determines which properties can be set. The most important and most frequently used properties are:

Field Size: Important at Text, Number, and sometimesAutoNumber. For a text field, you can specify the maximum length of the text. For a number field, you can specify what kind of numbers can be entered, like Byte, Integer, Long Integer, Decimal, Single, and Double.
Format: It can be used with most of the data types. It allows you to control how the content of the field will look when it is displayed in tables, queries, forms, and reports. You can use all valid number formats, and often there is also a list of predefined formats available.
Decimal Places: Important at Number en Currency. To specify the number of decimal places for displaying the numbers.
Default Value: It can be used with most of the data types. Automatically enters a value for the field when a new record is created. Very useful when the value is often the same. The value can be changed afterward.
Required: It can be used with most of the data types. The possible values are No (default) and Yes.

A special role is there for the properties Input mask, Validation Rule, and Validation Text. These can be used in the validation of data, that let you control the conditions for the entered data.

4.2.4 Number field size property

Possible values for data type number.

Byte: For integers that range from 0 to 255.
Integer: For integers that range from -32,768 to +32,767.
Long Integer: For integers that range from -2,147,483,648 to +2,147,483,647.
Single: For numeric floating-point values that range from -3.4 x 1038 to +3.4 x 1038 and up to seven significant digits.
Double: For numeric floating-point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits.
Decimal: For numeric values that range from -9,999… x 1027 to 9,999… x 1027.

4.3 Validation

To ensure that a user enters data in a particular way, you can make use of an input mask. Additionally, you can also use a validation rule to control if the value entered meets certain requirements and if not indicating the user on it through a validation text.

4.3.1 Input masks

An input mask ensures a solid, mandatory format for data entry in a field. An input mask consists of a series of characters and symbols. The data entered must conform to the pattern of the mask. For example, with an input mask, you can ensure that a telephone number consists of exactly 10 digits. Input masks are possible for the data type Text, Numeric, Currency, and Date/Time.

The input mask also determines the number of characters that can be entered. Input masks are made up of one mandatory part and two optional parts, and each part is separated by a semicolon. The purpose of each part is as follows:

The first part is mandatory. It includes the mask characters along with placeholders and literal data such as parentheses, periods, and hyphens.
The second part is optional and refers to the embedded mask characters and how they are stored within the field. If the second part is set to 0, the characters are stored with the data, and if it is set to 1, the characters are only displayed and not stored.
The third part of the input mask is also optional and indicates a single character or space that is used as a placeholder. By default, Access uses the underscore (_). If you want to use another character, enter it in the third part of your mask.

Setting the second part to 1 can save database storage space.

Table 4.2 lists the placeholder and literal characters for an input mask and explains how it controls data entry.

Table 4.2: Placeholder and literal characters for an input mask

Character	Explanation
0	User must enter a digit (0 to 9).
9	User can enter a digit (0 to 9).
#	User can enter a digit, space, plus, or minus sign. If skipped, Access enters a blank space.
L	User must enter a letter.
?	User can enter a letter.
A	User must enter a letter or a digit.
a	User can enter a letter or a digit.
&	User must enter either a character or space.
C	User can enter characters or spaces.
. , : ; - /	Decimal and thousands placeholders, date and time separators. The character you select depends on your Microsoft Windows regional settings.
>	Converts all characters that follow to uppercase.
<	Converts all characters that follow to lowercase.
!	Causes the input mask to fill from left to right instead of from right to left.
\	Characters immediately following will be displayed literally.
“”	Characters enclosed in double quotation marks will be displayed literally.

You can either quickly add input masks by using the Input Mask Wizard or specify masks manually by typing custom masks to the Input Mask field property setting.

Example 4.1 US telephone number

Input mask: (999) 000-000;0;-

Explanation:

(999) user can enter three digits as area code
000-000 user must enter six digits
;0 specifies that the mask characters will be stored along with the data.
;- specifies that a hyphen (-) instead of the underscore (_) is to be used as the placeholder character.

Example 4.2 ZipCode

Input mask: 0000\ >LL

Explanation:

0000 user must enter four required digits (0 t / m 9).
\ indicates that a blank space is displayed. You don’t need to enter this space.
>LL user must enter two required letters that will be converted to uppercase.

4.3.2 Validation rule and text

Validation rules can restrict what a user can enter in a particular field. This way you can ensure that only values can be entered that meet certain conditions. Does the input not meet the conditions then a message appears on the screen.

Validation rule: An expression that needs to be true for the entered data in the field. When you enter or change data in a field, Access checks if the expression is true. If not, a warning message with the value of the validation text is displayed. You then need to change the value until the validation rule is true.
Validation text: The text that will be displayed when a value is entered which does not satisfy the expression in the validation rule.

When creating expressions you should consider the following:

Field names must be enclosed in square brackets, like [OrderDate].
Dates must be surrounded by #, like #12-31-2010#.
Text must be surrounded by double quotes, like “Amsterdam”.
Wild cards are permitted. Most known are
- ?: one character
- *: multiple characters
- #: one digit

Table 4.3 lists the most used operators.

Table 4.3: Operators in expressions

Operator	Function	Example
<	Less than	<100
<=	Less than or equal to	<=15
>	Greater than	>10
>=	Greater than or equal to	>=0
=	Equal to	=21
<>	Not equal to	<>0
AND	Logical AND	>=1 AND <=9
OR	Logical OR	“m” OR “v”
NOT	Logical NOT	NOT >10
IN	Should exist in a list	IN (“Berlin”,“London”,“Paris”)
BETWEEN	Should be between two values	BETWEEN 1 AND 9
LIKE	Matches pattern string	LIKE “Ams*”

Example 4.3 Date validation

Validation rule: >=#1-1-2010#
Validation text: Enter a date from January 1, 2010.

Example 4.4 Invoice number validation

Validation rule: Like "[0-9][0-9][0-9][0-9]"
Validation text: Invoice number must consist of 4 digits.

Example 4.5 Simple email address validation

Validation rule: Like "*@*.???"
Validation text: Not a valid email address. :::

4.4 Create a new table manually

Purpose: Creating a new table from scratch and assign a key.

You had to create a new table with the name Transport Companies to store the contact details of the transport company.

Task 4.1 File: candy365.accdb

Open the database.
Choose [tab Create > Table (group Tables)]{.uicontrol. A new table has been created. There is automatically one field with the name ID which can be removed if you don’t need the field.
Switch to Design View. Because the table is not named yet, the dialog box Save As appears.

Enter the name Transport Companies and click OK. The table Transport Companies appears in the design view.
Right-click somewhere in the field ID and choose Delete Rows and confirm the removal.
Add the following fields:

Figure 4.2: Fields table Transport Companies.

The company name will be unique so you can use this field as a primary key.

Select the first row and choose tab Design > Primary Key (group Tools). At the beginning of the first row, you now see now the key .
Switch to Datasheet View Answer the question to save the table with Yes.
Add the following records:

Figure 4.3: Records table Transport Companies.

Close table Transport Companies.

4.5 New table with Excel import

Purpose: Import an Excel worksheet as a new Access table.

Access works well with Excel. So you can import an entire worksheet in a new or an existing table.

Task 4.2 File: candy365.accdb

Open the database.
Choose tab External Data > New Data Source (group Import & Link) > From File > Excel.

Figure 4.4: Dialog box Get External Data - Excel Spreadsheet.

Navigate with the button Browse to the file transport.xlsx.
Select the first option Import the source data into a new table in the current database and click OK. The Import Spreadsheet Wizard appears.
Select the option First Row Contains Column Headings and click Next. In the wizard screen that appears now, several characteristics of the fields can be changed.
Click Next. The wizard asks now what should be used as a key for the new table.
Select No primary key and click Next. The wizard then asks for the name of the table.
Specify the name Transport for the table and click Finish. The wizard now shows that the data is imported from Excel.
Click Close.
Open the table, look at the result, and close the table.

4.6 Creating a List Box

Purpose: Create a list box in a field so that the values can be selected from a list.

When only a limited number of pre-defined values should be entered in a field, then the use of a list box can be very useful. For example, the Customers table has a field Region in which only the values North and South are allowed. In the following steps, the data type for this field will be changed in a list box.

Unfortunately, the list box does not automatically update the forms that are based on this table as the form Registration. To make the list box work in the form Registration, you must in design view first remove the field Region and then added again. That’s not part of this task.

Task 4.3 File: candy365.accdb

Open the database.
Open table Customers in Design View.
Click in the box Data Type of field Region.
Click the down arrow and select Lookup Wizard…. The first dialog box of the Lookup Wizard appears.
Select the option I will type in the values that I want. and click Next. Now you can type in the values for the list.
Type in the first cell North and in the second cell South.

Click Finish.
Switch to Datasheet View and answer the question to save the table with Yes.
Click at some customer in the field Region and make sure the list box is present and works.

4.7 Relations between tables

Between two tables exists a relation when a key field from one table is linked to a key field in the other table. The linked fields in both tables have mostly the same name and the same data type. What relations should be established is the result of the normalization process which removes the redundant information and puts this in multiple tables.

When tables are related, you can create queries, forms, and reports where the information can be combined from multiple tables and is displayed as an entity.

4.7.1 One-to-many relationship

A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. This is sometimes called a parent-child relation.

An example is the relation between the tables Customers and Orders. Between these two tables is a link on the basis of the field CustomerCode. A customer from the Customers table can have several orders in the Orders table. But every order belongs to only one customer. The table Customers is on the one-side of the relation and the table Orders on the many-side. The table Customers is the primary table in the relation and the table Orders is the related table. Through this link, all orders can be collected for a customer.

Figure 4.7: One-to-many relation between the tables customers and orders.

For this type of relationship the field with which the link is realized at one side of the relationship should be unique. In almost all cases the primary key of the table is used. The field at the many-side of the relation is called the foreign key.

Figure 4.8: Example of a one-to-many relationship.

In Access, a one-to-many relationship is displayed by a line between the primary key and foreign key, with the number 1 at the one-side and the infinity symbol ∞ at the may-side.

4.7.2 One-to-one relationship

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. This kind of relationship is not common, because most information that is related in this manner would be in one table. This will sometimes be done to divide a table with many fields in two tables, each with fewer fields. In general, one should try to avoid this situation.

4.7.3 Many-to-many relationship

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. A many-to-many relationship can not be defined directly in Access. You create such a relationship by defining a third table that is called a junction table. This junction table is related to each of both tables through a one-to-many relationship.

An example is the table Orders and the table Boxes. Each order is likely to contain several boxes and each box is probably on multiple orders. The table OrderDetails can act as the junction table. The table OrderDetails is related to the table Orders in a one-to-many relationship via the field OrderCode. And the table OrderDetails is also related to the table Boxes in a one-to-many relationship via the field BoxCode.

Figure 4.9: Example many-to-many relationship.

4.7.4 Relationships: creating, modifying and deleting

Relations between tables can be created in the window Relationships. In this window, you can also modify or delete a relation. The window can be made visible by clicking on tab Database Tools > Relationships (group Relationships).

The ribbon contains commands for editing the relationships.

Figure 4.11: Ribbon Design relationships.

If there are no relations made earlier then the window Relationships is empty. Through the button Show Table, a window with tables (and queries) is displayed with which you can add tables.

A relation between two tables can be created by dragging the primary key from one table to the foreign key in another table. Then the dialog box Edit Relationships is displayed.

[Dialog box Edit Relationships.]images/tables/relationship-edit-boxes-boxdetails.png(){#fig-relationship-edit-boxes-boxdetails-1}

If you want to enforce referential integrity for this relation then check the box Enforce Referential Integrity.

A relationship can be modified by first selecting the relationship line. This line is then displayed thicker. Then double-clicking will display the dialog box Edit Relationships.

You can delete a relationship by selecting the relationship line and then press the DELETE key.

4.7.5 Referential integrity

Referential integrity is a system of rules by which it is ensured that the internal consistency between the tables is ensured. Access then ensures that relations between records in related tables are valid, and related information is not inadvertently removed or altered.

Referential integrity is handled through the dialog box Edit Relationships by selecting the box Enforce Referential Integrity.

Consequences Referential Integrity

You cannot enter a value for the foreign key in the related table if that value does not exist in the primary key of the primary table. For example, you cannot enter an order for a non-existent customer. With an order for a new customer, you must therefore first create the customer and only then the order.
You cannot delete a record from the primary table if there is a record that relates in another related table. For example, you cannot delete a record from the Customers table if records for this customer still exist in the Orders table.
You cannot change the value of the primary key in the primary table if there is a record that relates in another related table. For example, you cannot change the customer code in the Customers table if records in the Orders table are assigned to this customer.

When the referential integrity is enforced and any of the foregoing rules is violated by an action, then an appropriate error message is displayed. In Figure 4.12 you see an example of this.

Figure 4.12: Error message when entering an order for a non existing client.

You can set referential integrity between two tables on the following conditions

Both of these tables are in the same Access database.
The linked field in the primary table is a primary key or has a unique index.
The linked fields have the same data type and numeric fields must have the same field length.
Existing data in both tables don’t violate the rules of referential integrity.

AutoNumber fields may be linked to fields of the type Number providing that the property Field Size has the value Long Integer.

When existing data in the tables break the rules for referential integrity, these violations must be addressed first. Usually, these problems arise by any of the following causes.

The fields that are linked to each other are not of the same data type or do not have the same length. This is easy to solve by changes in the design of one or both tables.
In the table at the many-side exist records with values in the linking field that don’t exist in the table at the one-side. This occurs when you delete a record in the table on the on- side without deleting the corresponding records in the table on the many-side. In fact, this creates orphans in the table on the many-side. Fortunately, Access provides a type of query with which you can find these orphans and delete them yet. If this is the case, select tab Create > Query Wizard (group Queries) > Find Unmatched Query Wizard.

4.7.6 Create Relationship Boxes-BoxDetails

A relationship connects two fields in different tables together. Before you start creating the relation, you must first determine which table is the primary table, which the related table and which fields are the linking fields.

Until now there is no relationship between the tables Boxes and BoxDetails. This is necessary because otherwise, it is not possible to determine which pralines and the amount of them are in a particular box.

Primary table: Boxes, link field BoxCode
Related table: BoxDetails, link field BoxCode

Task 4.4 File: candy365.accdb

Open the database.
Click tab Database Tools > Relationships (group Relationships). Window Relationships will be displayed. It shows the existing relations. You can also see that currently no relationships exists between the tables Boxes and BoxDetails.

Select in table Boxes the field BoxCode and drag it to the field BoxCode in table BoxDetails.

Figure 4.14: Dialog box Edit Relationships.

Check the box Enforce Referential Integrity and click Create. The relationship is now visible in the window.
Close window Relationships and answer the question to save the changes with Yes

4.8 Exercises

Exercise 4.1 Prevent duplicate names (tabl001)

The Suppliers table has a field Supplier. This is the name of the supplier. It is now possible to use the same supplier name more than once, which is undesirable. To prevent this, you can set the value of the property Indexed of this field on Yes (No Duplicates). Apply this change.

Exercise 4.2 Insertion in names (tabl002)

The last name of the customer with CustomerCode 15 is Molen, van de and the first name is Robert. If you use these names when creating labels, then you will see: Robert Molen, van de. This is not professional and you want the name to be listed on the label Robert van de Molen. A possible solution could be to change LastName in van de Molen. But this solution has the drawback that the sort by name will produce an undesired sequence. Think of a better solution, if necessary the design of the table may be changed. Do not apply this change in the database.

Exercise 4.3 Title field with a list box (tabl003)

Some customers appreciate that their title is listed with a mailing. Add a field Title to table Customers. Use data type Lookup Wizard to produce a list with the values: ing. , ir., drs., mr., dr.

Exercise 4.4 Currency format (tabl004)

In this exercise, you need the table Transport that had to be made earlier in the course. When this table is not present in your database, you have two possibilities:

You still perform the task by creating this table.
You use database transport.accdb where the table Transport already was made for you.

Open table Transport and note that the transport costs do not appear in a currency format. Change the value of the property Format of field TransportationCosts in a currency format with your local currency symbol. Then switch to datasheet view and observe the result of the change.

The showed currency symbol depends on the local Windows settings.

Exercise 4.5 Composite key (tabl005)

In this exercise, you need the table Transport that had to be made earlier in the course, see Section 4.4. When this table is not present in your database, you have two possibilities:

You still perform the task by creating this table.
You use database transport.accdb where the table Transport already was made for you.

In the Transport table, the ID TransportCompany field regularly has the same value. So you can see 13 times the value 1. Therefore, this field can not serve as a primary key. The value of DeliveryProvince appears three times because each carrier has a rate per province. The field DeliveryProvince therefore can not serve as a primary key. The combination of the fields ID TransportCompany and DeliveryProvince is unique and can therefore be used as a primary key. Set this as primary key table Transport.

Exercise 4.6 A field target group with a list box (tabl006)

There is a need to classify each customer in one of the following target groups: top customer, good customer, small customer. To do this the design of the Customers table needs to be modified. Make a new field in this table with the name TargetGroup with a list box with the values mentioned before.

Exercise 4.7 Relationship BoxDetails-Pralines (tabl007)

Create a relationship between the tables BoxDetails and Pralines and enforce referential integrity.

Exercise 4.8 Table Employees (tabl008)

Late 2010 was decided to track which employee has entered an order. There is already an Excel file containing the data of all employees. Perform the following actions:

Import the Excel file employees.xlsx and give the table the name Employees. Provide an appropriate primary key for this table.
Add a field EmployeeCode.
Create a relationship between the tables Orders and Employees and enforce referential integrity.

Exercise 4.9 Contact registration (tabl009)

The marketing department wants to record all contacts ( telephone, mail, email, visits) in order to calculate the costs incurred for a customer. For each contact, the contact type, date, start time and end time, subject, and any charges (e.g. to send a gift) are tracked. If for example an employee has spent half an hour with a letter, then it must be possible to calculate later that the costs are 0.5 * $60.

To do this, create a new table named Contacts and link this to table Customers and enforce referential integrity.

Exercise 4.10 Complaints registration (tabl010)

There are complaints from customers. The complaints can be classified into the following categories: Packaging damaged, Box not delivered on the date, Best before date has expired, Pralines don’t taste.

A complaint always refers to an order. Create new table Complaints and link this to table Orders and enforce referential integrity.

Exercise 4.11 Referential integrity (tabl011)

Check if the referential integrity is enforced at the relationships of tables Customers, Orders, OrderDetails, Boxes, BoxDetails, and Pralines. If not, do it.