Microsoft Access is a complex and powerful database management system. A variety of AU offices and academic departments have deployed Access to accomplish a variety of tasks, some simple and some not so simple. Developers in the Information Technology Services department have created and continue to support many of the more complex Access applications, with all user-accessible functions listed on custom-designed menus. Making changes to this type of database requires the assistance of a database Programmer from Information Technology Services. The reason for this is that it is easy for users who do not have an expert's knowledge of the underlying data structure of the application can easily make a mistake that renders the database unusable.

There are, however, some relatively simple and reasonably safe processes that end users can perform themselves that can put useful data in their hands quickly without the need for requesting assistance from an ITS staff member. This guide describes several of those processes.

Warning: Performing the tasks described here are reasonably safe if the necessary precautions are taken, but all users must understand that any modification of a database application entails a certain risk. Make sure that you follow the guidelines below and do not make any changes to your database other than the ones described here. Specifically, do not delete or modify any existing objects in the system other than the ones you have created. Also make sure before you begin work that you have a good backup of your database file, so that if something goes wrong you can quickly restore the original version.

On Structured Databases

Creating new access tools in Access is easier if you understand something about the way Access databases put together. An Access application is basically a hierarchy of what Microsoft calls "objects." These objects are linked in various ways to accomplish various tasks. The four main classes of objects in Access are the following:

• Tables. These structures define and store the data in your database. They are not designed to be used directly by casual users, so you should never have a need to open or modify your tables (if you do, major damage can result). The main purpose of the table is to store the data that is used by other objects.

• Queries. A query is a device that basically asks question of the database. Its purpose is to retrieve a partcular set of data from one or more tables that matches the specifications of the person asking the question.

• Forms. A form is a user-defined template that arranges data from various tables in a window in Access for editing or review. It can pull the data either directly from a table or from a saved query.

• Reports. A report is very much like a form, except that it arranges your data for printing instead of on-screen display.

When you open your Access database you may see either of two possible windows. Some users will see a custom-designed main menu that has been created by an ITS programmer. This menu allows the user to execute a set of database functions without having to deal directly with, or even know about, the underlying objects. The other opening window possibility is the standard Database window that is common to all Access applications. In order to create your own queries you will need access to your Database window (it may have been hidden in your application -- if so, please contact ITS for assistance). Here, as an example, is the Database window of the Northwinds database appliction, a demonstration database that is distributed with Microsoft Access:

The left part of this window contains a list of various database objects. The right panel is a list of the currently defined object of the selected type. This example shows all the query objects that have been defined and saved for use in this application. In the sections below you will learn how to create new queries and save them to the list.

Creating and Using Queries

To create a query you need to know something about the structure of your database. The instructions below assume that you have already identified one or more tables that contain the information you would like to retrieve.

1. In the Database window, click the Queries entry under Objects.

2. Double click the icon on the right side of the window labeled "Create query using wizard."

3. When the Simple Query Wizard window appears, scroll through the drop-down window labeled Tables/Queries and select the object that contains the data you want to retrieve. Normally it will be a table but it can be another query. When you have selected the data source object, the various elements that are part of that object will appear in the box labeled Available Fields.

4. Take a look at the available fields and decide which ones you want to include in your query. Only the fields you select will be used in the query -- the others will be ignored. With your cursor highlight your first selection and click the ">" button to move it over to the Selected Fields box. Clicking ">>" moves all the fields over at once, and "<" and "<<" are used to un-select previously selected fields. In the example below, four fields have been selected from the Customers table. Click Next when you're ready to move on.

   

5. The next dialog box asks two questions. Under "What title do you want for your query?" you'll notice that the system has suggested a possible label, but feel free to enter a different one that makes sense to you. If you don't intend to save this query the label is irrelevant, but a useful attribute of Access queries is that you can save them and run them again later. Here's a suggestion: add your initials at the beginning of the name for every query you create -- this will help distinguish your personal queries from others already in use on the system (which you must not alter or delete). The second question on this screen asks whether you want to open or modify the query. For now use the default option (open) and click Finish.

6. After a moment a new window will appear containing a spreadsheet-style display (Microsoft calls this the datasheet view) of your retrieved data. In this example, the system has retrieved 91 records. You can use the control bar at the bottom of the window to navigate around the display. You can also sort the list: place the cursor anywhere in the column you want to use for sorting and click the Sort Ascending icon on the toolbar.

   

7. To close the query, just click the "X" box in the upper right-hand corner of the window.

Important note: in most cases you can modify data or even delete records from the datasheet display of a query. Please keep this in mind. It's a real convenience to be able to use queries as a method for quickly modifying a set of records, but there is also the risk of doing something that you don't intend. Be very careful when handling queries to make sure you don't inadvertently make any changes to the data.

Modifying Saved Queries

The real power of queries is using them with parameters that select certain records and reject others. Let's say, for example, that in the query presented above you want to display only the company names located in France. You've already saved a query that retrieves every record in the original table. What you need to do next is revise the query to pare down the results.

1. From the main Database window, click Queries, and then click the entry for new query created above, called "Customer name." It will now be highlighted. Then either click the Design icon in the toolbar or right-click on the query name and select Design view. The datasheet view of the query will now be replace by the design view, which allows you to make changes. To limit the results to France, simply type the word "France" in the Country column and the Criteria row, as shown below. Quotes are required but if you omit them, Access will provide them for you.

   

2. Next, test your revision by clicking the datasheet view icon on the tool bar, or select the menu option View > Datasheet view. The example below shows the results. The original set of 91 one records has been reduced to the 11 records in which the contents of the Country field exactly match the character string "France." Note that if the match is not exact the record will not be retrieved.

   

3. If you're happy with the results and want to save the revised query, either click File > Save, or just close they query and answer "yes" when prompted whether you want to save your changes.
   If you don't like the results you can switch back to design mode and make further changes. You can toggle back and forth between the design and datasheet views as much as you like until you get the results you want.

The example above demonstrates a simple exact-match query on a single field. re are a number of other types of changes you can make to a query when you're in design mode. It is outside of the scope of this document to describe these tools in detail, but if you're willing to learn the specialized syntax required you can create queries that do the following:

• Add a criteria for a second column. Only records matching both parameters will be retrieved.

• Expand the scope of the criteria for a particular field. For example, in the query displayed above you could have the to retrieve company names located in either France or Spain.

• If your query contains numerical values, display only records with values greater or lesser than a particular value.

• If your query contains dates, display only records with a date value that falls before, after, or within a particular range of dates.

• Search for records that contain a particular string of characters in a particular field (as opposed to an exact-match search as illustrated above).

For further information on how to construct queries using various specialized expressions, consult online help or contact Information Technology Services for assistance.

Printing and Exporting Query Results

When you run a query the system shows you the datasheet view of the results. Sometimes that's all you need -- you scan the table for the information you're looking for and you're done. But other times you'd like to do something more with those results, such as print them or move them into another environment. The possibilities are many, but the two described below are particularly useful.

Convert queries into reports. If your goal is to print your query results, you can use the report creator built into Access. Using the wizard, the technique is very similar to the creation of a query. Here are step-by-step instructions.

1. With your database open in Access, go to the main Database window and click Reports in the Objects column.

2. On the right side of the window displaying the current report objects, double click "Create report using wizard."

3. When the Report Wizard window appears, scroll down in the Tables/Queries box and select the query you want to use to produce the data for your report.

4. Just as you did when creating the query, select the fields you want to print and move them over to the Selected Fields window (you can move them all over if you wish). Click Next.

5. Several dialog screens will following asking you to make various decisions about the appearance of your report. In most cases you can use the default settings -- just keep clicking Next. When you come to the window asking you for sort order you should select the field that will determine how the records appear alphabetically in the report.

6. In the last panel you will see the question "What title do you want for your report?" Just as with the query wizard, you can at this point choose a descriptive name for purposes of saving the report as an object. Do this if your report is one that you'll want to run again later. Then click Finish.

7. Your report will appear in a preview window. If you like what you see, select File > Print, or click the printer icon on the toolbar to print the report.

Report created this way can be customized in numerous ways. This however requires going into the report Design view, the use of which is beyond the scope of this document. Consult Information Technology Services or browse through online help for assistance.

Saving a query as a spreadsheet. You can also export a query as an Excel-compatible spreadsheet file. This involves creating a specially constructed file based on the results of a query. Here are the steps involved:

1. From your Access database, run the query of your choice and make sure the datasheet view is the active window.

2. Click File > Export. (If you don't see an export option in the file menu it may be because the datasheet view of the query is not selected as the active window.)

3. The Export Query window will appear and you will see a view of your local file system. You will need to do three things in this window. First, use your mouse to select the drive and folder where you would like to save the output file. Second, enter a descriptive name for your spreadsheet file (it might make sense to name it after the query used to created it). Make sure you remember the name and location because you'll need to find it later. Third, in the Save as type box, scroll down and select "Microsoft Excel 97-2003 (*.xls)". Then click Export All.

You can now start Excel and open your exported data as a spreadsheet for further processing, printing, mail-merging, etc.