wsh - Creating a parametric query
(Page 2 of 4 )
What the parametric query accomplishes
Let's begin by creating a parametric query. Let's also use the table shown above. What we want to accomplish is this: when the query is run, the program asks for the CustomerID we want to look up. When we supply the CustomerID as an input, the query returns all of the columns (or some chosen columns) for that particular row. For example, if you supply the CustomerID as 'ALFKI', you should retrieve all the information for this particular customer, such as Customer Name, Contact Name, Contact Title, etc.
Designing the Select queryThere are many ways of designing the queries, as you can learn from reading other articles on this site. Click on the tab New when you have highlighted the Queries object. This opens the interface for creating the query, and offers a number of options for creating the new query.
Choose Design View and click OK. This opens up the next window. If you choose Customers from the list of tables, the table related list is added to the design pane of the query designer as shown. After choosing the table (or tables, or queries and tables as the case may be), close the Show Table window.
Now create a Select query by simply choosing the columns you want included in your selection. If you click on the drop-down arrow next to Field : it brings up a drop-down showing all the columns in the table. You can choose the columns you want, in the order you want as shown. You can achieve the same result by just double clicking the column in the list on the design pane and the column is added to the list from left to right. This query and its modifications are saved as Qparam.
Turn a Select query into a Parametric query
Once you have the Select query it is very simple to turn it into a parametric query. All you need to do is shown in the next picture. Since we want to search the records for the Customer of our choice, the CustomerID becomes the parameter, and it is the one that is used in the criteria for the search. Enter [CustomerID: ] exactly as shown. After each change to a query you will be asked to save the modification, and you should not forget this to incrementally modify your query.
Row returned by the parametric query
When you double click the Query, Qparam in this case, the program pops up a little form asking for your input as shown. The form also shows that it is asking for a particular parameter, CustomerID:
For example, if you type in 'ALFKI' and click OK, the query returns all the selected column values in your select query as shown here.
The SQL behind this Query
Behind the scenes, the query runs an SQL command against the table. This can be reviewed by right clicking an empty area in the design pane and choosing the SQL View. This brings up the following window, which shows the SQL statement. You could make changes to this statement, but once you save this after modifying, you cannot access the design view.
Next: Turning this into a Data Access Page >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
|
| · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | | |
|
