Database tables for storing information

Information in databases is stored in database tables like the one in Figure 1-1. In a database table, you include one field for each category of information you want to keep on hand. Fields are the equivalent of columns in a table. Your first duty when you create a database table is to name the fields and tell Access what kind of information you propose to store in each field. The database table in Figure 1-1 is for storing employee information. It has eight fields: ID, First Name, Last Name, E-mail Address, Business Phone, Company, Job Title, and Home Phone.

A database can comprise one database table or many different tables that are linked together. If you’re dealing with a lot of information, storing data in more than one table is to your advantage. Later in this chapter, “Separating information into different database tables” explains why storing data across several database tables is advantageous.

Forms for entering data

After you create the fields in the database table, you can start entering the records. A record describes all the data concerning one person or thing. Although you can enter records straight into a database table, the easiest way to enter a record is with a form. Similar to a dialog box, a form has convenient text boxes and drop-down lists for entering information, as shown in Figure 1-2. On a form, you can see clearly what kind of information needs entering in each field.

Queries for getting the data out

Figure 1-3 shows a simple query for finding out which employees in the database table shown in Figure 1-1 are Sales Representatives. A query is a question you ask of a database. The question here is, “Who is a Sales Representative?” Notice the criterion “Sales Representative” in the Job Title field on the Query grid.

In an address database, you can use a query to find all the people in a particular ZIP code or state. If information about contributions is stored in the database, you can find out who contributed more than $500 last year. Queries can get very complex. For example, you can find all the people in a particular city who contributed between $50 and $500 and volunteered more than eight hours in the past year. You can construct the query so that it produces each person’s name and telephone number, or you can construct it so that all the information you have concerning each person appears in the query results.

When you get junk mail, it likely comes to your mailbox as the result of a database query. Companies routinely buy and sell customer databases. They query these databases to gather the names of people who they believe are well-disposed to purchasing the products they sell. Next time you get junk mail solicitation, study the letter and ask yourself, “How did I get in this database, and which database query produced my name?” The junk mailer is probably targeting extraordinarily beautiful, intelligent people.

After you create a query, you can save it and run it again. You can use it as the basis for constructing new queries. The information in database tables usually changes over time. Customers change addresses. New products come online, and others are discontinued. But no matter how much the data changes, you can find out exactly what you want to know from a database by running a well-crafted query.

Reports for presenting and examining data

Figure 1-4 shows a report. Reports can be made from database tables or from the results of queries. Reports are usually read by managers and others who don’t get their hands dirty in databases. They’re meant to be printed and distributed so that the information can be scrutinized and analyzed. Access offers many attractive reports. Don’t worry: The program does most of the layout work for you, and exporting reports to a Word file is easy.

Macros and modules

Macros and modules aren’t covered in this minibook, but they are also database objects. A macro is a series of commands. You can store macros for running queries and doing other Access tasks. A module is a collection of Visual Basic procedures and declarations for performing tasks in Access.

Creating a blank database file

Follow these instructions to create a blank database file:

1. On the File tab, choose New.

   The New window appears.

2. Click the Blank Desktop Database icon.

   A dialog box appears for choosing the folder where you will store your new database.

3. Click the Browse button.

   You see the File New Database dialog box.

4. Select the folder where you want to keep the database file, enter a name in the File Name text box, and click OK.

5. Click the Create button.

   The Navigation pane and a blank table appear. Later in this chapter, “Finding Your Way around the Navigation Pane” explains what this pane is all about. I suggest you go there without delay or deferral.

Getting the help of a template

As I explain earlier, templates are wonderful if you have the wherewithal to modify them. Access offers prefabricated databases for tracking assets, keeping inventory, scheduling resources, and doing other things. Unfortunately, the only way to find out whether one of the templates is worthwhile is to go to the trouble to create a database from a template, open the database file, and look around.

Follow these steps to create a database file from a template:

1. On the File tab, choose New.

   The New window opens.

2. Select a template or use the Search box to obtain a template online from Microsoft.

   Templates showing the globe icon are applications, not databases. Access applications are designed for use with web browsers.

3. Click the Browse button.

   The File New Database dialog box opens.

4. Select the folder where you want to keep the database file, enter a name in the File Name text box, and click OK.

5. Click the Create button.

   The Navigation pane and a blank table appear. Read on to find out how to find your way around the Navigation pane.

Deciding what information you need

The first question to ask yourself is about the kind of information you want to get out of the database. Customer names and addresses? Sales information? Information for inventory tracking? Interview your coworkers to find out what information could be helpful to them. Give this matter some serious thought. Your goal is to set up the database so that every tidbit of information your organization needs can be recorded.

A good way to find out what kind of information matters to an organization is to examine the forms that the organization uses to solicit or record information. These forms show precisely what the organization deems worthy of tracking in a database. Figure 1-6, for example, shows the paper form that players fill out to sign up for a baseball league whose database tables appear in Figure 1-7. Compare Figure 1-6 with Figure 1-7 and you can see that the Players, Teams, and Divisions database tables all have fields for entering information from this form.

Separating information into different database tables

After you know the information you want to record in the database, think about how to separate the information into database tables. Many are tempted to put all the information into a single database table, but because Access is a relational database, you can query more than one table at a time, and in so doing, assemble information from different tables.

To see how it works, consider the simple database, as shown in Figure 1-7. The purpose of this little database and its four tables is to store information about the players, coaches, and teams in a baseball league. The Team Name field appears in three tables. It serves as the link among the tables and permits more than one to be queried. By querying individual tables or combinations of tables in this database, you can assemble team rosters, make a list of coaches and their contact information, list teams by division, put together a mailing list of all players, find out which players have paid their fee, and list players by age group, among other things. This database comprises four tables:

• Players: Includes fields for tracking players’ names, addresses, birthdays, which teams they’re on, and whether they paid their fees
• Coaches: Includes fields for tracking coaches’ names, addresses, and the names of the teams they coach
• Teams: Includes fields for tracking team names and which division each team is in
• Divisions: Includes fields for tracking division numbers and names

Deciding how many database tables you need and how to separate data across the different tables is the hardest part of designing a database. To make the task a little easier, do it the old-fashioned way with a pencil and eraser. Here are the basic rules for separating data into different tables:

• Restrict a table to one subject only: Each database table should hold information about one subject only — customers, employees, products, and so on. This way, you can maintain data in one table independently from data in another table. Consider what would happen in the Little League database (refer to Figure 1-7) if coach and team data were kept in a single table, and one team’s coach was replaced by someone new. You would have to delete the old coach’s record, delete information about the team, enter information about the new coach, and reenter information about the team that you just deleted. But by keeping team information separate from coach information, you can update coach information and still maintain the team information.
• Avoid duplicate information: Try not to keep duplicate information in the same database table or duplicate information across different tables. By keeping the information in one place, you have to enter it only once, and if you have to update it, you can do so in one database table, not several.

Entire books have been written about database design, and this book can’t do the subject justice. You can, however, store all your data in a single table if the data you want to store isn’t very complex. The time you lose entering all the data in a single table is made up by the time you save not having to design a complex database with more than one table.

Choosing fields for database tables

As I explain earlier, fields are categories of information. Each database table needs at least one field. If the table itself is a subject, you could say that its fields are facts about the subject. An Address database table needs fields for recording street addresses, cities, states, and ZIP codes. A Products database table needs fields for product ID numbers, product names, and unit prices. Just the facts, ma’am. Within the confines of the subject, the database table needs one field for each piece of information that is useful to your organization.

When you’re planning which fields to include in a database table, follow these guidelines:

• Break up the information into small elements. For example, instead of a Name field, create a First Name field and a Last Name field. This way, you can sort database tables by last name more easily.
• Give descriptive names to fields so that you know what they are later. A more descriptive name, such as Serial Number, is clearer than SN.
• Think ahead and include a field for each piece of information your organization needs. Adding a field to a database table late in the game is a chore. You have to return to each record, look up the information, and enter it.
• Don’t include information that can be derived from a calculation. As I explain in Chapter 4 of this minibook, calculations can be performed as part of a query or be made part of a table. For example, you can total the numbers in two fields in the same record or perform mathematical calculations on values in fields.

Deciding on a primary key field for each database table

Each database table must have a primary key field. This field, also known as the primary key, is the field in the database table where unique, one-of-a-kind data is stored. Data entered in this field — an employee ID number, a part number, a bid number — must be different in each record. If you try to enter the same data in the primary key field of two different records, a dialog box warns you not to do that. Primary key fields prevent you from entering duplicate records. They also make queries more efficient. In a query, you tell Access what to look for in database tables, Access searches through the tables, and the program assembles information that meets the criteria. Primary key fields help Access recognize records and not collect the same information more than once in a query.

Social security numbers make good primary key fields because no two people have the same social security number. Invoice numbers and serial numbers also make excellent primary key fields. Returning to the sample baseball league database (refer to Figure 1-7), which fields in the Little League database tables are primary key fields? In the Teams table, Team Name can be the primary key field because no two teams have the same name. Division Number can also be a primary key field because divisions in the league are numbered and no two divisions have the same number.

The Players and Coaches database tables, however, present a problem when it comes to choosing a primary key field. Two players might have the same last name, which rules out Last Name as a primary key field. A brother and sister might have the same telephone number, which rules out a Telephone No. field. Because no field holds values that are certain to be different from record to record, I introduce fields called Player Number and Coach Number. For the purpose of this database, players and coaches are assigned numbers. (Chapter 2 in this minibook explains how Access can assign sequential numbers for you in a database table.)

Mapping the relationships between tables

If your database includes more than one table, you have to map how the tables relate to one another. Usually, relationships are formed between the primary key field in one table and the corresponding field in another, called the foreign key. Figure 1-8 shows the relationships between the tables in the Little League database. Because these tables are linked by common fields, you can gather information from more than one table in a query or report. Chapter 2 in this minibook takes up the subject of linking tables in more detail. For now, when you design your database, consider how to connect the various tables with common fields.