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.
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.
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 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.
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
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 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.