Getting to Know Microsoft Access, Part 6: Advanced Queries and SQL
(Page 1 of 5 )
This week we’re going to take queries to another level. We discussed the commonly used select query in last week’s tutorial. This week we’ll discuss some other ways to retrieve data as well as other query operations that allow for updating, adding, and deleting data. We’ll start this lesson with a look at some special purpose queries: parameter and autolookup queries. After that we’ll discuss the action queries update, append, make-table, and delete.
Special Purpose Queries
The first type of special purpose query we’ll look at is the parameter query. The parameter query is similar to the select query except that, where the select query is static, the parameter query prompts the user for selection criteria before running. The second type of special purpose query we’ll look at is the AutoLookup query, which fills in values for you in related tables, saving data entry time.
Parameter Queries
Parameter queries are especially useful for finding activities that fall within a certain time period. You can use multiple parameters in a query and Access will prompt the user for each one.
To create a parameter query just begin with a select query by clicking the New Query button, selecting Design View in the dialog box, and selecting your table(s) or query(ies) from which to select data, like so:
Then, instead of entering criteria in the Criteria cell as you would in a standard select query, just enter the parameter text enclosed in brackets ( [ ] ). This text will make up the prompt the user is given when the query is run, as in the following screenshot that asks the user for the Category Name:
Besides asking the user for a basic text answer, you could also prompt them for a specific time period using the syntax Between…And in the Criteria cell in brackets. For example:
Between [Enter start date] And [Enter end date]
Select any other fields you want to appear in the query results by dragging the fields from the field list and putting them in the query grid in the appropriate places.
The default data type for a parameter query is text, but you can change that, for example, to a number data type by choosing the Query | Parameters menu option while in Query design view. In the dialog box that appears, enter the parameter precisely as it appears in the Criteria cell and select the appropriate data type, such as the number data type.
Next: AutoLookup Queries >>
More Microsoft Access Articles
More By Sage Adams
/ 18 
