If you're looking for a handy guide to working with Microsoft Access, check out Tech Toolshed's Access reference charts. Three of the charts target the basics of working with tables, queries, and reports. A fourth chart provides tips and shortcuts for performing various Access tasks.
Microsoft Access isn't in every user's software repertoire. Those who rely on it for their day-to-day work tend to know it very well. But a lot of users work with Access only occasionally, maybe for a special project or when their manager suddenly needs a slice of data presented in a slick-looking report. Those users are likely to spend as much time trying to get back up to speed on a certain feature or capability as they do actually producing the desired results—and they may tie up coworkers and help desk staff along the way.
A basic understanding of how to build a table, run a query, and generate a report will give such users a head start in tackling the occasional Access-based project or task. Here's a bare-bones look at what they need to get underway.
Easiest way to build an Access table
For less-experienced Access users, the Table Wizard probably offers the best starting point for creating a table. The wizard isn't necessarily more efficient, but it gives novices an edge because specific knowledge about data types and field properties isn't required. The user just needs to identify the type of data the table will store, and the wizard will make most of the decisions. To execute the Table Wizard:
- Press [F11] to display the Database window.
- Click the Tables button on the Database window's Object bar.
- Double-click the Create Table By Using Wizard option.
The wizard will launch and present the first screen (Figure A), which lets the user specify a table, such as a mailing list or a list of employees or orders, and select from a list of fields relevant to that type of data. The wizard will then guide the user through the process of naming the table, assigning a primary key, creating any desired relationships to other tables, and entering data.
Query-building with wizards
Queries allow users to populate forms and reports and display only the data they need. Queries can be based on a single table, multiple tables, or on another query. Access offers a number of wizards to simplify the process of creating a query:
- The Simple Query Wizard creates a simple select query, with one limitation—you can't conditionally limit data.
- The Crosstab Query Wizard walks you through the process of creating a crosstab query.
- The Find Duplicates Query Wizard locates duplicate entries and records.
- The Find Unmatched Query Wizard locates orphan records—child values (or foreign key values) in related tables that don't relate to any parent value (or primary key value).
Choose one of the following methods to launch a query wizard:
- Click on the Queries shortcut in the Database window and then double-click the Create Query By Using Wizard shortcut. This launches the Simple Query Wizard (Figure B).
- Click on the Queries shortcut in the Database window and then click the New button on the Database Window toolbar.
- Choose Query from the Insert menu or from the New Object button's drop-down list.
Fastest way to design a useful report
Although Access provides an arsenal of reporting options in its Design View, users who haven't created many reports will probably find that a wizard is the best way to go. As they get a little more experience, they can use Design View to fine-tune the format of their reports. Sorting out the various types of report wizards can be a little tricky. Here's a look at what Access offers:
- AutoReport—The AutoReport Wizard bases a report on the specified data source and requires no further information. It's the simplest but most limited choice. The result is a columnar report that uses all of the fields and data in the report.
- Report Wizard—The Report Wizard is more flexible than any of the others, allowing users to limit both the fields and the actual data that make it to the finished report. It also lets users retrieve data from more than one data source and enables them to specify groups, to sort, and to summarize numerical data.
- AutoReport: Columnar—The AutoReport: Columnar wizard creates a columnar AutoReport from one data source.
- AutoReport:Tabular—The AutoReport:Tabular wizard generates a tabular AutoReport from one data source.
- Chart Wizard—The Chart wizard creates a report that contains a chart.
- Label Wizard—The Label Wizard creates labels from data.
Using the Report Wizard
The Report Wizard requires a bit of interaction from the user, but since it's the most flexible of the wizards, it's probably the most useful one. To execute this wizard:
- Press [F11] to display the Database window.
- Click the Reports shortcut on the Object bar.
- Double-click Create Report By Using Wizard. The wizard will then launch (Figure C), presenting a series of options for identifying the report's data sources, selecting the fields to be included in the report, and specifying sorting, grouping, formatting, and summarizing functions.
Shortcuts worth remembering
As with most applications, Access offers tons of shortcuts for performing various tasks. And even though no user wants to memorize a list of obscure keyboard combinations, some are genuinely worth mastering in the interest of efficiency. Here are some shortcuts users may find handy.
[Ctrl];—Inserts the current date
[Ctrl][Shift];—Inserts the current time
[Ctrl][Alt][Spacebar]—Inserts the default value for a field
[Ctrl]'—Inserts the value from the same field in the previous record
[F2]—Switches between Edit mode and Navigation mode
[F5]—Jumps to the record number control in the Navigation bar; type the record number and press [Enter]
[F6]—Switches between the upper and lower panes (in Design View)
[Shift][F2]—Opens a criteria expression in the Zoom window (in Design View)
[Alt][Enter]—Displays the Query Properties window (in Design view)
[Ctrl][F4]—Closes the Query Design window (prompting to save changes first)
[Shift][Enter]—Opens the selected report in Print Preview (from the Database window)
[Ctrl][Enter]—Opens the selected report in Design View (from the Database window)
C or [Esc]—Cancels Print Preview or Layout Preview
S—Opens the Page Setup dialog box (in Print Preview or Layout Preview)
P—Opens the Print dialog box (in Print Preview or Layout Preview)
Z—Zooms in or out of a page (in Print Preview or Layout Preview)
Jody Gilbert has been writing and editing technical articles for the past 25 years. She was part of the team that launched TechRepublic and is now senior features editor for Tech Pro Research.