Takeaway: If you create Access queries, you probably know your way around the Query Design window. But there are some not-so-obvious tricks you can use to work more efficiently, maintain accuracy, and ensure database integrity.

This article is also available as a PDF download.

To create an Access query, you use the Query Design window. You specify a few data sources and fields, add a criteria expression or two, and perhaps specify a sort order. What you might not realize is that there's more to working in this window than just creating a query. Working efficiently in this environment can help you maintain accuracy and database integrity.

#1: Mastering the terminology

Queries come with a few terms you might not associate with their graphic counterparts. With that in mind, here are a few environmental terms you might read in articles and documentation:

• Design view — This is the window you use to design a query. You might also see this window called the Query Design window or the Query Builder. The Query Builder is actually a different interface from the Query Design window. Access displays this window when you define a query as a control's data source.
• Datasheet view — This is the table-looking view of a query's resulting recordset. There are other views, but you'll usually work with Datasheet view.
• QBE grid — This is the lower pane in the Query Design window's graphic interface. (QBE stands for query by example.) Use the bottom pane to specify the fields upon which the query acts.

#2: Adding fields to the QBE grid

The upper pane of the Query Design window (Design view) displays a query's data sources. If you want the query to act upon a field, you must drag that field to the QBE grid (the lower pane). Fortunately, Access supports a number of ways to do this:

• The most obvious way is to type the field's name into a Field cell. As you type, Access selects the most appropriate field name from the data source. If there's more than one table and the same field name occurs in both, you must also specify the source table in the Table cell. If you don't, Access defaults to the first table (left to right in the upper pane) that contains the field.
• Perhaps the most efficient and trouble-free method is to select the field from the Field cell's drop-down list. Doing so eliminates errors. If there are multiple tables, the list displays all the fields from all the tables, using the table.field format, which can make this method a little more difficult to use.
• You can use the drag-and-drop method to drag a field from a field list in the upper pane to a column in the QBE grid. In the field list, click the field you want to add to the query. Then, while holding down the mouse, drag the field to the QBE grid.
• A quick double-click will also do the trick. Simply double-click a field to add it to the QBE grid.

If you enter a field name that doesn't exist or you create an ambiguous reference, Access won't run the query, so you don't have to worry about this type of mistake fouling up a query's results. However, you will waste time finding and correcting the error. For this reason, it's best to avoid typing field and table names.

#3: Quickly adding all the fields to the QBE grid

Some queries require all the fields from a data source. When this is the case, you could use the asterisk character (*) in the QBE grid to represent all of the fields. You'll find it at the top of each field list. However, you should avoid using the asterisk character because it has limits. For instance, you can't include an aggregate function or run a Totals query against the asterisk character. The limits make perfect sense, but the asterisk character has a way of turning up an error when you least expect it.

Fortunately, there's another easy way to add all fields to the QBE grid. Double-click the field list's title bar to select all of the fields. Then, simply drag the entire block of fields to the grid. Access will fill the grid in the order the fields occur in the list.

#4: Dragging contiguous and noncontiguous blocks

You can drag a contiguous or noncontiguous block of fields to the grid. To select a contiguous block of fields, click the first field name in the block. Then, hold down the [Shift] key and click the last. Access will select the two clicked fields and all the fields in between. Once Access selects the block, drag it to the QBE grid. Selecting a noncontiguous block is similar. Hold down the [Ctrl] key and click each field name. When you're finished, drag the block to the QBE grid. When dragging multiple fields to the QBE grid, Access always inserts the fields in list order.

#5: Having it all

Although you can easily add all the fields in a data source to the QBE grid, and ultimately, to your query, you probably shouldn't. Add only the fields that your query needs: fields that are part of a criteria expression, fields you need to sort or group by, and fields you need to view.

Keep in mind that bound objects have access to only the data specified in the query. If bound objects need more data, set the query's Output All Fields property to Yes. Then, any bound object will have access to all of the underlying data source's fields. To access this property, click the Properties button in Design view.

#6: Setting query properties

In Design view, you can click the Properties button to display a list of query properties. Alternately, you can right-click the query window's background and choose Properties from the resulting submenu to display the same list. Although the latter method requires an extra click, it's good to know you can access properties this way in case the Query Design toolbar isn't available.

#7: Reading the Jet SQL behind your queries

Access communicates with Jet using Jet SQL. The Query Design window is just a graphical interface that allows you to create the appropriate SQL statement, without actually writing the statement yourself. From the choices you make in the QBE grid, Access creates the SQL statement the Jet engine requires to run the query. If you'd like to see the SQL behind your queries, choose SQL View from the View button on the Query Design toolbar. The quickest way to learn Jet SQL it is to read it.

#8: Combining nested queries

You probably know that you can base one query on another. This arrangement is known as nested queries. Often, you can replace nested queries with a subquery — a SQL statement that contains a second SQL query. However, writing a subquery from scratch can be difficult, even if you're familiar with Jet SQL.

Fortunately, you can use the SQL View window (#7) to help create a subquery. To get started, view the first query in SQL View and copy it to the Clipboard. Next, open the second query and add the WHERE keyword (usually at the end of the statement). Then, paste the first query beneath the new WHERE keyword and enclose the entire copied SQL statement in parentheses. Replace the query is generic viagra real reference with the appropriate table name(s). Now, this method isn't foolproof for every subquery, but it's a good start.

#9: Viewing results without running the query

Running a SELECT query retrieves and displays data, but it doesn't change underlying data. For this reason, clicking Run to see the results is a safe choice. But that won't always be the case. If you run an action query — an Update, Delete, or even an Append query — at the wrong time, you could seriously compromise your application.

Fortunately, you can test an action query's results without actually committing its changes. Simply choose Datasheet View from the View menu on the Query Design toolbar. Doing so lets you see what the query will do if you actually run it.

If, for some reason, Datasheet View isn't available, run the action query as a Select to view the results. Once you're sure the query does the job you want, change the query type and run it as an action query.

#10: Building relationships

To create a relationship between two tables, you probably use the Relationships window (click Relationships on the Database toolbar). The resulting window displays existing relationships and allows you to create new ones. You can also create and manipulate relationships in the Query Design window. Simply drag related fields between the field lists in the upper pane. Then, right-click the resulting join line to modify the relationship, if necessary. However, you must remember that any relationship you create this way is valid for that query only. This relationship will not affect any other queries.

#11: Rearranging columns

Order matters in some queries. For instance, if you specify a sort order for more than one field, Access sorts from left to right. Sometimes you must rearrange columns in the QBE grid to get the sorted or grouped results you want. To do so, click the thin bar at the top of the column you want to move to select the entire column. Then, click the selected column, hold down the mouse, and drag the column to its new position. When you close the query, Access will prompt you to save changes you made to the layout. Click Yes only if you want to retain the new column position.

#12: Using SQL View to build and debug

The SQL window (#7) doesn't just display SQL statements. Take full advantage of this view to build and debug SQL statements you execute via code. First, create a SQL statement you intend to use in code in the Query Design window. Then, copy the statement from the SQL View window to a code module. You can also use SQL View to debug SQL statements you're already using in code. Copy the erring statement from the module to the SQL View window and run it. Generally, this window displays a much more helpful error message than the coding environment. Run the statement via the SQL View window as many times as necessary to correct the problem. Then, copy it back to the code module.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@setel.com.

Date: March 8th, 2007

Author: Susan Harkins

Access stores one of your most important assets–your data–in tables. Now, truthfully, the table you see onscreen is just a graphic representation, but you use this representation to determine how the rest of your database uses and reacts to your data. For this reason, it’s important to spend time up front assigning the appropriate properties and data types to suit the data. If the tables aren’t right, everything else is suspect.

This entry originally appeared as an article and as a PDF download. We’re presenting it here as well so that we can build a “10 things” archive.

# 1: Use table level properties to increase efficiency

Access tables offer several properties that remain with the data. You set the property just once, at the table level, and bound objects inherit those settings:

You’re not stuck with these property settings. At the form or report level, you can override any of these properties for just that form or report. In all cases, you save time and your data exhibits continuity from object to object.

# 2: Name fields appropriately

A field name should reflect a field’s purpose and describe the data it stores. The field names FirstName, LastName, and ZipCode are self-documenting and easy to manage. There’s no guessing. You know exactly what type of data should be in that field. There are, however, a few rules you must follow when choosing field names:

• A field name can consist of up to 64 characters–but only letters, numbers, and spaces.
• Don’t use reserved words to name fields, or any object, for that matter. For a list of reserved words, search Help for “reserved words.”

If you aren’t bound by an in-house naming convention, consider creating one of your own.

# 3: Don’t use spaces in field names

Although you might be tempted to use spaces in your field names, don’t. Spaces can be difficult to work with, especially in SQL statements and VBA code. If you use them, you must remember to enclose the reference in brackets, which is a nuisance. In addition, if you upgrade the database to SQL Server or export the data to use in another application, those space characters most assuredly will cause problems.

If you want field how long does viagra stay in your system headings and subsequent objects (see # 1) to display more natural text, use the field’s Caption property. For instance, if you name a field LastName, use the Caption property to display Last Name. Just remember that the Caption property is for display only. When referencing the field, you must always use the field’s actual name.

Caption properties come with their own set of behavioral problems. If you’re going to use the Caption property, plan for the following pitfalls:

• Access ignores the AS keyword (alias) in a SQL statement if the underlying field has a Caption setting.
• A Caption property setting won’t make it to the results of a Make Table query.
• DAO and ADO field objects return a field’s Caption property and not the underlying field’s name.

# 4: Don’t waste data type effort

When storing numeric data, you might be tempted to assign a Number data type. Before you do, consider the data’s purpose. For instance, you won’t use street number or zip code values in mathematical equations. When this is the case, store the data as Text. You’ll save a bit on memory, but more importantly, the data type is true to the data’s purpose. If you should need to use a text value as a true numeric value, use the Val() function in the form

=Val(field)

where field represents the Text field that’s storing numeric values or a literal value.

# 5: Use the most appropriate field size

With today’s powerful systems, assigning the most appropriate field size isn’t as urgent as it once was. However, as a matter of good practice, developers still limit field size. It’s your first step to validating data. For instance, let’s say you store state abbreviations in a Text field with Field Size setting of 2. If the user enters ARK instead of AK, Access will reject the entry. Of course, the field size property rejects only entries that are too big. It can’t spot typos or other mistakes. The field would still accept other invalid entries, such as “A” or “K6.”

This works with numeric fields too; it just isn’t as obvious. For instance, a Byte field accepts values 0 through 255, while the Long Integer accepts values from -2,147,483,648 to 2,147,483,647.

Choose the appropriate data type and field size property for each field. Always choose the smallest data type and field size that will accommodate the largest possible value in that field.

# 6: Choose indexes carefully

Access uses an index to sort data in logical order, according to data type. The right indexing can improve performance. The wrong index will slow things down. By far, the most common mistake is to set too many indexes. Because Access updates the index each time you add or change a record, too many indexes will affect performance. Fortunately, you can apply a few indexing guidelines that will help:

• Remember that a primary key automatically sorts–that’s an internal action you can’t control. In most tables, it’s the only index you really need. It’s called a unique index because every value in the field must be unique.
• As a general rule, consider indexing a table’s foreign key.
• Avoid an additional index on a table that you’ll be frequently updating, unless one of the above conditions applies.
• If the table has a primary key, consider an index on a second field only when you’re working with large amounts of data, you plan to search or sort by that field often, or the field contains mostly unique values.

In short, index any field you want to sort, search, or join to another table to speed up tasks. Just remember that each index increases the size of the database, and too many indexes will slow things down. If you’re working with small to reasonable amounts of data, indexes–beyond the primary key–usually aren’t necessary.

One final word on indexes: Don’t use an index to sort. That isn’t their purpose and you might not end up with the results you need.

# 7: Beware of AutoNumber limitations

Use an AutoNumber field to generate a unique number for your records. Many people use these fields as the table’s primary key, which is fine. This type of key is called a surrogate key. In later versions of Access, you can generate an incremental or random value. Incremental values are fine for most tables. You probably won’t use random values unless you’re using replication to synchronize multiple databases.

Remember that a table can have only one AutoNumber field. That might limit the way you can use that table later. For instance, if you need to merge two tables with AutoNumber fields in a Make Table query, you might run into trouble. It’s important to keep this limitation in mind when adding an AutoNumber to a table.

# 8: Change the starting value of an AutoNumber field

By default, an AutoNumber field starts with the value 1 and increments by 1 for each record. Occasionally, you may need to start a new table with an AutoNumber value other than 1. There’s no built-in property that lets you do this, but you can force a value:

1. Create the table and add an AutoNumber field but don’t enter any records.
2. Create a second table with one Long Integer Number field. Give the field the same name as the AutoNumber field in the data table (step 1).
3. In the single field table (step 2), enter the value that is 1 less than the value you want the AutoNumber field to begin with. For instance, if the first AutoNumber value must be 100, enter 99.
4. Run an append query to append the record in the single-field table to the data table.
5. Delete the single-field table.

When you enter the first record into your data table, the AutoNumber field will generate a value that is 1 more than the value you entered into the single-field table (step 3).

# 9: Use analysis tools

Access has two tools that will help you refine your design. First, the Table Analyzer Wizard reviews a table and recommends changes where appropriate. Second, the Performance Analyzer reviews your entire database and makes suggestions for improving the design. Usually, you’ll want to apply the wizards’ advice.

To run either wizard, choose Analyzer from the Tools menu and choose the appropriate item.

# 10: Don’t forget table properties

Like fields, tables have properties that define the table’s purpose. Most are self-explanatory, and the defaults are usually adequate. To access these properties, open the table in Design view and then choose Properties from the View menu. Here are a couple you should know about:

• Order By: Specifies a sorting order that Access applies when you open the table. Simply enter the name of the field by which you want to sort the records. If you don’t use this setting, Access sorts by the primary key. If there’s no primary key, Access displays records in data entry order. It won’t matter much at the table level, since users don’t view tables. However, like many field properties, bound objects inherit the property.
• Subdatasheet Name: Determines whether subdatasheets display related records. Many people find subdatasheets annoying. If that’s you, set this property to [None] to disable it.

# 11: Be aware of the limits

Although you may never reach any of the limits set for tables, you should know they exist:

• Table names can contain up to 64 characters.
• Field names can contain up to 64 characters.
• Each table can contain up to 255 fields.
• Table size is limited to 2 gigabytes minus the space needed for the system objects.
• A Text field stores up to 255 characters.
• A Memo field stores up to 65,535 characters when you enter data via the user interface. It’ll accept up to 1 gigabyte if you enter the data programmatically.
• An OLE field supports up to 1 gigabyte.
• Each table can have up to 32 indexes.
• Each index can comprise up to 10 fields.
• A Validation Rule expression stores up to 2,045 characters; the Validation Text property can have up to 255 characters.
• The Description property stores up to 255 characters.
• Each record can store up to 4,000 (for 2003) characters. (Version 2002 is 2,000.)

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader’s Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner’s Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@setel.com.

Takeaway: Microsoft Access forms made for quick data entry of large volumes of records should consist of a simple top-to-bottom layout. Get step-by-step instructions on how to create a simple data entry form.

Not all Microsoft Access forms are made for data entry. Some forms are made for data search and analysis, which can contain features such as option groups, check boxes, and combo boxes.

Forms made for quick data entry of large volumes of records should be built without such controls. Rather, they should consist of a simple top-to-bottom layout, with all of the controls lined up in a single column.

To create a simple data entry form, follow these steps:

1. In the Database Window, select the table for data entry.
2. Click the New Object tool in the Database toolbar.
3. Select Form, select Design View, and click OK.
4. Double-click the Field list button in the Database toolbar.
5. Press [Ctrl] and select the fields to be added to the form.
6. Click and drag the selected fields to the form.

Be sure to set the form's Cycle property to All Records so that tabbing out of the last field takes the user immediately to a new record.

Date:October 2nd, 2007

Author: Mary Ann Richardson

Check boxes are fine for data entry, but you may not want to use them on a formal report. For example, suppose you have a Yes/No field in your Employee Records table called Insurance. If the employee signed up for your company’s life insurance plan, the box is checked; if the employee declined the insurance, the box is not checked. You want to create a report that lists employee name, ID, hire date, and whether the employee declined or accepted life insurance. Follow these steps:

;"Accepted";"Declined"

1. Click the Lookup tab.
2. Click in the Display Control property box and select Text Box.
3. Close and save the query.

cheap viagra without prescription align=”justify”>When you create a report based on this query, either the word Accepted or Declined will replace the check box in the Insurance field.

Date: March 11th, 2008

Author: Mary Ann Richardson

When every other page of your 10-page Access report prints out blank, your first thought may be to change the report orientation from Portrait to Landscape. Rather than spend time redesigning your report layout, open the report in Design view and check the position of the report controls next to the right page margin. If any of the controls touch the right-margin border or even override it, Access automatically increases the right page margin to accommodate the controls. If this increases the margin past the eight-inch mark, Access cannot print the entire page on one piece of paper. (The blank page is actually a printout of the rest of the preceding page.) To eliminate the blank pages, follow these steps: