Applies to
Microsoft Office Access 2003 Microsoft Access 2002

This article was adapted from from Chapter 1 of Microsoft Office Access 2003 Step by Step by Online Training Solutions, Inc. Visit Microsoft Learning to buy this book.

Access is part of the Microsoft Office System, so the basic interface objects — menus, toolbars, dialog boxes — will be familiar if you have used other Office products or other Microsoft Windows programs. However, Access has more dimensions than most of those programs, so it might seem more complex until you become familiar with it.

This book gives you straightforward instructions for using Access to create databases. It takes you from knowing little or nothing about Access — or, for that matter, about databases — to a level of expertise that will enable you to develop database programs for use by one person or by many.

This article introduces you to the concept of a database, explains a little about Access, and takes you on a tour of the program. The illustrations of parts of the database show The Garden Company, a fictional garden supply and plant store. This tour will give you a firm foundation from which to begin working with Access to create your own databases. After exploring the structure of the GardenCo database, you will look at some of the objects used to store and manipulate the data it contains. Finally, you will learn how to preview and print the various Access objects.

Understanding databases

In its most basic form, a database is the computer equivalent of an organized list of information. Typically, this information has a common subject or purpose, such as the list of employees shown here:

ID	Last name	First name	Title	Hire date
1	Daly	Jim	Sales Rep	May 1, 1992
2	Flood	Kathie	V.P., Sales	Aug 14, 1992
3	Lee	Mark	Sales Rep	Apr 1, 1992
4	Philips	Carol	Sales Rep	May 3, 1993

This list is arranged in a table of columns and rows. Each column (field) stores a particular type of information about an employee: first name, last name, date of hire, and so on. Each row (record) contains information about a different employee.

If a database did nothing more than store information in a table, it would be as useful as a paper list. But because the database stores information in an electronic format, you can manipulate the information in powerful ways to extend its utility.

For example, a phone book for your city is probably sitting on a shelf within a few feet of you. If you want to locate a person or a business in your city, you can do so, because the information in the telephone book is organized in an understandable manner. If you want to get in touch with someone a little further away, you can go to the public library and use its collection of phone books, which probably includes one for each major city in the country. However, if you want to find the phone numbers of all the people in the country with your last name, or if you want the phone number of your grandmother's neighbor, these phone books won't do you much good because they aren't organized in a way that makes that information easy to find.

When the information published in a phone book is stored in a database, it takes up far less space, it costs less to reproduce and distribute, and, if the database is designed correctly, the information can be retrieved in many ways. The real power of a database isn't in its ability to store information; it is in your ability to quickly retrieve exactly the information you want from the database.

Discovering what's special about Access

Simple database programs, such as the Database component of Microsoft Works, can store information in only one table, which is often referred to as a flat file. These simple databases are often called flat databases. More complex database programs, such as Access, can store information in multiple related tables, thereby creating what are often referred to as relational databases. If the information in a relational database is organized correctly, you can treat these multiple tables as a single storage area and pull information electronically from different tables in whatever order meets your needs.

A table is just one of the types of objects that you can work with in Access. The following graphic shows all the Access object types:

Tip  For maximum compatibility with existing databases, the default format for new databases created with Access 2003 is Access 2000.

Of all these object types, only one — the table — is used to store information. The rest are used to manage, manipulate, analyze, retrieve, display, or publish the table information — in other words, to make the information as accessible and therefore as useful as possible.

Over the years, Microsoft has put a lot of effort into making Access not only one of the most powerful consumer database programs available, but also one of the easiest to learn and use. Because Access is part of the Office System, you can use many of the techniques you know from using other Office programs, such as Microsoft Office Word and Microsoft Office Excel, when using Access. For example, you can use familiar commands, buttons, and keyboard shortcuts to open and edit the information in Access tables. And because Access is integrated with other members of the suite, you can easily share information between Access and Word, Excel, or other programs.

Opening an existing database

The Garden Company's database contains information about its employees, products, suppliers, and customers that is stored in a series of tables. As you read this article, you will learn how to work with these tables to develop an assortment of queries, forms, reports, data access pages, macros, and modules that can be used to enter, edit, and manipulate the information in the tables in many ways.

In this exercise, you will explore some of the objects in the GardenCo database. You won't find a lot of detailed explanation here, because this is just an overview.

1. On the taskbar, click the Start button, point to All Programs and then Microsoft Office, and click Microsoft Office Access 2003.

   As with other Office programs, Access has a menu bar and one or more toolbars across the top of the window. When you click either New or File Search on the File menu, or click Office Clipboard on the Edit menu, the New File task pane is displayed at the right side of this window.

   

2. The database's switchboard appears. A switchboard is used to easily access the database objects needed to perform common tasks.

   

3. Click Close Switchboard to close the switchboard.

   The GardenCo database window appears.

   

   Across the top of the window is a toolbar and along the left edge is the Objects bar, which lists the Access database objects. Because Tables is selected, the right pane of the window lists the tables contained in the database.

4. On the File menu, click Close to close the GardenCo database.

Exploring tables

Tables are the core database objects. Their purpose is to store information. The purpose of every other database object is to interact in some manner with one or more tables. An Access database can contain thousands of tables, and the number of records each table can contain is limited more by the space available on your hard disk than anything else.

Tip  For detailed information about Access specifications, such as the maximum size of a database or the maximum number of records in a table, click the Ask A Question box at the right end of the menu bar, type Access specifications, and press the ENTER key.

Every Access object has two or more views. For tables, the two most common views are Datasheet view, in which you can see and modify the table's data, and Design view, in which you can see and modify the table's structure. Clicking the View button toggles the view of the open table between Datasheet and Design views. You can also click the down arrow to the right of the View button and select a view from the drop-down list.

When you view a table in Datasheet view, you see the table's data in columns (fields) and rows (records).

If two tables have one or more fields in common, you can embed the datasheet for one table in another. With the embedded datasheet, which is called a subdatasheet, you can see the information in more than one table at the same time. For example, you might want to embed an Orders datasheet in a Customers table so that you can see the orders each customer has placed.

In this exercise, you will explore the tables in the GardenCo database in different views.

1. On the Objects bar, click Tables.
2. On the toolbar at the top of the Database window, click the Details button.

   A description of each of the objects listed in the window is displayed to the right of its name.

   Tip  You can resize the columns in the database window by dragging the vertical bar that separates columns in the header. You can set the width of a column to the width of its widest entry by double-clicking the vertical bar.

3. Click the Maximize button in the upper-right corner of the Database window.

   The Database window expands to fill the Access window. Note that the first three items in the Name column are not tables; they are shortcuts to three commands you can use to create a new table.

   Tip  If you don't want these shortcuts at the top of each list of objects, on the Tools menu, click Options, click the View tab in the Options dialog box, clear the New object shortcuts check box, and then click OK.

4. Click the Restore Down button.

   The Database window shrinks.

5. Click the Categories table, and then click the Open button at the top of the Database window to open the table in Datasheet view.

   

   This datasheet contains a list of the categories of products sold by The Garden Company. As you can see, there are fields for Category ID, Category Name, and Description.

6. Click the plus sign to the left of the record for the Bulbs category.

   Clicking the plus sign expands an embedded subdatasheet. The category records from the Categories table and product records from the Products table are displayed simultaneously.

   

   Notice that the plus sign has changed to a minus sign.

7. Click the minus sign to the left of the Bulbs record to collapse the subdatasheet.
8. On the File menu, click Close to close the Categories table. If you are prompted to save changes to the table layout, click Yes.

   Tip  You can also close a window by clicking the Close button in the window's upper-right corner. When an object window is maximized, this button is called the Close Window button to avoid confusion with the Close button at the right end of the Access window's title bar. Be careful to click the correct button, or else you will quit Access.

9. Double-click Orders to open the table in Datasheet view.

   

   The navigation area at the bottom of the window indicates that this table contains 87 records and that the active record is number 1.

10. Move the selection one record at a time by clicking the Next Record button several times.

    The selection moves down the OrderID field, because that field contains the insertion point.

    Tip  You can move the selection one record at a time by pressing the UP ARROW or DOWN ARROW key, one screen at a time by pressing the PAGE UP or PAGE DOWN key, or to the first or last field in the table by pressing CTRL+HOME or CTRL+END.

11. Move directly to record 40 by selecting the current record number, typing 40, and pressing ENTER.
12. Close the Orders table, clicking No if you are prompted to save changes to the table's layout.
13. Double-click Products in the list of tables to open it in Datasheet view.

    Notice that this table contains 189 records.

14. On the toolbar, click the View button to switch the view of the Products table to Design view.

    

    In Datasheet view, you see the data stored in the table, whereas in Design view, you see the underlying table structure.

Exploring queries

One way you can locate information in an Access database is to create queries. You use queries to locate information so that you can view, change, or analyze it in various ways. You can also use the results of queries as the basis for other Access objects.

A query is essentially a question. For example, you might ask, “Which records in the Customer table have the value 98052 in the Postal Code field?” When you run a query (the equivalent of asking a question), Access looks at all the records in the table or tables you have specified, finds those that match the criteria you have defined, and displays them in a datasheet.

For Access to be able to answer your questions, you have to structure queries in a specific way. Each type of question has a corresponding type of query. The primary query types are select, crosstab, and parameter. Less common types are action, AutoLookup, and SQL (Structured Query Language). Access includes wizards that guide you through the creation of the common queries; less common ones have to be created by hand in a design grid in Design view. Here's what a typical query looks like:

At the top of this query window are four small windows listing the fields in the four tables that will be included in this query. The lines connecting the tables indicate that they are related by virtue of common fields. The first row of the grid contains the names of the fields to be included in the query, and the second row shows which table each field belongs to. The third row (labeled Total) performs calculations on the field values, and the fourth row indicates whether the query results will be sorted on this field. A check mark in the check box in the fifth row (labeled Show) means that the field will be displayed in the results datasheet. (If the check box isn't selected, the field can be used in determining the query results, but it won't be displayed.) The sixth row (labeled Criteria) contains criteria that determine which records will be displayed, and the seventh row (labeled or) sets up alternate criteria.

Don't worry if this all sounds a bit complicated at the moment. When you approach queries logically, they soon begin to make perfect sense. And don't worry if they sound like a lot of work. The Query Wizard is available to help you structure the query, and if you create a query that you are likely to run more than once, you can save it. It then becomes part of the database and is displayed in the database window when you click Queries on the Objects bar.

In this exercise, you will explore a few of the queries that have already been defined and saved in the GardenCo database.

1. On the Objects bar, click Queries.

   The Database window displays all the queries that have been saved as part of the GardenCo database.

2. Double-click the title bar of the Database window to maximize the window.

   The top two entries in this window are commands for creating queries. The remaining entries are queries that have already been created.

3. Click the Details button.

   

   The description of each query explains its purpose. The icon in the Name column is an indication of the query's type, as is the information in the Type column, which you can see by scrolling the window to the right.

4. Click the Restore Window button on the menu bar (not the title bar) and then click Restore.

   The Database window is restored to its original size.

5. Open the Products by Category query in Datasheet view by selecting it and clicking the Open button at the top of the Database window.

   When you open the query, Access processes it (described as running a query) and produces a datasheet that displays the results.

   

   The navigation bar tells you that 171 records are displayed. The Products table contains 189 records. To find out why 18 of the records are missing, you need to look at this query in Design view.

6. On the toolbar, click the View button to view the query in Design view.

   

   In the top part of the query window, two boxes list the fields of the tables this query is designed to work with. The bottom part is the design grid, where the query is formed. Each column of the grid can refer to one field from one of the tables above. Notice that <> Yes (not equal to Yes) has been entered in the Criteria row for the Discontinued field. This query therefore finds all the records that don't have a value of Yes in that field (in other words, that have not been discontinued).

7. As an experiment, in the Criteria row of the Discontinued field, select the text <>Yes, type =Yes, and then click the Run button on the toolbar.

   Tip  You can also run a query by switching to Datasheet view.

   The query is changed so that it now finds all the records that have a value of Yes in the Discontinued field (in other words, that have been discontinued).

   

   The 18 discontinued products account for the difference in the number of records in the Products table and the number of records displayed by the original query.

8. Close the query window, clicking No when prompted to save the design changes.

Exploring forms

Access tables are dense lists of raw information. If you create a database that only you will use, you will probably be very comfortable working directly with tables. But if you create a database that will be viewed and edited by people who don't know much about it — and don't necessarily want to know about it — working with your tables might be overwhelming. To solve this problem, you can design forms to guide users through your database, making it easier for them to enter, retrieve, display, and print information.

A form is essentially a window in which you can place controls that either give users information or accept information that they enter. Access provides a toolbox that includes many standard Windows controls, such as labels, text boxes, option buttons, and check boxes. With a little ingenuity, you can use these controls to create forms that look and work much like the dialog boxes in all Windows programs.

You use forms to edit the records of the underlying tables or enter new records. As with tables and queries, you can display forms in several views. The three most common views are Form view, in which you enter data; Datasheet view, which looks essentially like a table; and Design view, in which you work with the elements of the form to refine the way it looks and works. This graphic shows what a form looks like in Design view:

This form consists of a main form that is linked to just one table. But a form can also include subforms that are linked to other tables. Arranged in the form are label controls containing text that appears in the form in Form view, and text box controls that will contain data from the table. Although you can create a form from scratch in Design view, you will probably use this view most often to refine the forms you create with a wizard.

In this exercise, you will take a look at a few of the forms in the GardenCo database that have been designed to make viewing tables, editing existing information, and adding new information easier and less error-prone.

1. On the Objects bar, click Forms, and then double-click Switchboard to open the main switchboard.

   

   The Switchboard form has a customized title bar at the top, a title for the GardenCo database, and four command buttons. The first two buttons open switchboards — other forms — that have the same name as the button.

2. On the switchboard, click the Forms button to display the Forms switchboard.
3. Click Edit/Enter Orders to display the Orders form.

   

   This form consists of a main form and a subform.

4. On the navigation bar, click the Next Record button to display that record's information.
5. Click the New Record button (the one with the asterisk) to display a blank form where you could enter a new order.
6. Close the Orders form, and in the Forms switchboard, click Return to redisplay the main switchboard.
7. Click the Close Switchboard button.
8. In the Database window, double-click Products in the Forms list to open the form.

   

   You use this form to edit the records of current products or enter new ones.

9. You are currently looking at the form in Form view. On the toolbar, click the down arrow to the right of the View button, and click Datasheet View.

   Now the form looks essentially like the Products table in Datasheet view but without gridlines.

   

10. Click the View button again to switch to Design view, and then maximize the form window.
11. If the toolbox is not displayed, on the toolbar, click the Toolbox button.

    

    Tip  If the toolbox is in the way, drag it by its title bar to a location where it's not obscuring anything.

12. Point to each of the icons in the toolbox until the name of the tool is displayed.

    These are the tools you use to build custom forms for your database.

13. Close the toolbox.
14. Close the Products form.

Exploring reports

You use reports to display the information from your tables in nicely formatted, easily accessible ways, either on your computer screen or on paper. A report can include items of information selected from multiple tables and queries, values calculated from information in the database, and formatting elements such as headers, footers, titles, and headings.

You can look at reports in three views: Design view, in which you can manipulate the design of a report in the same way that you manipulate a form; Print Preview, in which you see your report exactly as it will look when printed; and Layout Preview, which shows you how each element will look but without all the detail of Print Preview. A report in Design view looks like this:

In this exercise, you will take a look at a report that has been saved as part of the GardenCo database, just to get an idea of what is possible.

1. On the Objects bar, click Reports.

   The top two entries in this window are commands you can use to create reports. The remaining entries are reports that have already been created.

2. Click Customer Labels, and then click the Preview button at the top of the Database window to display the report.

   This report prints customer names and addresses in a mailing label format. You are looking at it in a view that is much like Print Preview in other Microsoft Office programs.

   Tip  Access provides a wizard that can help you create a mailing label report. You can also use the Customer table in this database with the mail merge feature of Word to create these labels.

3. Click in the form to change the zoom level.

   Tip  If the report is too small to read in Print Preview, you can also select a zoom level in the Zoom box on the toolbar.

4. Close the Customer Labels report.
5. In the Database window, select the Invoice report, and click the Preview button to see the invoice.

   

6. Check out each page by clicking the navigation buttons at the bottom of the window.
7. On the toolbar, click the View button to display the report in Design view, and then maximize the report window.

   

   In this view, the report looks similar to a form, and the techniques you use to create forms can also be used to create reports.

8. Close the report.

Exploring other Access objects

Tables, queries, forms, and reports are the objects you will use most frequently in Access. You can use them to create powerful and useful databases. However, if you need to create a sophisticated database, you can use data access pages, macros, and modules to substantially extend the capabilities of Access. To round out this introduction to Access databases, this section provides a brief overview of these objects.

Pages

To enable people to view and manipulate your database information over an intranet or the Internet, you can create pages, also known as data access pages. Working with a data access page on the World Wide Web is very much like working directly with a table or form in Access — users can work with the data in tables, run queries, and enter information in forms.

Although publishing database information on the Web seems like a fairly difficult task, Access provides a wizard that does most of the tedious work of creating data access pages for you. You can use a wizard-generated page as-is, or you can add your own personal touch in Design view.

Macros

You use macros to have Access respond to an event, such as the click of a button, the opening of a form, or the updating of a record. Macros can be particularly handy when you expect that other people who are less experienced with Access than you will work in your database. For example, you can make routine database actions, such as opening and closing forms or printing reports, available as command buttons on switchboards. And by grouping together an assortment of menu commands and having users carry them out by using a macro with the click of a button, you can ensure that everyone does things the same way.

Modules

More powerful than macros, modules are Microsoft Visual Basic for Applications (VBA) programs. VBA is a high-level programming language developed by Microsoft for the purpose of creating Windows programs. A common set of VBA instructions can be used with all programs in the Office System, and each program has its own set as well. Whereas macros can automate four to five dozen actions, VBA includes hundreds of commands and can be extended indefinitely with third-party add-ins. You could use VBA to carry out tasks that are too complex to be handled with macros, such as opening an Excel spreadsheet and retrieving specific information.

Tip  The Office installation CD-ROM includes several sample databases that illustrate many of the principles of creating and using a database. One of these, the Northwind Traders database, is used as an example in many topics in Access online Help, so it is a particularly good database for you to explore. You'll find a link to this database on the Access Help menu, under Sample Databases.

Previewing and printing Access objects

Because Access is a Windows program, it interacts with your printer through standard Windows dialog boxes and drivers. This means that any printer that you can use from other programs can be used from Access, and any special features of that printer, such as color printing or duplex printing, are available in Access.

As you have seen in this article, you can use different Access objects — tables, forms, reports, and so on — to display the information stored in your database. Within each object there are several views available: Design view, Datasheet view, and so on. You can choose the view you want by selecting it from the View menu (the views available will depend on the object that is active).

Like most Windows programs, Access includes the Page Setup, Print Preview, and Print commands on the File menu. These commands are available when their use would be appropriate, which is determined by the object displayed and the current view of that object.

This table shows the relationships for the primary objects:

View/Object	Table	Query	Form	Report
Design			PP, P	PP, P
Datasheet	PP, P	PP, P	PP, P
PivotTable	PP, P	PP, P	PP, P
PivotChart	PP, P	PP, P	PP, P
SQL
Form			PP, P
Layout Preview

If a cell is shaded, that object supports that view. PP in a cell indicates Print Preview is supported for that object in that view. P indicates that Print and Page Setup are supported for that object in that view.

The less common objects — Pages, Macros, and Modules — offer more limited views and print commands, as shown here:

View/Object	Page	Macro	Module
Design		PP, P
Page	P
Web Page Preview
Code			P
Object			P

These tables make the process of previewing and printing your data seem a little complex, but the point is that the appropriate print commands are generally available when you need them.

Tip  When printing tables in Datasheet view, you will often find that printing in Landscape orientation will provide the best image.

In this exercise, you will learn how to preview and print employee information that is in a table and a form in the GardenCo database.

1. On the Objects bar, click Tables.
2. Double-click the Employees table to open it in Datasheet view.

   

   This table contains information about nine employees. You can see that there are more fields than will fit on the screen.

3. On the File menu, click Print Preview to display the first page of the datasheet printout.

   

4. Move the pointer over the table — the pointer will become a magnifying glass — and then click to zoom in. Click again to return to a reduced view.
5. Click the Next Record button at the bottom of the screen to preview the next page. Click it again.

   You can see that there will be three short pages if you print this datasheet.

6. On the toolbar, click the Setup button to display the Page Setup dialog box.

   

   This is the same dialog box you would see if you clicked Page Setup on the File menu. You can use this dialog box to control margin and page layout settings.

7. On the Page tab, click Landscape and then OK.

   The preview page is displayed lengthwise across the screen, and displays more fields. There are now only two pages.

8. Click the Print button to send this datasheet to your default printer.

   Tip  If your computer is connected to more than one printer and you would like to send a job to a printer other than the default one, on the File menu, click Print, and then select the desired printer from the list near the top of the dialog box.

9. Click the Close button to close Print Preview.
10. On the File menu, click Close to close the datasheet.
11. On the Objects bar, click Forms.
12. Double-click the Employees form to open it in Form view.

    

    The information for each employee appears on its own page. Notice that there are two tabs at the top of the page, one for company information and one for personal information.

13. Click the Personal Info tab to see the information that is listed there, and then return to the Company Info tab.
14. On the toolbar, click the Print Preview button to preview the printout.

    

    Notice that the preview shows only the information that was on the active tab. If you want to print the information on a different tab you need to make that tab active first.

15. Click the Close button to close the Print Preview window.
16. On the File menu, click Print to display the Print dialog box.

    

    The dialog box you see will depend on the printer you have installed. Use this dialog box to select a printer, set the range of pages to be printed, and do other things.

17. Click Close to close the dialog box.

Key points

• Access is part of The Microsoft Office System, so the basic interface objects — menus, toolbars, dialog boxes — work basically the same as other Office products or other Microsoft Windows programs.
• A database is the computer equivalent of an organized list of information. The power of a database is in your ability to quickly retrieve precise information from it. In Access, data is organized in tables comprised of columns and rows, called fields and records. Access is a relational database, so you can treat the multiple tables in one database as a single storage area and easily pull information from different tables in whatever order and format suits you.
• A table is just one of the types of objects that you can work with in Access. The other object types are: queries, forms, reports, data access pages, macros and modules. Tables are the core database objects and the purpose of every other database object is to interact with one or more tables.
• Every Access object has two or more views. For example, you view data in a table in Datasheet view and define how the data is displayed in Design view.
• One way you can locate information in an Access database is to create and run a query. You use queries to locate information so that you can view, change, or analyze it in various ways. Queries can be viewed in Datasheet view or Design view, but you can also use the results of a query as the basis for other Access objects, such as a form or report.
• Forms make it easy for users to enter, retrieve, display and print information stored in tables. A form is essentially a window in which you can place controls that either give users information or accept information they enter. Forms can be viewed in Form view, Datasheet view, or Design view.
• Reports display information from your tables in a nicely formatted, easily accessible way, either on your computer screen or on paper. A report can include items of information selected from multiple tables and queries, values calculated from information in the database, and formatting elements such as headers, footers, titles, and headings. Reports can be viewed in Design view, Print Preview, and Layout Preview.
• Data access pages, macros, and modules substantially extend the capabilities of Access. Data access pages enable people to view and manipulate your database information over an intranet or the Internet. Macros can be used to make routine database actions available as command buttons in forms, which help less experienced users work in your database. Modules are Visual Basic for Applications (VBA) programs. Whereas macros can automate many actions, VBA can be used to carry out tasks that are too complex to be handled with macros.