Figure 11 Query creation: selecting the information source(s)
Add the tables and/or queries upon which the query is to be based then Close the Add Table Dialogue box. The following figure shows the Query Window with one table (Account) added.
Figure 12 Query Specification
The Query Specification determines the condition(s) records must satisfy to be output, which fields are output, and how output is sorted.
The Fields appearing in the top row of the query specification are those which are needed because they contain data that needs to be queried and/or displayed. They are most easily specified by dragging and dropping from the table.
The Show boxes are crossed to indicate that the corresponding field should appear as output - if one is blank the corresponding field will not appear.
The Criteria are simply typed in (Access will automatically add quotes if they are omitted). Wild card characters (see above) can also be used.
To run a query after composing it, click the Datasheet View button on the tool bar.
To return to the design click the Design View button.
Editing Through a Query
Information can be edited through a query if certain conditions are met. A query is run and displays a set of records. Alterations can then be made to the underlying information in the tables upon which the query is based by altering the information displayed.
Sorting Information
The output records can be sorted in ascending or descending order on specified fields. The sort order is determined by entries in the Sort row of the Query Specification.
Figure 13 Sorting
The output resulting from the above Query Specification would be sorted in ascending order according to branch: where records have the same branch they would appear in descending order of balance.
Figure 14 Output
Operators
The comparison operators (<, <=, >, >= , =, and <>(not equal)) can be used in the usual sense. e.g. Criteria: >2
The like operator is used in conjunction with wild card characters - it will be automatically added by Access if omitted.
The Is operator can be used with the Null keyword to find missing information - e.g. Criteria: Is Not Null excludes records with no information in the corresponding field.
AND, OR and NOT
The AND, OR and NOT (Boolean) operators can be used (in conjunction with brackets if needs be) in specifying criteria. Complicated OR expressions can be entered by filling in more than one of the Criteria rows. The following query will display details of Accounts which satisfy (at least) one of the following conditions:
·
Managed at Branch 1, have type Savings or Current, and have a balance between £100 and £1000.
·
Managed at Branch 2.
Figure 15 A query using comparison and Boolean operators
Expressions
In the examples above, fields have been queried and displayed. However Access allows an expression as well as a field to be specified in the Field row of a query specification. The expression is treated in exactly the same way as a field. The Access Language Reference contains a comprehensive list of functions that can be applied to data in fields - the information can also be found in the Help system under functions: reference. The query below outputs the Account Id, Type (in upper case), Branch, and 5% of the balance labelled as 5pc_balance
Figure 16 A Query using expressions
Similarly a field (or expression) specified in the Field row can be compared against another field or expression specified in the Criteria Row.
Groups and Summary Statistics
Records can be grouped and simple summary statistics output on a per group basis.
The query (left) outputs the sum of the Account balances for each Branch at which Accounts are managed. Clicking in a cell on the Total row will display the list shown on the right hand side of the query (which is not part of the query proper but serves to illustrate some of the choices). Select from it by clicking one of the items.
Joining Information
In the examples above queries have been directed at a single table. It is frequently the case, however, that queries need to be directed at more that one table which contain related information.
Two tables, table1 and table2 say, may be joined over a pair fields - one from each table - which are defined over the same data domain. For each record in table table1, records in table2 are sought which have the same values in the join fields. A composite record is build from the pairs of records. The collection of all such records is the so-called natural join of table1 and table2 (often referred to as simply the join). This is illustrated by the example below where table1 and table2 to be joined - the join fields are field2 from table1 and field1 from table2.
In database theory, a number of different sorts of join have been identified as useful. For example, note that the last record of table1 does not contribute a record to the natural join because z does not appear as a value in table2 field1: we may want such an entry. A so-called outer join would generate a record (with a null entry in third column of the output).
 |
Access uses queries to join tables and supports both natural and outer joins. This is best illustrated by example: to find out details of Accounts including the address of the Branch at which they are managed it is necessary to join information in the Branch and Account tables. |
 |
Start by adding the Account and Branch tables to a new query. The join columns are identified in the diagram above by the line running between them. To draw the join line, click on the Branch field in the Account table, drag it over to the Branch table and drop it over the Branch Id field. Release the mouse button and the line appears.
Double clicking the join line displays the Join dialogue box (left) through which a natural or outer join can be requested. |
Figure 20 Output from the Query specified in previous Figure
Query Properties
Associated with each query are a number of properties, the settings of which affect the way it runs and what data is displayed. Those most commonly used are detailed below.
·
Output All Fields: outputs all fields irrespective of all other specifications.
·
Top Values: restricts the number of records displayed.
·
Unique Values: set to Yes to display unique records only. Unique Records is similar, but is effective only under certain circumstances. (Its description in the manual does not appear to fully describe its behaviour).
·
Run Permissions: Run either with the permission set of the user running the Query or with that of the Query creator.
Field Properties
Each field on the query specification has a number of properties which vary depending on its datatype. The Format (and Decimal Places perhaps), Input Mask, and Caption properties are analogous to those for fields in a table documented above.
Parameters
Query parameters enable a query to be specified in general terms. For example, you may routinely wish to look at Accounts managed at a particular Branch but want to vary the specification of the Branch. Specifying a parameter rather than an absolute value in the Criteria will cause Access to prompt for a value when the query is run.
Figure 22 Query parameters
To display the Query Parameters Dialog box choose Parameters... from the Query menu.
Global Editing
 |
In all the examples above queries have been used to select information - these are termed select queries. Queries can be used to insert, modify or delete information in database tables - these are so-called action queries. Use the Query menu to specify the type of query. |
 |
The format of the Query Specification varies according to the sort of query chosen. The following is an example of an update query which, when executed, will change the Account type from Savings to Current for all Accounts.
 To execute an action query click the Run button on the Tool Bar.
|
Relationships
Figure 25 The Relationships Window
Queries
