MS-Access / Getting Started

---

Creating a Query with the Simple Query Wizard

The Simple Query Wizard does a great deal of the work of creating a query for you. It's most useful when you want to use fields from different tables and when you want a query that summarizes your data.

The Simple Query Wizard gives you the option of creating either a summary (totals) query or a detail query. A detail query lists every record that meets your criteria. A summary query (also called a totals query) performs calculations on your data to summarize it. You can create a summary query if the fields you choose for the query include both of the following:

• A field with values
• A field with repetitions or a field with dates, used to group the values

A summary query gives you the option of totaling (summing), averaging, counting the number of values in a field, or finding the minimum or maximum value in a field. A summary query creates new calculated fields that you can use in other queries or in reports.

If you have a field that lists the amount spent and a field that lists the dates on which the money was spent, the Simple Query Wizard creates a summary query for you that sums the amount spent by date.

Ready to give the Simple Query Wizard a spin? Just follow these steps to use the wizard to create a query:

1. Display the Create tab on the Ribbon and click the Query Wizard button.
2. Select Simple Query Wizard from the New Query dialog box and click OK.
   Access displays the first window of the Simple Query Wizard.
3. Use the Tables/Queries list box to choose the first table or query that you want to use fields from.
   Many queries are based on tables, but you also have the option of basing a query on another query. For instance, maybe you already created a query to select sales data from only the year 2003. Now, without modifying the original query, you want to create a query that lists 2003 sales by state, or limits the analysis to just a few salespeople.
   When you select a table or query, fields from that object appear in the Available Fields list box.
4. Move the fields you want to use in the query from the Available Fields list to the Selected Fields list by double-clicking a field name (or by selecting the field name and then clicking the > button).
5. If you're using fields from more than one table or query, repeat Steps 2 and 3 to add fields from the additional tables or queries to the Selected Fields list and then click Next.
   From this point on, the windows you see depend upon the types of fields and the type of query (detail or summary) you choose.
6. Choose the type of query you want: Detail or Summary. Depending on your selection, do one of the following:
   • If you choose a summary query, click the Summary Options button.
   • f you choose a detail query, click Next and jump to Step 9.
   The Summary Options window displays, where you tell the wizard how to summarize each field.
7. Choose how to summarize your data and click OK to close the Summary Options dialog box. Then click Next to see the next window of the wizard.
   Use the check boxes to indicate the new fields you want Access to create. For example, if you want to add all the values in the Qty field (to calculate how many of each item have been sold), click the Sum check box in the row for the Qty field.
   Don't overlook the Count check box(es) that may appear in this window - selecting a Count check box tells the wizard to create a field that counts the records within each grouping.
8. If the fields being summarized can be grouped by a Time/Date field, choose the time interval the records should be grouped by and click Next.
   You will not see this window if your data does not contain a Time/Date field.
   For example, if you choose to include the Order Date field in the query and to sum the Qty field, you can group by month to see how many of each item you sold in each month. You can choose to display total check amounts by the following options: Day, Month, Quarter, or Year. The Unique Day/Time option groups records by each unique date and time; if your data includes times, each record with the same date and time is grouped together. If your data only includes a date without the time, each record from the same day is grouped together (which is the same as the Day option).
9. Type a name for the query in the box at the top of the window.
   Choose from these options:
   • Open the Query to View Information: This option shows you the query in Datasheet view.
   • Modify the Query Design: This option shows you the query in Design view.
   • Display Help on Working With the Query: Click this check box if you want to see the help screen that covers working with a query.
10. Click Finish to view the query.
    If you chose the Open the Query to View Information option, you see the query in Datasheet view. If you chose the Modify the Query Design option, you see your resulting query datasheet.

You can edit the query created by the Simple Query Wizard using Design view, (about which there's lots more in the rest of this tutorial).

The Simple Query Wizard doesn't allow you to include criteria to choose which records you want to include in the query datasheet. If you want to include criteria in your query, open the query created by the wizard in Design view and add the criteria. (Details of Design view appear throughout this tutorial.)