Modifying Existing Queries in Access 2007 Forms and Reports

As you work with queries in Access 2007, you may find that the queries you create could use a bit of fine-tuning. Perhaps you want to pare down the results so you don't have to wade through so many records, or maybe you've discovered that it really is a pain to slog through a whole bunch of unsorted data. Whatever the reason, being able to modify existing queries, rather than starting from scratch, can be a real time saver.

When you're experimenting with modifying queries, be sure to heed any warning messages that Access displays when you attempt to run the modified query. Remember that select queries and crosstab queries are always safe because they don't modify any of your data, but the other types of queries can modify data.

Deleting a query

Sometimes, the easiest, safest, and smartest thing to do with a query is to get rid of it entirely and start over. If something has gone wrong and you're not sure what it is, go ahead and delete the query. Just highlight it in the list of queries and press Delete.

Modifying an existing query

No matter which method you used to create a query, you really have only one option for modifying an existing query. That option is to open the query in Design View and make your changes in the Query editor. Access doesn't offer any wizards to help you modify existing queries.

To open an existing query in Design View, select the query in the database window, right-click it, and then choose Design View. When you open a query this way, the Query editor looks exactly the way it does when you're creating a query in Design View. That is, there aren't separate creating and editing modes to confuse you.

In some cases, you may find that fields you added to a query don't appear in the Query editor when you edit the query. The reason for the mysterious disappearing fields can't be attributed to aliens or ghosts; it's simply that Access drops any unused fields when you save a query. If you had fields in the query that didn't serve any purpose in the query, those fields are gone when you reopen the query.

After you open the query in Design View, you can click the Show Table button on the Ribbon. The Show Table dialog box appears so that you can add additional tables to the query. You can also add additional fields to the query by using the same methods you use when you're first creating a query in Design View. Likewise, you can add or modify any of the query criteria in exactly the same way you do when creating a query.

Rearranging and deleting fields in a query

The one query-editing task that you might find a little confusing at first is rearranging the fields in the query. You can change the order of the fields by

• Dragging and dropping new fields: To drag and drop a field, you need to drag the very small gray bar that's just above the Field row. When you drag and drop a field onto the column of an existing field, the existing field and any fields to its right shift to the right so that the new field is inserted before the existing field.

• Clicking the Field box of a column and selecting a different field for the column.

• Deleting existing fields: To delete a field, position the mouse pointer at the top of the field's column just above the Field row and then click when the pointer becomes a solid black down arrow. Choose the appropriate Delete button from the Ribbon or press the Delete key to remove the field. The remaining fields to the right shift to the left to fill the empty column.

dummies

Source:http://www.dummies.com/how-to/content/modifying-existing-queries-in-access-2007-forms-an.html