Lesson 1

There is a common saying among database professionals: Garbage in, garbage out.
If you do not take steps to control the information being stored in your database, you will get erroneous information when displaying the data in reports.
The place to start controlling the insertion of bad data into your database is at the table level. By the end of this module, you will know how to:

To Access, a table is always just a table. But to your Access application, different tables serve different purposes. A database table fits into one of three types:

1. an object table,
2. a transaction table, or
3. a join table.

Knowing what type of table you’re creating helps to determine how you create it.

Object tables are the most common. Each record of this type of table holds information that relates to a real-world object. A customer is a real-world object, and a record in a table named tblCustomers holds information about that customer. The fields in an object table reflect the characteristics of the object they represent. A City field describes one characteristic of the customer, which is the actual city where the customer is. When creating an object table, think about the characteristics of that object that make it unique or that are important.

The next most common type of table is a transaction table. Each record of a transaction table holds information about an event. For example, placing an order for a book is an example of an event. To hold the details of all the orders, you might have a table named tblBookOrders. Transaction tables almost always have a Date/Time field because when the event happened is usually an important piece of information to record. Another common type of field is a field that refers to an object table, such as a reference to the customer in tblCustomers that placed the order. When creating a transaction table, think about the information created by the event and who was involved.

Join tables are the easiest to design and are vitally important to a well-designed database. Usually relating two tables is a simple process: A customer orders a book, for instance, and you can easily relate that order to that customer. But sometimes the relationship is not so clear. A book may have many authors, and an author may have many books. When this relationship exists, called a many-to-many relationship, a join table sits in the middle of the two tables. A join table usually has a name that reflects the association, such as tblAuthorBook. A join table generally has only three fields: a unique field to identify each record, a reference to one side of the association, and a reference to the other side of an association.

1. Use the Validation Rule property to test data
2. Set the Validation Text property to display a custom message
3. Use the Input Mask Wizard to specify data input formats
4. Set up fields that display Combo Box and List Box controls for data input
5. Use the correct data type for optimal performance

In the next lesson, you will learn how to use the Validation Rule property to test data.