Querying: creating, saving and editing in ms access

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 All Access Objects bar on the left side.
2. From the Create tab, click on the icon "Query Wizard."
3. When the New Wizard window appears, select “Simple Query Wizard” and click OK.  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, six 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 29 records.  You can use the control bar at the bottom of the window to navigate around the display.  You can also sort the list: from the Home tab, 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 job titles that contain “Owner”.  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 "Customers Query."  It will now be highlighted.  Then right-click on the query name and select Design view.  The datasheet view of the query will now be replaced by the design view, which allows you to make changes.  To limit the results to owners, simply type the word "owner" in the Job Title 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 example below shows the results.  The original set of 29 one records has been reduced to the six records in which the contents of the Job Title field exactly match the character string "owner."  Note that upper and lower case are insignificant but if the match is not otherwise exact the record will not be retrieved.

3. If you're happy with the results and want to save the revised query, either click the Office Button > Save, or just close the 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. There 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 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 retrieved job titles of either Owner or Purchasing Manager.
• 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 Microsoft’s 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 on the left and click Reports.
2. From the Create tab, click on the icon "Report 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 follow 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, click the Office button File > Print, or click the printer icon on the toolbar to print the report.

Reports 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 Microsoft’s 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 the External Data tab.  Click on the Excel icon in the export section.  
3. The Export Query window will appear and you will see a view of your local file system.  You will need to do several things in this window.  First, use your mouse to enter the descriptive name (it might make sense to name it after the query used to create it).  The first part of the name contains the drive and folder where you would like to save the output file.  Make sure you remember the name and location because you'll need to find it later.  Second, select the file format for your spreadsheet file.  Select Excel Workbook (*.xlsx).  Third, check the box to Export data with formatting and layout.   Fourth, check the box to Open the destination file after the export operation is complete by checking that box.  Fifth, check the box to save the export steps if this in an operation you want to perform again.  A default name is provided.  The saved exports may be viewed by going to the External Data tab and clicking on the Saved Exports icon in the Export section.