Access 2007: Forms, Queries, and Reports
Forms, Queries, and Reports share a similarity in that they all are named and stored in a database file. In this lesson, you will learn what these tools are and how to use them. Before working with these tools, you may want to view information about basic operators and wildcards if you aren't already familiar with these concepts.
To help simplify your database design, you can use a calculated field to take data from a field in your database and perform some manipulation to generate new information. You also can pull data from more than one field and combine the information to create a new field. Note this concept applies to generating data for forms, queries, and reports. For those unfamiliar with creating database expressions, Access provides an Expression Builder that can help you enter a formula in an acceptable format. For example to use Expression Builder with a query, first open the query from within the All Tables navigation column. Right-click the cell where the expression should be inserted (e.g., the Field cell for a calculated field or the Criteria cell for a filter condition).
Access forms provide a way of collecting data and storing it in your database simultaneously. Using forms to allows you to enter data without having to use Datasheet view with the accompanying scrolling back and forth within rows. Forms can be modified anytime you like. You can even create different forms corresponding to the way data is collected (for example, you might have one for data entry staff or one for managers).
Following are tools you can use to create forms:
- Use Autoform if you want to include all fields in a table and don't care about having control over the style applied to the form. There are three types of AutoForms: Simple forms that display one record at a time; Split forms that display all records as a datasheet at the top and the current record at the bottom; and Multiple Items forms that show all records in a columnar datasheet format (works best for table with just a few fields).
- Use the Form Wizard if you want to choose specific fields to include from one or more tables and you also want to select the style for your form.
- Use Blank Form only if you are an experienced user and want to create a form from scratch.
To create a form using Autoform:
- Open the table that contains the data you want to include in the form.
- Select the Create tab.
- Begin creating your form by clicking either
[Forms
button], 
,
[Split Forms button] or 
[Multiple
Items Forms button] in the Forms group based on the type of form you want
to create. - Save the form by selecting
[Save
button] on the Quick Access toolbar. - When you want to use the form to enter , simply select it from the All Tables column on the left side of the Access window.
To create a form using the Form Wizard:
- Select the Create tab.
- Launch the wizard by clicking
in
the Forms group and
selecting "Form Wizard" from the pop-up menu that appears. - Select the table to use in the Form Wizard dialog box.
Click
after making a formatting
choice. - Add the field(s) to include selecting an entry under Available
Fields and clicking
[Add
button]. To add all fields, simply click 
[Add
All button]. To remove a field you added in error, select the field
and click 
[Remove
button]. - Select the layout for your form by clicking one of the radio buttons (Columnar, Tabular, Datasheet, or Justified).
- Select a form style from the list of options. Take into consideration fancy styles usually take longer to load.
- Type a title for your form and click
.
To create a blank form from scratch:
- Select the Create tab.
- Begin creating your form by clicking
in
the Forms group.
An empty form is displayed containing a a list of fields in all the tables
in your database. - Add the fields you want to include by dragging them into the report area or by double-clicking a field name.
- Right-click the tab at the top of the report area and select "Save" to keep the form.
Queries provide a more powerful way of pulling information from your database than the simple find, filter, and sorts you have previously seen. Key fields (the primary and foreign key) relate your tables to one another. Queries use these fields to match records in one table to another. Setting up the proper table relationships beforehand helps to simplify the process. When creating a query, you need to know which tables to use; how the tables are related to each other; which fields contain the data you want; and what fields you need in the solution.
As was the case with Forms, there are different methods you can use to create a query:
- Use the Simple Query Wizard if you are new to queries. The Wizard walks you through the process of selecting tables and fields and also allows you to add summary calculations (for example, counting records) to your query. In addition to Simple Query, there are three other Query wizards: Crosstab Wizard summarizes multiple rows of data into a speadsheet-type format; Find Duplicates Query Wizard locates duplicate records in your database caused by data entry errors or problems with imported data; and Unmatched Query Wizard finds unrelated records in two tables that share a common field.
- Use Query Design if you want to build a query from scratch.
To create a query using the Wizard:
- Select the Create tab.
- Launch the wizard by clicking
[Query
Wizard button] in the Other group and selecting "Simple
Query Wizard" from the pop-up
menu that appears. Click 
. - Add the field(s) to include by selecting an entry under Available
Fields and clicking [Add
button]. To add all fields, simply click [Add
All button]. To remove a field you added in error, select the field
and click [Remove
button].
- Click and type a
title for the query. Leave the option Open the query to view
information selected,
and then click .
- Review the results.
To create a query from scratch:
- Select the Create tab.
- Launch the query builder by clicking
[Query
Design button] in the Other group to open the Show
Table dialog box. - Select the table(s) to include in your query by double-clicking an
entry on the Table tab. When finished, click
. - Select the field(s) to include from the Table(s) displayed at the top
of the window by double-clicking an entry. Do this in the order you want
them to appear in the query results. Tip: If you select a column in error,
click the Field name cell and then click
from
the Query Setup group
on the Ribbon. - To sort a particular field in your query results, click the Sort cell
within the field's column. Click
[Down
arrow] and select Ascending or Descending. - To add criteria for a particular field, type the expression in the Query cell within the field's column. For example, to find a total greater than 100, you would type > 100 in the cell. See more examples.
- Save the query design by clicking
[Save
icon] in the Quick
Access Toolbar at the
top of the Access window and typing a name when prompted. When finished,
click . - Run your query and view the results by clicking
[Run
button] in the Results group on the Query Tools contextual
tab.
After you have added fields to the query grid a the bottom of the Query workspace, it's possible to keep a field from being included in the final results by deselecting the checkbox in the Show cell within the field's column.
The Access report tools allow you to create a hard copy of selected data. The difference between simply printing the raw data in a table and creating a report is that Reports give you the ability to fine tune-tune formatting as well as use options for grouping and summarizing data. Like queries, reports are special database objects that you can prepare and keep on hand for future use. Access allows you to produce reports based on queries when you want to include only selected information.
Following are tools you can use to create forms:
- Use the Report tool if you want to include all fields from a single table in your report and you don't care about doing any special formatting. This tool does not allow you to do any formatting, so the report you get will look much how the table appears in DataSheet view.
- Use the Report Wizard if you prefer to select the fields to include from one or more tables. This tool also gives you the ability to customize the appearance of the report.
- Use Blank Report only if you are an experienced user and want to create a report from scratch.
To create a one-table report using the Report tool:
- Open the table to use for your report.
- Select the Create tab.
- Create your report by clicking [Report] in the Forms group. A preformatted report is displayed containing a series of rows and columns.
- Get a hard copy of your report by clicking
[Microsoft
Office Button] and selecting "Print" from the pop-up menu that
appears.
The Report tool gives you some choices for modifying the appearance of your report. When you create the report a contextual tab appears at the top of the Ribbon. It contains two additional tabs, Format and Arrange. Review these tabs to see the available options.
To create a report using the Report Wizard:
- Select the Create tab.
- Begin creating your form by clicking
in
the Forms group.
The Report Wizard dialog box appears listing all fields in the active table
(or first table, if none are open). - Select the table to start with from the Tables/Queries pop-up menu.
- Add the field(s) to include by selecting an entry under Available
Fields and clicking [Add
button]. To add all fields, simply click [Add
All button]. To remove a field you added in error, select the field
and click [Remove
button].
- Continue selecting tables and fields for your report. When finished,
click .
- Optional: Add a grouping level (for example, a name field) by
selecting the field and clicking [Add
button]. Click
to
select an interval for group-level fields. Click . - Choose a first-level, second-level, etc. sort order for your report
by selecting a sort field from the associated pop-up menu. Click
to
toggle between Ascending and Descending sort order. Click . - Select a report layout and orientation by clicking the appropriate radio
buttons, and click .
- Select a report style from the list of options, and click .
- Leave the Print Preview option selected, and type a
title for your report and click .
The Print Preview contextual tab appears. It contains
a number of tools for adding or modifying print options. If no further
changes are needed, click
[Close
Print Preview button]. - Get a hard copy of your report by clicking [Microsoft
Office Button] and selecting "Print" from the pop-up menu that
appears.
To create a blank report from scratch:
- Select the Create tab.
- Begin creating your form by clicking
in
the Forms group.
An empty report is displayed containing a a list of fields in all the tables
in your database. - Add the fields you want to include by dragging them into the report area or by double-clicking a field name.
- Add any other elements to include (e.g., logo, title, page numbers, and date/time) and apply any desired formatting.
- Right-click the tab at the top of the report area and select "Save" to keep the report.