Microsoft Access 2000 Bible

Chapter 3: A Review of Database Concepts

Before you begin to use a database software package, you must understand several basic concepts. The most important concept is that the data is stored in a "black box" known as a table and that by using the tools of the database, you can retrieve, display, and report the data in any format you want.

What Is a Database?

Database is a computer term for a collection of information concerning a certain topic or business application. Databases help you organize this related information in a logical fashion for easy access and retrieval.

A typical manual filing system consists of people, papers, and filing cabinets. Paper is the key to the manual database system. In a real manual database system, you probably have in/out baskets and some type of formal filing method. Information is accessed manually by opening a file cabinet, taking out a file folder, and finding the correct piece of paper. Paper forms are used for input, perhaps with a typewriter. Information is found by sorting the papers manually or by copying desired information from many papers to another piece of paper (or even into a computer spreadsheet). A calculator or a computer spreadsheet might be used to analyze the data further or to report it.

A computer database is nothing more than an automated version of the filing and retrieval functions of a manual paper filing system. Computer databases store information in a structured format that you define. They can store data in a variety of forms, from simple lines of text (such as name and address) to complex data structures that include pictures, sounds, or video images. Storing data in a precise, known format enables a database management system (DBMS) to turn the data into useful information through many types of output, such as queries, and reports.

With an automated database management system such as Access, the person uses a computer to access the data stored in tables -- entering data in the tables through data-entry forms and retrieving it by using a query. Queries retrieve only the desired data from the tables. Then a report outputs the data to the screen or a printer. Macros and modules allow the user to automate this process and to create new menus and dialog boxes.

A relational database management system (RDBMS) such as Access stores data in many related tables. The user can ask complex questions from one or more of these related tables, with the answers returning as forms and reports.

Databases, Tables, Records, Fields, and Values

Microsoft Access follows traditional database terminology. The terms database, table, record, field, and value indicate a hierarchy from largest to smallest.

Databases

In Access, a database is the overall container for the data and associated objects. Database objects include tables, queries, forms, reports, macros, and modules. In some computer software products, the database is the object that holds the actual data; in Access, this is called a table.

Access can work with only one database at a time. Within a single Access database, however, you can have hundreds of tables, forms, queries, reports, pages, macros, and modules -- all stored in a single file with the file extension .MDB (multiple database) or .ADP if you are using SQL Server.

Tables

A table is a container for raw data. When data is entered in Access, a table stores it in logical groupings of similar data (the Pets table, for example, contains data about pets) and the table's design organizes the information into rows and columns. In a typical Access table design, its datasheet (also known as a browse table or table view) displays multiple lines of data in neat rows and columns.

Records and fields

The datasheet is divided into rows called records and columns called fields. The data shown in the table has columns of similar information, such as Pet Name, Customer Number, Breed, or Date of Birth; these columns of data items are fields. Each field is identified as a certain type of data (Text, Number, Date, and so on) and has a specified length. Each field has a name that identifies its category of information.

The rows of data within a table are its records. Each row of information is considered a separate entity that can be accessed or sequenced as desired. All the fields of information concerning a certain pet are contained within a specific record.

Values

At the intersection of a row (record) and a column (field) is a value -- the actual data element. For example, Bobo, the Pet Name of the first record, is one data value. (How do you identify the first record? It's the record with the rabbit. But what if there is more than one rabbit?) Whereas fields are known by the field name, records are usually known by some unique characteristic of the record. In the Pets table, one field is the Pet ID; Pet Name is not unique because there could be two pets named Fido in the table.

Sometimes it takes more than one field to find a unique value. Customer Number and Pet Name could be used, but it's possible for one customer to have two pets with the same name. You could use the fields Customer Number, Pet Name, and Type of Animal. Again, theoretically, you could have a customer come in and say, "Hi, my name's Larry -- this is my pet snake Darryl, and this is my other pet snake Darryl." Creating a unique identifier (such as Pet ID) helps distinguish one record from another without having to look through all the values.

Why Use More Than One Table?

A database contains one or more tables (that is, logical groupings of similar data). Most applications that are developed in Access have several related tables to present the information efficiently. An application that uses multiple tables can usually manipulate data more efficiently than it could with one large table.

Multiple tables simplify data entry and reporting by decreasing the input of redundant data. By defining two tables for an application that uses customer information, for example, you don't need to store the customer's name and address every time the customer purchases an item.

Consider the following typical table relation: the Customer table related to the Pets table. If there were only one table, the customer name and address would have to be repeated for each pet record. Two tables let the user look up information in the Customer table for each pet by using the common field Customer Number. This way, when a customer changes address (for example), it changes only in one record in the Customer table; when the pet information is onscreen, the correct customer address is always visible.

Separating data into multiple tables within a database makes the system easier to maintain because all records of a given type are within the same table. There is a significant reduction in design and work time by taking the time to segment data properly into multiple tables.

Tip: It is also a good idea to create a separate database for just your tables. By separating your design objects (queries, forms, reports, pages, macros, and modules) and the tables into two different databases, it is easier to maintain your application.

Database Objects and Views

If you are new to databases (or are even an experienced database user), before starting to use Access you need to understand some key Access concepts. The Access database contains seven objects, which consist of the data and tools you need to use Access:

Tables: Holds the actual data (uses a datasheet to display the raw data)
Query: Lets you search, sort, and retrieve specific data
Form: Lets you enter and display data in a customized format
Report: Lets you display and print formatted data, including calculations and totals
Pages: Lets you publish live forms to a corporate intranet
Macro: Gives you easy-to-use commands to automate tasks without programming
Module: Program written in VBA

Database Objects and Views

Datasheets are one of the many ways by which data can be viewed. Although not a database object, a datasheet displays a list of records from a table in a format commonly known as a browse screen or table view. A datasheet displays data as a series of rows and columns (comparable to a spreadsheet). A datasheet simply displays the information from a table in its raw form. This spreadsheet format is the default mode for displaying all fields for all records.

You can scroll through the datasheet using the directional keys on your keyboard. You can also display related records in other tables while in a datasheet. In addition, you can make changes to the displayed data. Use caution when making any changes or allowing a user to make any modifications in this format. When a datasheet record is changed, it is the data in the underlying table that is actually changing.

Queries and dynasets

A query is used to extract information from a database. A query can select and define a group of records that fulfill a certain condition. You can use queries before printing a report so that only the desired data is printed. Forms can also use a query so that only certain records (that meet the desired criteria) appear onscreen. Queries can be used within procedures that change, add, or delete database records.

An example of a query is when a doctor at Mountain Animal Hospital says, "Show me which of the pets that we treat are dogs or cats and located in Idaho. Show them to me sorted by customer name and then by pet name." Instead of asking the question in actual English, the doctor would use a method known as QBE, which stands for Query by Example.

When you enter instructions into the QBE window, the query translates the instructions and retrieves the desired data. In this example, the query first combines data from both the Customer and Pets tables, using the related field Customer Number (the common link between the tables). Then it retrieves the fields Customer Name, Pet Name, Type of Animal, and State. Access then filters the records, selecting only those in which the value of State is ID and the value of Type of Animal is dog or cat. It sorts the resulting records first by customer name and then by pet name within the customer names that are alike. Finally, the records appear onscreen in a datasheet.

These selected records are known as a dynaset -- a dynamic set of data that can change according to the raw data in the original tables.

After you run a query, the resulting dynaset can be used in a form that can be displayed onscreen in a specified format or print on a report. In this way, user access can be limited to the data that meets the criteria in the dynaset.

Data-entry and display forms

Data-entry forms help users get information into a database table in a quick, easy, and accurate manner. Data-entry and display forms provide a more structured view of the data than does a datasheet. From this structured view, database records can be viewed, added, changed, or deleted. Entering data through the data-entry forms is the most common way to get the data into the database table.

Data-entry forms can be used to restrict access to certain fields within the table. You can also use these forms to check the validity of your data before you accept it into the database table.

Most users prefer to enter information into data-entry forms rather than datasheet tables; data-entry forms can be made to resemble familiar paper documents. Forms make data entry self-explanatory by guiding the user through the fields of the table being updated.

Display-only screens and forms are solely for inquiry purposes. These forms allow for the selective display of certain fields within a given table. Displaying some fields and not others means that you can limit a user's access to sensitive data while allowing inquiry into other fields.

Reports

Reports present your data in printed format. You can create several different types of reports within a database management system. For example, your report can list all records in a given table, such as a customer table. You can also create a report that lists only the customers who meet a given criterion, such as all those who live in the state of Washington. You do this by incorporating a query into your report design. The query creates a dynaset consisting of the records that contain the state code WA.

Your reports can combine multiple tables to present complex relationships among different sets of data. An example is printing an invoice. You access the customer table to obtain the customer's name and address (and other pertinent data) and the sales table to print the individual line-item information for the products ordered. You can then have Access calculate the totals and print them (in a specific format) on the form. Additionally, you can have Access output records into an invoice report, a table that summarizes the invoice.

When you design your database tables, keep in mind all the types of information you want printed. Doing so ensures that the information you require in your various reports is available from within your database tables....