The Text data type is by default set to a maximum size of 50 characters. Trying to insert data greater than the maximum size allowed in a Text field type generates a database error. If your Text field storage needs require greater length, you need to increase its field size attribute during table design. If you require a number of characters greater than 255 (the maximum size allowed in a Text field), use the Memo field type.

To better understand data types and their use, database developers must understand the data they are working with and how it can be logically grouped into tables and then separated into appropriate fields or data types. This type of database work is commonly referred to as normalization or data modeling. See if you can model some data by matching an Access data type (seen in Table 1.2) with the data that needs represented in the following numbered list.

1 An employee's social security number.

2 The start time of a test.

3 The number of employees in a company.

4 A varying bonus percentage applied to employee salaries.

5 Determines if a user is currently logged into a system.

6 Stores the address of homes.

7 The textual contents of a sample chapter.

8 An ID generated each time a new user is created.

9 The cost of a book.

10 A computer's serial number.

The correct data types for the previous storage needs are listed here:

1 Text (If dashes are used when entering data)

2 Date/Time

3 Number

4 Number

5 Yes/No

6 Text

7 Memo

8 AutoNumber

9 Currency

10 Text (If dashes are used when entering data)

Once an Access table has been created, you enter data directly into the fields by opening the table's name from the main window. As seen in Figure 1.9, Access provides a built-in graphical interface for managing data in a table directly.

From a developer's point of view, using the built-in table interface to manage data directly is sufficient while designing or maintaining a database, but is not adequate or friendly to most users.

Managing table data directly with the Access table graphical interface.

Managing table data directly with the Access table graphical interface.


As mentioned before, Access implements a relational database, allowing database developers to link one or more tables using keys or relationships. To demonstrate, follow the next sequence of steps to link two tables called Students and HomeWork_Results.

1. Create and save a new Access file and database.

2. Create two new tables called Students and HomeWork_Results.

3. The Students table should have the following fields: Student_Id (AutoNumber), First_Name (Text), Last_Name (Text), Middle_Initial (Text), Gender (Text), and Age (Number).

4. Set the Student_Id in the Students table as the primary key by right-clicking the Student_Id field in Design view and right-clicking the Primary Key menu option.

5. The HomeWork_Results table should have the following fields: Homework_Id (Number), Student_Id (Number), Homework_Completed (Yes/No), and Homework_Score (Number).

6. Set two fields as the primary key in the HomeWork_Results table by holding down your Shift or Ctrl key and clicking both the Homework_Id and Student_Id fields.

7. While both fields are highlighted and still holding the Shift or Ctrl key, right-click in the gray column to the left of one of the highlighted fields and select the Primary Key menu option. This is known as a multifield key!

8. Ensure both tables are saved and named then select Tools, Relationships from the Access menu. An interim window is displayed from where you will add both tables to the relationship window. After which, the relationship window displays your two new tables. Note the primary keys in both tables are highlighted in black as seen in Figure 1.10.

9. Drag and drop the Student_Id field from the Students table onto the Student_Id field in the HomeWork_Results table. A new window appears, as depicted in Figure 1.11.

10. Click the Create New button and you have created a one-to-many relationship, which means for every student in the Students table, there are many occurrences of that student in the HomeWork_Results table.

The primary key in the Students table, Student_Id, has also now become a foreign key in the HomeWork_Results table.

You must make other considerations about a relationship:

• Enforce referential integrity, which means values entered into the foreign key must match values in the primary key.

• Enforce cascading updates between one or more tables, which means related updates from one table's fields are cascaded to the other table or tables in the relationship.

Enforce cascading deletes between the two tables. In short, this means any relevant deletions from one table cascade in the other table(s).

Both cascading updates and deletes help enforce referential integrity.

Viewing relationships between two tables.

Viewing relationships between two tables.

Editing a relationship between two tables.

Editing a relationship between two tables.

To better visualize the relationship, enter a few records into the Students and HomeWork_Results tables by double-clicking each table, one at a time, from the main Access window. Remember, opening a table from the main Access window allows you to manage field values directly!

11. After you've entered data in both tables, open the Students table again and you should see a plus sign (+) to the left of each Student_Id.

Click the plus sign and you should see the related HomeWork_Results record for the student, as depicted in Figure 1.12.

Viewing related table information after creating a table relationship.

The book's accompanying CD has a copy of the database discussed in this section titled Chapter1.mdb.


Though careful analysis is the key to a well-designed and adaptive database, end users are more likely to appreciate a well-built user interface time and again. A good interface leverages forms and controls in a way that is intuitive to users for managing data. User interfaces should hide the complexities of a database, such as business rules and relationships.

Access forms are graphical controls that act as containers for other graphical controls such as text boxes, labels, and command buttons. You can add a form to your database by clicking the Form object in the Access main window and then clicking the Create form in Design view item as seen in Figure 1.13.

Creating a new form in Access.

* Microsoft Access - [Access Essentials : Database (Ac-ess '">

File Edit View Insert Tools window Help _ s x iQGSUBIdiaVIJlfc»^-!®.-!»-

jopen ^'[Design J3 New | |


iCreate form in Design view!

J Tables

i|U Create form by using wizard

jp Queries

0 0

Post a comment