Creating and Using Queries


The word query is one of those fancy-sounding words for something really simple. A query is a question. When you query an Access database, you ask it a question, such as:

  • How many salespeople in Oregon sold more than 1,200 books last March?

  • Which of our regional offices works the longest hours?

  • How many students took advantage of our online courses last semester? And which classes were most popular?

You might think answering these kinds of questions would take hours of fact-digging. In fact, they can be answered in less than a minute in Access, if you ve set up your fields correctly, and know how to use queries. This section introduces you to this time-saving feature.

Composing a Query

Your first task in creating a query that delivers the results you want is to tell Access what you need to find. Click Queries in the Database window to start the process. Then follow these steps:

  1. Double-click Create Query By Using Wizard. The Simple Query Wizard appears (see Figure 17-4).

    click to expand
    Figure 17-4: The Simple Query Wizard walks you through the process of creating your first query.

  2. In the Tables/Queries box, choose the name of the table you want to use.

  3. Click the field (or fields) from the Available Fields list that you want to add to the Selected Fields list (these are the fields you will use in the query). Click the Add button to add each field. Click Next.

  4. Add a title for your query. Leave the other settings as they are. Click Finish. Click the Close box of the query window that appears.

Applying a Query

Now that you ve created the query, you need to apply it to your database. Because you assigned the query a name, you can use that query on this or other tables at any time by selecting it from the Queries list in the database window. To apply a query to a table, follow these steps:

  1. Display the database window for the database you want to query.

  2. Click Queries in the left panel.

  3. Double-click the query name from the list on the right. The information appears in the Query results window, showing you the latest query results based on the data in the data table as it currently stands.

start sidebar
Aha! ”Use Queries for Repeat Procedures

Queries are very helpful when you need to know similar things periodically, such as who took the greatest number of vacation days last month, or which employees are due for rate increases . Once you create a query and name it, that query is available in the database window, and you can select it to apply it on your database monthly, weekly, and so on, to get the most up-to-date picture your data can present.

end sidebar
 

Modifying a Query

If you want to change a query you ve created, you can simply edit the selections you ve made. You do this using Design view. Here s how:

  1. Display the database window for the database you want to use.

  2. Click Queries in the left panel.

  3. Double-click the query you want to use. The query opens in the query results window as usual.

  4. Click Design View on the far left side of the Standard toolbar. The query appears in Design View (as shown in Figure 17-5 on the following page).

    click to expand
    Figure 17-5: To modify a query, display it in Design View and change your selections.

  5. To choose a different field, click in the Field entry and a down arrow appears. Click the arrow, and select the field you want from the displayed list.

  6. If you want to choose a different table, click in the Table entry. Again, choose the table you want from the list that appears when you click the down arrow.

  7. Make any other modifications as needed, then click Run on the toolbar. The modifications are applied to your data, and the query is saved in the new form.

start sidebar
Aha! ”Delete Unnecessary Queries

If you want to do away with a query, simply click Queries in the database window, select the query you want to delete, and press Delete. Access will ask you to confirm that you want to delete the query. Click Yes to complete the operation.

end sidebar
 
start sidebar
More Fun with Queries

Queries are really wonderful, and can be quite complex, depending on what you want to do with them. Access includes five types of queries:

  • A select query allows you to select the data you re looking for.

  • A crosstab (short for cross-tabulation) query enables you to perform calculations based on the data values in query results.

  • A make-table query creates a table with the results of the query.

  • An update query goes through your database and updates records with new information.

  • An append query allows you to add information to the selected database.

For more information about working with these more specialized forms of queries, see Microsoft Office Access 2003 Inside Out by John Viescas, also from Microsoft Press.

end sidebar
 



Faster Smarter Microsoft Office System
Faster Smarter Microsoft Office System -- 2003 Edition
ISBN: 0735619212
EAN: 2147483647
Year: 2003
Pages: 238

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net