Database Basics

Whereas previous hours of this book began by introducing you to the program right away, this hour begins by explaining database concepts. You need to learn how a database management system organizes data before you jump into Access.

A database is an organized collection of data. Access is called a database management system because it enables you to create, organize, manage, and report from the data stored in databases.

Database experts have written complete books on database theory. This hour won't give you an extremely in-depth appreciation for databases, but you will learn enough to get started with Access.

A database typically contains related data. In other words, you might create a home-office database with your household budget but keep another database to record your rare-book collection titles and their worth. In your household budget, you might track expenses, income, bills paid, and so forth, but that information does not overlap the book-collection database. Of course, if you buy a book, both databases might show the transaction, but the two databases would not overlap.

Technically, a database does not have to reside on a computer. Any place you store data in some organized format, such as a name and address directory, could be considered a database. In most cases, however, the term database is reserved for organized, computerized data.

When you design a database, consider its scope before you begin. Does your home business need an inventory system? Does your home business need a sales contact? If so, an Access database works well. Only you can decide whether the inventory and the sales contacts should be part of the same system or separate, unlinked systems. The database integration of inventory with the sales contacts requires much more work to design, but your business requirements might necessitate the integration. For example, you might need to track which customers bought certain products in the past.

Not all database values directly relate to one another. Your company's loan records do not relate to your company's payroll, for example, but both probably reside in your company's accounting database. Again, you have to decide on the scope when you design your database. Fortunately, Microsoft made Access extremely flexible, so you can change any database structure when you begin using your database. The better you analyze the design up front, however, the easier your database is to create.

Database Tables

If you threw your family's financial records into a filing cabinet without organizing them, you would have a mess. That is why most people organize their filing cabinets by putting related records into file folders. Your insurance papers go in one folder; your banking records go in another.

Likewise, you cannot throw your data into a database without separating the data into related groups. These groups are called tables ; a table is analogous to a file folder in a filing cabinet. Figure 17.1 illustrates a set of tables that hold financial information inside a business's database.

Figure 17.1. A database will contain data separated into groups called tables.

A database might contain many tables, each being a further refinement of related data. Your financial database might contain tables for accounts payable, customer records, accounts receivable, vendor records, employee records, and payroll details (such as hours worked during a given time period). The separate tables help you eliminate redundant data; when you produce a payroll report, Access might retrieve some information from your employee table (such as name and pay rate) and some information from your time tables (such as hours worked).

Access is a relational database as opposed to a flat-file database . That means Access uses data from multiple tables instead of requiring you to duplicate data in two or more places. Therefore, if you increase a customer's discount, you need to change the discount in only one customer table rather than in the customer table, the pricing table, and the sales table.

Access stores all tables for a single database in one file that ends with the .mdb extension. By storing the complete database in one file, Access makes it easier for you to copy and back up your database. You never have to specify the .mdb extension when you create a database.

You can import data from an Access database table into a Word document or Excel worksheet. The interaction between Access and the other Office products makes creating and reporting data simple. Appendix A, "Sharing Information Between Office 2003 Programs," explains more about sharing data between the Office products.

Records and Fields

To keep track of table data, Access breaks down each table into records and fields . In some ways, a table's structure looks similar to an Excel worksheet because of the rows and columns in a worksheet. As Figure 17.2 shows, a table's records are the rows, and a table's fields are the columns . Figure 17.2 shows a checkbook-register table; you usually organize your checkbook register just as you would organize a computerized version of a checkbook , so you will have little problem mastering Access's concepts of records and fields.

Figure 17.2. Tables have records (rows) and fields (columns).

Your table fields contain different data types. As Figure 17.2 shows, one field might hold a text description, whereas another might hold a dollar amount. Every item within the same field must be the same data type, but a table might contain several fields that differ in type. When you design your database, you are responsible for indicating to Access which data type you want for each field in your database tables.

The types of data that you can store in an Access database table are

• Text ” Text data consists of letters , numbers, and special characters . You only report text data; you cannot calculate with it. A balance-due field would never be a text data type, but addresses, names , and Social Security numbers are examples of text fields. Generally, you store short text items (names, addresses, cities, product names, and part codes) in text fields.

• Memo ” The memo field can hold an extremely large amount of text, including paragraphs. Memo fields consume a lot of space, and not all tables require them. Memos are great for documenting table entries and adding textual data that is free-form. For example, an Evaluation field for an employee database would be a good Memo candidate because you could then make entries that describe the employee's performance.

• Number ” A number field holds numbers. Use this field to calculate values.

• Date/Time ” These fields hold date and time values (similar to the date and time format in Excel). Access enables you to enter data into date and time fields using many formats. Additionally, Access respects your Windows international settings, so you can enter a date in your country's format.

• Currency ” This field holds dollar amounts. Access keeps the dollar amounts rounded to the correct decimal alignment needed to match your currency designation. Access recognizes your Windows international settings and uses international currency amounts when needed.

• AutoNumber ” This field holds sequential numbers, a different number for each record in the table.

• Yes/No ” These fields hold Yes and No (or True and False) two-pronged values to indicate the existence or absence of an item or to indicate the answer to an implied question. For example, some items in an inventory database might be tagged for a special discount whereas others are not tagged.

• OLE object ” This is an embedded object, such as a graph you create in Excel. Your Access databases can hold any kind of OLE-compatible embedded object.

• Hyperlink ” This is an Internet Web site address. Such a field can hold an Internet address for a file as well as a network or an intranet address within your system network. When the database user clicks the hyperlink, Access shows the hyperlink's Web page or network file.

The Internet integration of the other Office products extends to Access. When you click a table's hyperlink to an Internet Web page address, Access sends you to the Web page, logging you on to your Internet provider if necessary.

Using a Key Field

Every Access table requires a primary key field . The primary key field (often just called a key) is a field that contains a unique value and no duplicate entries. Whereas a table's city field might contain multiple occurrences of the same city name, a key field must be unique for each record. You can designate an existing data field as the table's key field, or you can use the AutoNumber field that Access adds to all tables as the key field.

If you access a particular field very often, even if that field is not a key field, designate it as an index field in the Design view property settings. Access creates an index for every database and locates the index fields in that index. Just as an index in the back of the book speeds your searches for particular subjects, the index field speeds searches for that field.

If you were creating a table to hold employee records, a good key-field candidate would be the employee's Social Security number because each one is unique. If you are not sure that your data contains unique information in any field, specify the AutoNumber field that Access creates as the key. In the AutoNumber field, Access stores a unique number for each table record.

Access uses the key field to find records quickly. When you want to locate an employee's record, for example, search by the employee's key field (the Social Security number). If you search based on the employee's name, you might not find the proper record; two or more employees might be named John Smith, for example.

So many companies assign you a customer number because the customer number uniquely identifies you in their database. Although today's computerized society sometimes makes one feel like "just another number," such a customer number enables the company to keep your records more accurate and keep costs down.