Table Design   «Prev  Next»
Lesson 1

Table Design Enhancement in MS Access

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:

In this module, you learn how to create a new Access database and its tables. You establish the database container to hold your tables, forms, queries, reports, and code that you build as you learn Access. Finally, you create the actual tables used.

AccessTable Types

Question: What are the different types of tables in Microsoft Access?
Microsoft Access offers a variety of table types to manage and organize your data effectively. Understanding these types is crucial for making the most of this database management system:
  1. Local Tables: Local tables are the standard table type in Microsoft Access. They store data locally within the database file. These tables can store a wide variety of data types, including text, numbers, dates/times, and more. When you create a new table in Access, it's a local table by default.
  2. Linked Tables: Linked tables are pointers to tables that are stored outside the current database. They allow you to connect to data from external sources, such as another Access database, an Excel file, or a SQL Server database, among others. Linked tables are beneficial for accessing and editing data that needs to be shared between multiple databases or applications.
  3. System Tables: System tables in Access are created automatically and used internally to store information about the database itself, such as information about the database's tables, queries, and other objects. They have names beginning with "MSys" and are usually hidden, as they're generally not intended to be accessed or modified by users.
  4. Temporary Tables: Temporary tables are often used to store intermediate results for complex queries or to backup data before performing operations that may change it. They're typically created within queries or VBA code and are deleted when no longer needed.
  5. Lookup Tables: Lookup tables are a specific kind of local table used to store a list of values for a specific field. These are used in conjunction with lookup fields, which display a list of values for users to choose from when entering data, providing a more controlled and efficient data entry process.
  6. Pivot Tables: Pivot tables are used to summarize, analyze, explore, and present a summary of data from another table or query. They are dynamic tables that provide flexible views of the original data, allowing users to group and summarize information in real time for reporting and analytics.
  7. Cross-Tabulation Tables: Cross-tabulation tables, also known as cross-tab or pivot queries, display summarized values (sum, average, count) from one field in a table and group them by one set of facts listed down the left side of the table and another set of facts listed across the top of the table.

By understanding the role of each table type, you can design your Microsoft Access databases to be efficient, flexible, and powerful tools for storing, manipulating, and analyzing your data.
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

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.

Transaction Tables

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

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.