Deciding what information you need in your MS Access database
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.
Check out the information below.
Paper forms also have fields.
This image shows the paper form that players fill out to sign up for a baseball league whose database tables appear below. Compare the images and you can see that the Players, Teams, and Divisions database tables all have fields for entering information from this form.
Plans for database tables and field names.
Separating information into different Access database tables
After you know the information you want to record in the Access 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 Access databases work, consider the simple database. 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
- 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 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.
Choosing fields for Access database tables
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 an Access 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
- 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. 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 Access 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, 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, fields called Player Number and Coach Number have been introduced. For the purpose of this database, players and coaches are assigned numbers.
Mapping the relationships between tables in Access
If your Access 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. The image below 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. For now, when you design your database, consider how to connect the various tables with common fields.
Table relationships.