Cheat Sheet

Access 2007 Forms & Reports For Dummies Cheat Sheet

From Access 2007 Forms and Reports For Dummies

By Brian Underdahl, Darlene Underdahl

Using Access 2007 to create queries, forms, and reports can be simplified if you know commonly used queries in Access, form and report tools, and a few useful functions. With a powerful tool like Access 2007, you’ll be organizing your data in no time and on your way to creating better forms and reports.

8 Commonly Used Queries in Access 2007

Queries are important keys that can help you create the types of forms and reports that you want in Access 2007. Here’s a list of the eight commonly used queries:

  • Append: Adds records to an existing table.

  • Crosstab: Calculates and displays summary results using a spreadsheet-like layout.

  • Delete: Permanently removes a specified set of records.

  • Make-Table: Creates a new table and adds records to the new table.

  • Parameter: Prompts for the record selection criteria whenever the query is executed.

  • Select: Displays records that match a specified set of criteria.

  • SQL-specific: Queries that can only be created using SQL statements.

  • Update: Modifies existing records in a database.

Access 2007 Form and Report Tools

Creating your first Access form from scratch in Design View can seem a bit daunting. When you select this option, you’re faced with a blank grid, a Ribbon full of strange-looking icon tools above it, and no clue about what to do next.

Here’s a quick description of the various tools that you’ll encounter on the Ribbon:

  • Select Objects: Click this button to use the mouse pointer to select objects that you’ve added to a form.

  • Control Wizards: Click this button to enable wizards that help you add certain types of objects such as combo boxes and command buttons to your forms. Both combo boxes and command buttons are explained elsewhere in this list.

  • Label: Click this button to add a simple text label anywhere on your form.

  • Text Box: Click this button to add a text box to the form. Unlike a label, a text box can be set to change the information it displays when the form is used. Text boxes actually have two parts — a label and the box where a user can enter information.

  • Option Group: Click this button to create a set of related option buttons (such as Taxable and Tax Exempt). The buttons function as a group, and you can select only one option at a time. For example, you can choose Taxable or Tax Exempt for an order, but not both.

  • Toggle Button: Click this button to add a button that has two states — up for off and down for on. For example, up for Taxable and down for Non-Taxable.

  • Option Button: Click this button to add option buttons that work like a toggle button or a check box. Generally, you use option buttons in an option group to restrict a user from selecting more than one item in a group.

  • Check Box: Click this button to add an item that can be selected or deselected independent of any other options.

  • Combo Box: Click this button to add a box in which the user can select from a list of options or enter a new value. For example, you might have a list of books by your favorite author as items to suggest but also allow the user to enter the name of a book that isn’t on the list.

  • List Box: Click this button to create a list box from which a user can select a set of pre-existing values. A list box looks something like a combo box but doesn’t allow the user to enter new values.

  • Command Button (or just plain Button): Click this button to add a button that executes a macro or a VBA (Visual Basic for Applications) procedure.

  • Image: Click this button to add a digital image (such as a company logo) to the form.

  • Unbound Object Frame: Click this button to add objects such as text or images that don’t change when moving between records.

  • Bound Object Frame: Click this button to add objects that change when the information in the database changes — such as images that are stored in fields in the table.

  • Page Break: Click this button to create multipage forms when you have too many objects to display on a single page.

  • Tab Control: Click this button to create a form with two or more tabs that group related objects.

  • Subform/Subreport: Click this button to add a subform so that the user can see records from a related table — such as the individual line items on an order.

  • Line: Click this button to add lines to the form to differentiate between different groups of objects on the form.

  • Rectangle: Click this button to draw a rectangle on the form so that users can see how objects are related.

Useful Functions in Access 2007

Even though Access 2007 has several built-in functions, you may not use more than a few of them in your reports. Still, if you don’t know they’re available, you could miss many of the functions that you might find really useful.

Although this list is far from comprehensive, this table shows a number of functions that you may want to use when you want a little more than just a simple sum.

Function Description
Abs Absolute value of a number
Avg Arithmetic mean
Count Number of records
Int Integer value of a number
LCase Converts a string to lowercase
Left Returns a specified number of characters from the left side of
a string
Len Length of a string
LTrim Removes extra spaces from the beginning of a string
Max Maximum value
Min Minimum value
Now Current date and time
Replace Replaces a string with a different string
Right Returns a specified number of characters from the right side of
a string
Rnd Random number
Round Rounds a number
RTrim Removes extra spaces from the end of a string
Sum The sum of values
Time Current system time
Trim Removes extra spaces from both ends of a string
UCase Converts a string to uppercase