Access Database Objects and Views

If you’re new to databases (or even if you’re an experienced database user), you need to understand some key concepts before starting to build Access databases. The Access database contains seven types of top-level objects, which consist of the data and tools that you need to use Access:

• Table: Holds the actual data

• Query: Searches for, sorts, and retrieves specific data

• Form: Lets you enter and display data in a customized format

• Report: Displays and prints formatted data

• Pages: Publishes data to a corporate intranet

• Macro: Automates tasks without programming

• Module: Contains programs written in the Visual Basic for Applications (VBA) programming language

Datasheets

Datasheets are one of the many ways by which you can view data in Access. Although not a database object, a datasheet displays a list of records from a table in a format similar to an accounting spreadsheet or Excel worksheet. A datasheet displays data as a series of rows and columns (comparable to an Excel spreadsheet). A datasheet displays a table’s information in its raw form. The datasheet view is the default mode for displaying all fields for all records.

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

Queries

Queries extract information from a database. A query selects and defines a group of records that fulfill a certain condition. Many forms and most reports are based on queries that pre-filter data before it is displayed. Queries are often called from VBA procedures to change, add, or delete database records.

An example of a query is when a person at the Auto Sales office tells the database, “Show me all customers, in alphabetical order by name, who live in Massachusetts and bought something over the past six months, and display them sorted by Customer name,” or “Show me all customers who bought cars for a value of $35,000 or more for the past six months and display them sorted by customer name and then by value of the car.”

Instead of asking the question in English words, the person uses the query by example (QBE) method. When you enter instructions into the QBE Design window, the query translates the instructions into Structured Query Language (SQL) and retrieves the desired data. Chapter 38 discusses the QBE Design window and building queries.

In the first example, the query first combines data from both the Sales and Contact tables, using the related field Contact ID (the common link between the tables). Next, it retrieves the first name, last name, and any other data you want to see. Access then filters the records, selecting only those in which the value of the sales date is within six months of the current date. The query sorts the resulting records first by contact’s last and first names. Finally, the records appear onscreen in a datasheet.

A similar action takes place for the second example—using sales, contacts, invoice items, and products and the criteria applied to the search is where the Description field has a car bought whose value in the Price field is greater than or equal to $35,000.

After you run a query, the resulting set of records may be used in a form that is displayed onscreen or printed in a report. In this way, user access is limited to the data that meets the criteria in the returned records.

Data-entry and display forms

Data-entry forms help users get information into a database table quickly, easily, and accurately. Data-entry and display forms provide a more structured view of the data than what a datasheet provides. 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 restrict access to certain fields within the table. Forms also check the validity of your data before it is added to the database table.

Most users prefer to enter information into data-entry forms rather than datasheet views of tables. Data-entry forms often resemble familiar paper documents and can aid the user with data-entry tasks. 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. Access supports several different types of reports. A report may list all records in a given table (such as a customer table) or may list only the records meeting a certain criterion, such as all customers living in the State of Washington. You do this by basing the report on a query that selects only the records needed by the report.

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 relevant data) and related records in the sales table to print the individual line-item information for the products ordered. You then instruct Access to 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 printed document that summarizes the invoice.

Designing the system’s objects

To create database objects, such as tables, forms, and reports, you first complete a series of tasks known as design. The better your design is, the better your application will be. The more you think through your design, the faster you can complete any system. The design process is not some necessary evil, nor is its intent to produce voluminous amounts of documentation. The sole intent of designing an object is to produce a clear-cut path to follow as you implement it.