Getting to Know Microsoft Access, Part 5: Retrieving and Arranging Data Meaningfully - Select Queries
(Page 4 of 4 )
To create a new query choose Insert | Query. This will open the New Query dialog box as shown below:
Choose "Simple Query Wizard" to create your select query. You will be given a series of dialog boxes in which you will select the records and fields you want to use in your query; you must also enter a name for your query. You can include fields from any of your other queries or tables. In the first dialog box, select your desired fields from your other tables or queries and choose "Next." Choose to create a detail query with all the record data or a summary query that calculates totals based on the field values.
Just like with tables, you are given a datasheet view of your query that shows all the records returned from the specifications you selected, a design view that shows you the query structure, and a SQL view showing the SQL statements that comprise the query; every query uses SQL statements. Let’s take a look at the Design view for your query.
In the query design view you can see an upper pane showing the tables that comprise the query data and a lower pane with the Query By Example design grid that shows the elements of the query design. The design grid shows the following fields: Field, Table, Sort, Show, Criteria, and Or, as you can see in the following image:
The Field and Table fields show from which fields and tables the elements that comprise the query came from. The Sort, Criteria, and Or rows are the same as in the filter grid. The Show field determines whether the field is displayed in the query result or not: check or uncheck the box as you please.
The criteria field is especially handy for filtering the results of your query. The Criteria field allows you to specify exactly which records you want to see in the query by using specific operators. For example, you can use the LIKE operator in conjunction with the * operator to do something like this:
Like Cre*
Access will return anything that begins with the letters "Cre" and has additional letters after "Cre." Another example would be to use the OR operator to return either one value or another, such as the following:
DVD Or VHS
Moreover, you can use the AND operator, in combination with the greater than and less than operators to produce something like this:
>50 And <150
Other operators such as +,-,=,*,/,-,Is Null, Is Not Null, Between…And, and NOT can be combined similarly and work just as you would expect them to.
When you want to create complex criteria and you’re not sure how to do it, you can use the Expression Builder for help. Click into the cell where you want to put an expression and click the Build toolbar button in the Query Design view.
You can set the appearance and behavior of your query using the Query Properties dialog box. To do so put your insertion point in the table pane and press ALT-ENTER. You can modify a query at any time in the Query Design view. You can insert new fields, change the field order, and change field properties. You can add calculated fields to your query that are recomputed each time the query is run so you always have current data. Using custom calculations you create new fields by combining values in other fields like so:
[End Date] – [Start Date]
Or
[First Name]&” “&[Middle Initial]&” “&[Last Name]
When you created your simple select query you probably noticed several other options for creating queries in the Query Wizard such as: Find Duplicates Query, Find Unmatched Query, and Crosstab Query. A find duplicates query locates and shows records of the same value as your selected field. The find unmatched query shows records in one table that have no match in a related table. Finally, a crosstab query correlates summary values from two or more sets of field values. Choose the option you like best in the Query Wizard dialog box and follow the instructions as you did for the Simple Query Wizard.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 12 
