The Six Types of Access Objects
Access databases are made up of objects - things you can create, edit, and delete, each with its own name and settings. Object-oriented systems allow you to create these things one piece at a time, using pieces that fit together.
Access contains various kinds of objects, including objects for storing, displaying, and printing your data, as well as objects that contain programs you write. At first, you will probably use only a few types of objects, but as you customize your database, you may end up using them all. You start with tables for storing data, forms for editing data on-screen, reports for printing data, and queries for selecting and combining data. Later, you may create macros and modules, which contain programs that you write. In this section, we cover each of the main types of Access objects: tables, queries, forms, reports, macros, and modules.
Tables for Storing your Data
Tables are where you put your data. A table is an Access object that is made up of a series of records - the electronic equivalent of the index cards that make up an address list. Each record contains information in the same format. In an address list, each record contains information about one person: name, address, and other facts. Each individual piece of information - such as first name, last name, or street address - is called a field.
Your database can contain many tables. A bookstore database (for example) can contain a table of books (with title, publisher, price, and other information about each book), a table of vendors from whom you buy books (with company name, address, discount terms, and other information about each vendor), and maybe a table of your regular customers (with name, address, and other information). Figure shows a table of names and addresses. Each row is a record, and the fields are shown in columns.
After you set up tables in your database and type in (or import) information, you can sort the records, select records that match a criterion, and then display and print the records.
Proper design of your tables - choosing how many tables to create and which fields are stored in which table - is key to creating a usable and flexible database.
Queries for Selecting your Data
Queries are operations that slice and dice your data to answer specific data needs. The most commonly used type of query helps you select data from a table, perhaps to select which records you want to include in a report. You can create a query that shows you all the people in your address book who live in (say) Vermont, or all those for whom you don’t have a phone number. To create this type of query, you enter criteria that specify what values you want to match in specific fields in the tables (for example, VT in the State field to find Vermonters, or nothing in the Phone Number field to find the phoneless, or both).
You can also use queries to combine information from several tables. A bookstore database may store book author names in the Books table and book ordering information in the Purchase Orders table. A query can pull information from both these tables - to show (for example) all the John Lowiss novels you ordered for the last month. Queries can also create calculated fields, including totals, counts, and averages.
Another type of query is the action query, which does something to the records you select - copy records from one table to another, make a change to all the records you select, delete records you select, that sort of thing. Crosstab queries help you analyze the information in your tables by summarizing how many records contain specific combinations of values.
Queries are the way you get useful information out of your tables - and you will probably create zillions of them as you play with your database.
Forms for editing and displaying your data
An easy way to enter data, especially into more than one related table, is to use a form - a standard database document that displays information from one or more tables on-screen. You can have all kinds of fun with forms, for example, you can
- Edit your data or type in new records.
- Choose the layout of the table’s information on the form.
- Specify the order in which your items appear.
- Group items together with lines and boxes.
- Use pull-down lists, radio buttons, and other types of on-screen controls for entering and editing data.
But why stop there? You can build intelligence into forms, too - program some smart boxes that automatically capitalize what you type in, or check your entry against a table of valid values.
After your database goes into production - that is, you use it for its intended purpose - forms become the most-used Access object.
Reports for printing your data
Forms are primarily designed to appear on-screen; reports (on the other hand) are designed to be printed out. Like forms, reports display information from tables; you get to choose the layout of the information. Most reports are based on queries, you use a query to choose the information that appears in the report. The report design defines the order in which records appear, which fields appear where, and which fonts, font sizes, lines, and spacing to use.
In addition to reports on normal paper, you can create reports for printing on envelopes, labels, or other printed forms. Access comes with report wizards that make creating fancy reports easy. It can also print charts and cross-tabulations (crosstabs) based on the data in your database.
Macros for saving keystrokes
Access includes two separate programming languages: one for macros and a separate one (VBA) for larger programs. Macros are programs that automate the commands you give when you use Access - you "write" them by telling Access to record your keystrokes while you do something on-screen. For example, you can write a macro that moves the cursor to the last record in the Orders table whenever you open the Order Entry form. (What are the chances that you’d want to edit your very first order? Most of us would be likelier to want to edit the last order or enter a new order.) Or you can write a macro that moves your cursor to the next applicable blank in a form, based on the entries you made so far.
After you get some practice at creating macros, you can create buttons on your forms that run the macros with a quick click. You can also tell your form to run a macro automatically whenever you move to a field on the form, or enter data into the field - handy.
You do not have to be a programmer to create macros. Access helps you write them by providing menus of commands.
Modules for writing your own programs
Now we come to the serious programming stuff: modules - another term for Visual Basic programs. VBA (Visual Basic for Applications) is a programming language based on the age-old BASIC language, it is specifically geared for working in Access and other Office programs. Macros are fine for saving a few keystrokes or cleaning up the data you enter in a field, but when the going gets complex, you can use VBA.