With Microsoft Access 97 you can easily define @Rqueries@r in your databases.
|
A query is a database object that provides a custom view of data from one or more tables, or from other queries.
|
Queries hold the key to the power of a relational database, enabling you to bring related data together in different ways.
|
Access uses a variant of the @RS@rtructured @RQ@ruery @RL@ranguage (SQL) to generate queries.
|
SQL is an acronym, sometimes pronounced "sequel".
|
SQL is an English-like database command language that evolved from SEQUEL (@RS@rtructured @RE@rnglish @RQue@rry @RL@ranguage) - developed by IBM in the early 1970s.
|
A version of SQL has been adopted as the official standard for relational databases by the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
|
However, most software companies have implemented variations of the standard to handle specific features of their products.
|
Access complies with the ODBC (@RO@rpen @RD@rata@Rb@rase @RC@ronnectivity) standard.
|
The ODBC standard is a @RC@rommon @RL@ranguage @RI@rnterface (CLI), aimed at enabling different relational database systems to communicate directly with each other in a standard way.
|
This enables it to "talk" to the many other database applications that now also support the ODBC standard, provided you install the appropriate database drivers.
|
To install or modify your ODBC setup, you double-click the @U32bit ODBC@u icon in the Control Panel, and add the required driver on the User DSN tabbed page.
|
Each query you create in Access is stored as a SQL command.
|
You do not need to be familiar with SQL to define queries in Access - you can use a design grid or a Query Wizard.
|
However, you also have the option of creating or editing queries by entering statements directly in SQL.
|
For more information on SQL see the CBT Systems course @RSQL@r @RProgramming@r.
@@
There are three main types of query you can define in Microsoft Access:
ò select queries
|
ò crosstab queries
|
ò action queries
|
A @Rselect@r query is one that retrieves information from the tables or other existing queries in your database.
|
These are useful for sifting through table and retrieving records, or combinations of records, that meet particular criteria.
|
Access creates a @Rrecordset@r of the selected data at the time that you run the query.
|
You can work with a recordset in much the same way as you would with a table.
|
But unlike a table, a recordset is not actually stored in your database - it is generated each time you open or run a query.
|
A @Rcrosstab@r query displays calculated values (such as sums, counts, or averages) from a field in a table, cross-tabulated according to entries from other fields.
|
These field entries are listed down the left-hand side and across the top of the datasheet resulting from the query.
|
With an @Raction@r query you can make changes to many records in one operation.
|
There are four kinds of action query:
ò delete queries
|
ò update queries
|
ò append queries
|
ò make-table queries
|
A delete query deletes a group of records from one or more tables in your database.
|
With an update query you can make global changes to a group of records in your tables.
|
An append query enables you to add fields or records to existing tables.
|
You can create a new table from all or part of the data in your other tables using a make-table query.
@@
Suppose you have a table called "Customers" in your database.
|
The table contains information about the companies that are your customers.
|
You wish to create a select query to extract all the records from the table, but display only certain fields.
|
For example, it may be useful to extract only the Company name, Contact name, Telephone, and Fax fields from the table.
|
To create a simple query based on a single table, you select the table (on the Tables tabbed page in the Database window) and choose @UQuery@u from the @UNew Object@u button's drop-down list.
|
If the last new object you created was a query, the @UQuery@u icon will be displayed on the button, and you can click the button itself (without using the drop-down list).
|
In the New Query dialog box, you click @UOK@u to create a new query without using a wizard.
|
This opens the Query window in Design view.
|
In this view, the top part of the window shows field lists for the tables (or queries) you chose for the new query.
|
The bottom part of the window is the design grid that you use to design a query.
|
Each column in the grid represents a field (from the chosen table) that you work with in the query.
|
These fields could be fields you want in the resulting recordset, fields you want to sort, or fields you want to test for values.
|
You can also generate custom field names (to display in the recordset) or use complex expressions to generate a calculated field.
|
You can select a field from your chosen table using the drop-down list in the first column.
|
When you do this, the name of the table is inserted automatically below the field name.
|
When you click the @UField@u row in the second column, the drop-down list button becomes available again.
|
You can use it as before to select a second field for your query from the Customers table.
|
You repeat the process until the fields you require are shown in the design grid.
|
Should you wish to sort the records, you can choose the sort order based on a particular field from the drop-down list in the Sort row of the relevant column.
|
If you do not choose to sort the records, they will be displayed in the recordset in the order in which they are retrieved from the database table.
|
You can sort on multiple fields as well - in which case Access implements your sorting criteria from left to right in the design grid.
|
When you are satisfied with the design of your query, you can click the @URun@u button to see the resulting recordset.
|
Alternatively, you can select the @UDatasheet@u @Uview@u of your query from the drop-down menu on the @UView@u button.
|
Notice that the @UView@u button's menu shows the Design view to be the active view.
|
Also, the @UView@u button itself shows the @UDatasheet View@u icon. In this case, you could simply click the button (without using the drop-down menu) to see the Datasheet view.
|
The Datasheet view shows the results of your select query.
|
The column headings in the list are the four fields you selected from the Customers table.
|
And the records are listed in alphabetical order of Company name (according to the ascending sort order you chose for that field).
|
You can format the Datasheet view of the query just as you would any other database object.
|
You can return to the Design view of your select query by clicking the @UView@u button (which shows the @UDesign View@u icon).
|
Finally, you click the @UClose Window@u button from the current view of the query.
|
You click @UYes@u to save the design of your select query.
|
Then you type a name for the query in the Save As dialog box, before clicking @UOK@u.
|
You are returned to the Database window, where you can click the @UQueries@u tab to see a list of your queries.
|
Your new select query is the only one you have created in this database.
@@
The easiest way to create a select query based on more than one table is to use the Simple Query Wizard.
|
You click the @UNew@u button on the Queries tabbed page to open the New Query dialog box.
|
Alternatively, you can use the @UNew Object@u button.
|
Then you select @USimple Query@u @UWizard@u from the list, and click @UOK@u.
|
The first step is to choose the fields you want in your query.
|
These can be from any existing tables or queries in your database.
|
You move a highlighted field from the Available Fields list to the Selected Fields list by clicking the right arrow button.
|
Then you can choose another table to provide fields for the query.
|
Again, you select the fields you require from the table (or query) using the arrow buttons.
|
When you are satisfied with the selection, you click @UNext@u to continue.
|
You wish to create a detail query, so you click @UNext@u again.
|
Access suggests a name for the query which you can accept or type over with a name of your choice.
|
The final choice given by the Simple Query Wizard is between opening the query and modifying its design.
|
If you click the bottom radio button, the wizard will take you into the Design view of the query.
|
You wish to open the query, so you click @UFinish@u.
|
The results of your new query are displayed in Datasheet view.
|
The @UView@u button shows the @UDesign@u @UView@u icon, so you can click it to see the field lists and design grid for the query.
|
All the tables you selected fields from, using the Wizard, are shown in the top part of the screen.
|
You will see that there are lines joining fields in the different tables.
|
These join lines are created automatically by Access if the relationships were previously defined.
|
If relationships are not already defined, you can create join lines yourself by dragging a field from one table to the equivalent field in another table.
|
The symbols above the lines show the relationships to be the one-to-many type.
|
These symbols are displayed only if referential integrity is enforced.
|
You can see the properties of a join by double-clicking the join line, or by right-clicking the join line and selecting @UJoin Properties@u.
|
Right-clicking a join line also gives you the option to delete the join.
|
The first option in the Join Properties dialog box is the default setting, and represents an @Rinner@r @Rjoin@r.
|
This type of join selects only those records that have matching values in the joined fields from both tables (or queries).
|
You can change the join type by selecting either the second or third option, before clicking @UOK@u.
|
The arrowhead on the join line indicates that this is now an @Router@r @Rjoin@r.
|
In this case, the Sales table contributes all of its records, even if there is no matching "Product ID" field in the Products table.
|
With an outer join, all records from one of the tables are returned. Where there are no corresponding records in the other table, the recordset will contain empty cells in the relevant field.
|
If you are interested in seeing (or editing) the SQL statements Access has stored with your query, you can select @USQL View@u from the @UView@u button's menu.
|
Another way of doing this is to choose @USQL View@u from the @UView@u menu.
|
This gives you a useful way of learning about SQL - you can create a query using the design grid or a Wizard, and then switch to SQL View to see how Access interprets the query in SQL statements.
|
Now you can move back to other views of the query, or click the @UClose@u @UWindow@u button to close the query.
|
You click @UYes@u to save your changes and return to the Database window.
@@
Suppose you now wish to design a select query based on a number of tables, and with multiple criteria.
|
First you click the @UNew@u button on the Queries tabbed page (or click the @UNew@u @UObject@u button displaying the @UQuery@u icon).
|
The design grid gives you more flexibility than the Simple Query Wizard when creating select queries, so you click @UOK@u.
|
The Show Table dialog box allows you to place the tables or queries you want to use in the top part of the screen.
|
You click the @UAdd@u button to show the selected table.
|
When you have chosen the tables you require, you click @UClose@u.
|
Now you use the drop-down lists as before to select fields in the design grid.
|
Suppose you then wish to apply selection criteria to filter the records returned in the query.
|
For example, you may be interested in the sales of products having Product IDs 2, 3, or 4 in Boston and San Francisco.
|
First you type @RBoston@r at the Criteria row for the City field.
|
When you click the next or row, Access adds the required quotation marks around the text you typed for the first criterion.
|
If you are testing a text field, quotation marks are required.
|
<f>Now you type @RSan@r @RFrancisco@r at the or row in the City field.
|
To select Product IDs 2, 3, and 4, you type the clause @RBetween@r @R2@r @RAnd@r @R4@r at the Criteria row in the Product ID field.
|
You can see the results of the query in Datasheet view by clicking the @UView@u button.
|
The resulting recordset contains records with Boston @Ror@r San Francisco in the City field, @Rand@r Product IDs between two and four inclusive.
|
When you click the @UClose Window@u button, you are prompted to save the query by clicking @UYes@u.
|
Then you type a name for the query before clicking @UOK@u.
|
Remember that this saves only the query - and not the recordset.
@@
Sometimes it is useful to base a new query on an existing query in your database.
|
For example, an existing query might already contain all the fields you need for your new query.
|
This saves you from having to select fields from individual tables when you design the query.
@@
It's easy to make changes to existing queries in Microsoft Access.
|
You select the query you want to edit or customize, and click the @UDesign@u button.
|
This opens the Design view of the query, where you can make the changes you require.
|
Suppose you wish to add a new field to the query.
|
First you highlight the column to the right of where you want the new field to be, and select @UInsert - Columns@u.
|
You need to add another table to the top part of the screen, so you click the @UShow Table@u button.
|
You select the table you require (@UProducts@u) and click @UAdd@u.
|
You can also add a table or query to a query by dragging the table or query name from the Database window to the top part of the query in Design view.
|
Then you click the @UClose@u button to return to the design grid.
|
The field you wish to add to the query is now available from the drop-down list in the Field row of the new column.
|
Sometimes it is useful to apply a filter to a field, without showing that field in the recordset; you can do this by clearing the appropriate @UShow@u checkbox.
@@
Now suppose you would like to calculate a new field for the query from existing fields in the database.
|
For example, you could create a field that shows the result of multiplying the Unit Price and Units sold fields from the Sales table.
|
You place the cursor in an empty column in the design grid.
|
Then you click the @UBuild@u button to create a new calculated field.
|
This opens the Expression Builder dialog box, which gives you an easy way of entering expressions.
|
You open the Sales table folder and select a field to include in the expression.
|
You click the @UPaste@u button to place the selected field in the expression.
|
The field is inserted (with the correct syntax) in the top window.
|
Now you click the @U*@u button to include a multiplication operator in the expression.
|
Then you select @UUnits sold@u and click @UPaste@u to add that field to the expression.
|
When the expression is complete, you click @UOK@u.
|
Access inserts the expression in the Field row of the chosen column.
|
When you press @UEnter@u you will see that Access has named the new field "Expr1".
|
This name will be shown as the field name when you run the query, or view it in Datasheet view.
|
To change the name of a field, you click any row in that field's column in the design grid, and click the @UProperties@u button.
|
In the Field Properties dialog box you can type a description of the field, and a caption for the field.
|
When you close the dialog box, the caption is not reflected in the expression name.
|
However it is shown as the field name when you switch to the Datasheet view of the query.
|
Notice that the Product ID field (to which a filter criterion is applied) is not displayed, as its @UShow@u checkbox was cleared in the design grid.
|
To save the query with the changes you have made to it, you select @USave As/Export@u from the @UFile@u menu.
|
You click @UOK@u to retain the query's current name.
|
If you wish to print the results of the query, you click the @UPrint@u button.
|
You can return to the Database window by clicking the @UClose Window@u button.
@@
Suppose you want to make changes to an existing query in your database.
|
For example, the current database has two fields called "Name" - one in the Products table, and another in the Categories table.
|
If one of the "Name" fields is retrieved by the query, it might be useful to change the field caption in the recordset to avoid any ambiguity.
