With the Query wizard, you can select a group of records based on some rules you specify, For example, if you have tables that contain large amounts of information in one or more tables, you can use a carefully designed query to collect or display just a few records that satisfy some numeric or logical value.
From Tools menu, choose Wizards, and then click Query.
In the Wizard Selection dialog box, select Query Wizard.
Step 1 вЂ“ Select Fields
In this step, you can choose free tables or tables within a database as the source for your query. You can select fields from one or more tables or views.
Use the Databases and Tables controls to locate and select the tables or views you want to use.
In the Available fields window, select one or more fields you want to use from the selected table, and use the arrow buttons to move them to the Selected fields window.
Repeat this process to add fields from other tables or views.
Step 2 вЂ“ Relate Tables
This step displays only if you select fields from more than one table or view. You can specify which fields in each table or view contain the same information and can therefore govern the relationship between the tables or views, thereby determining the included records.
Select the desired fields from the two drop-down list boxes, and then choose Add. If you use multiple tables in your view, then you must relate the tables by indicating which fields contain matching data in each table.
Step 2a вЂ“ Include Records
This step displays only if you select fields from more than one table or view. You can specify which records from the selected tables will be made available to the Query wizard.
If you are using more than one table, you can specify a join condition. For more information, see How to: Create a Multitable View. For example, if you specify only matching rows in Step 2a, you can further refine that choice in Step 3 by specifying a particular value for a field.
- Only matching rows
- Returns only records from both tables that match the comparison condition set between the two fields in the join condition. This is called an inner join.
- All rows from this table
- Returns all the rows from one of the listed tables or views. This makes it possible for you to create a left or right outer join.
- All rows from both tables
- Returns matching and non-matching records from both tables. This is called an outer join.
By default, only matching records are included.
Step 3 вЂ“ Filter Records
This step appears immediately after Step 1 if you have chosen only one table or view as source for your query. You can specify a filter condition, so only certain of the records you have chosen to make available are used in the query.
Select a field from the Field drop-down list.
Specify an operator, such as equals or contains, in the Operator drop-down box.
Specify a value in the Value textbox.
For example, if you wanted to filter on a specific city, such as Helena, you might choose city from the Field drop-down list, choose equals in the Operator drop-down list, and enter Helena in the Value textbox.
You can reduce the number of records by creating expressions that filter records from the selected tables or views. You can create two expressions and connect them with And, which returns only records meeting both specified criteria, or Or, which returns records meeting either criteria.
You can see the result by clicking the Preview button.
Step 4 - Sort Records
In this step, you can specify the sort order of your query records. For example, if your query will be used to assess orders by region, then you might sort by state or zip code.
Choose up to three fields or an index tag that already exists in the database to determine the order in which your view results will be sorted. Select Ascending to sort the view in ascending order or Descending to sort the view in descending order.
Step 4a вЂ“ Limit Records
This step appears only if you have specified one or more sort fields in Step 4. You can further limit the number of records in the view, based on either a percent of the records returned or an actual number of records.
To see a percentage of the available records, choose the Percent of records radio button, and then specify the percentage in the Portion value edit box. See all records by selecting Number of records and then selecting All records. As shown in the following procedures, you can choose a portion of records from the beginning or the end of the available records.
Choose the Number of records radio button
Enter 10 in the Portion value box.
In Step 4, change the sort order to Descending.
|This changes the sort order to last record = first read.|
In Step 4a, select the Number of records radio button.
Enter 10 in the Portion value box.
Step 5 - Finish
In this step, you can choose how to save your query.
- Save query
- Makes it possible for you to save the query for later use.
- Save query and run it
- Makes it possible for you to save the query and run it immediately
- Save query and modify it in Query Designer
- Makes it possible for you to save the query and then use the Query designer to enhance or modify it.