When defining queries you sometimes won't be interested in individual records in your database.
|
Instead you might wish to see, for example, sums or averages of different groups of data.
|
A @Rtotal@r query is a type of select query that you can use to calculate totals in a recordset.
|
Suppose you are interested in the total revenue generated by each of Centurion's products.
|
You decide to define a new query in Design view to create the recordset you require.
|
At this stage you have included the following fields in the query:
ò Name (from the Products table)
|
ò Unit Price (from the Products table)
|
ò Units sold (from the Sales table)
|
ò an expression multiplying Unit Price
with Units sold
|
Notice that you can change a field heading that is shown in the recordset by typing the name you require, followed by a colon, in front of the field name (or expression) in the design grid.
|
This is useful for making recordset column headings more descriptive, or for naming calculated fields.
|
You can see the recordset resulting from the current design of this query by clicking the @UView@u button.
|
The recordset shows individual sales (in Units sold) of each product, and indicates the revenue generated by each sale.
|
You can now group all the occurrences of each Product Name, and view the total number of units sold and the total revenue generated by each group.
|
To do this you click the @UTotals@u button in the Design view of the query.
|
A new row - the Total row - is now shown above the Sort row in the design grid.
|
By default, the Total row contains "Group By" entries in each field.
|
The records will be grouped according to the entries in each field when you run the query.
|
<f>Now the recordset shows one row for each unique set of values - but no totals are calculated.
|
You can replace Group By in any field with a total function that you select from the appropriate drop-down list in the design grid.
|
Alternatively, you can choose the total function you want by typing its name in the Total row in the design grid.
|
Here, you require the Sum function.
|
Each Product Name entry in the recordset now represents a group of records having the same product name.
|
Also shown are the total number of units sold (SumOfUnits sold) and the total revenue generated (SumOfRevenue) for each group.
|
In addition to Group By and the nine total functions, there are two more options available in the drop-down list:
ò Expression
|
ò Where
|
Expression allows you to create an expression, in the Field row, that uses one or more of the listed total functions.
|
You can use Where, together with criteria you set in the Criteria row, to filter out certain records from the groups in your query.
|
For example, you might be interested in the number of units sold and the income generated by the products AutoPay and Timescape together.
|
Running the query now returns the information you require, without showing the Product Name field.
|
If you want the product names to appear in the query's results, you must add the field to the design grid again, with the @UShow@u checkbox checked.
|
Now the Product Name groups are shown separately in the datasheet.
@@
A @Rsummary@r query is a total query that you can create using the Simple Query Wizard.
|
You click the @UNew@u button on the Queries tabbed page to open the New Query dialog box.
|
First you choose the fields you want in your query before proceeding to the next step in the wizard.
|
Now you can choose between a Detail and a Summary query.
|
The @USummary Options@u button becomes available when you click the @USummary@u radio button.
|
When you click the @USummary Options@u button, you have a choice of summary values Access can calculate for you for any numerical field.
|
As well as selecting one or more summary values to calculate for a field, you can get Access to perform a count of records in the source table(s).
|
You click @UOK@u to return to the query wizard.
|
Then you click @UNext@u to continue.
|
Now you can give the query a title, and click the @UModify the query design@u radio button to view its design.
|
When you click @UFinish@u, Access will take you to the Design view of the summary query.
|
As you can see, your summary query is just like a regular total query.
|
In the Datasheet view, you can now see the total number of units sold for each product group, and the number of sales of each product.
@@
A @Rcrosstab@r query is a special type of total query that allows you to see calculated values arranged in a spreadsheet-like format.
|
For example, you could use a crosstab query to summarize the number of units of each product you sold to each of your customer companies.
|
You can easily create a crosstab query by double-clicking the @UCrosstab Query Wizard@u in the New Query dialog box.
|
First you choose a table or query that contains the fields you want to use in the crosstab query.
|
If you wish to use fields from more than one table, you should use an existing select query containing the fields you require.
|
You double-click the required query name (or select the query and click @UNext@u) to continue.
|
Now you choose the field(s) that will provide row headings for the crosstab query.
|
To select a field, you highlight it and move it across with the right-arrow button (alternatively you can double-click the field).
|
Then you click @UNext@u to continue.
|
Now you select the field that will provide values for the column headings, before clicking @UNext@u again.
|
Although you can select up to three fields to provide row headings, only one field can be used to provide column headings.
|
Then you decide on a calculated number to be included in the body of the crosstab table.
|
You select a field and a function from the appropriate panes in the dialog box.
|
If you don't need row sums, you can choose not to include them before clicking @UNext@u.
|
A row sum summarizes the calculated entries in each row of a crosstab table by adding them together.
|
Finally you can name the query and click @UFinish@u to view the results.
|
In the Datasheet view, you can see at a glance the number of units of each product sold to each customer company.
|
In Design view, you can identify the query as a crosstab query by the presence of Total and Crosstab rows in the design grid, and by the icon on the @UQuery Type@u button.
|
Since the Crosstab Query Wizard has already saved the query, you can click the @UClose Window@u button to return to the Database window.
@@
You can also create a crosstab query without using a wizard.
|
You can use the design grid to start from scratch, or change an existing query (such as City Sales) into a crosstab query.
|
City Sales is currently a select query that returns this recordset.
|
To convert it to a crosstab query, you choose @UCrosstab Query@u from the @UQuery Type@u button's drop-down list.
|
Alternatively, you can choose @UCrosstab Query@u from the @UQuery@u menu.
|
Now the Total and Crosstab rows are visible in the design grid.
|
You use the drop-down list in the Crosstab row of each field to decide which part of the crosstab table that field's entries will form.
|
Each column heading must have Group By as the setting in the Total row.
|
Any field that provides row headings can have a Group By setting, one of the Total functions, or an expression that includes a total function.
|
For your crosstab query to work, you must specify one field as a value (as well as having one column heading and at least one row heading).
|
The field containing the Value setting in the Crosstab row of the design grid must contain a total function (or an expression using a total function).
|
Since you do not wish to use the calculated field, you can highlight it, and press the @UDelete@u key to remove it from the design grid.
|
Now you can click the @UView@u button to see the results of your crosstab query.
|
Notice that entries in the Name and Unit Price fields provide the row headings, while entries in the City field are the column headings.
|
When you click the @UClose@u @UWindow@u button, you are prompted to save changes to the design of your query.
|
On the Queries tabbed page, the icon next to City Sales indicates that it is now a crosstab query.
@@
Suppose you have a select query (called Products Query) that returns this recordset.
|
You decide to create a crosstab query based on this query to summarize the number of units of each product sold to your customers.
@@
An @Raction@r query is a query that makes changes to many records in one operation.
|
There are four types of action query:
ò make-table queries
|
ò append queries
|
ò delete queries
|
ò update queries
|
You might find that you often have to execute a particular select query against data that isn't changing.
|
In such a case, it could be useful to save the recordset as a new table to give you faster access to the data.
|
You can create a new table by changing the select query to a @Rmake-table@r query, and then running the converted query.
|
To convert this select query to a make-table query, you choose @UMake-Table Query@u from the @UQuery@u menu.
|
Alternatively you can choose @UMake-Table Query@u from the @UQuery@u @UType@u button's drop-down menu.
|
Then you type a name for the new table in the Make Table dialog box.
|
You can choose to add the new table to the current database or another one before clicking @UOK@u to return to the Design view of the query.
|
You can recognize this query as a make-table query by the icon on the @UQuery Type@u button.
|
Now you can make the new table by clicking the @URun@u button on the toolbar.
|
You could also select @UQuery -@u @URun@u to create the new table.
|
Access asks you to confirm that you want to create a new table from the selected records by clicking @UYes@u.
|
You can now see the new table if you view the Tables tabbed page in the Database window.
@@
An @Rappend@r query adds a group of records from one or more tables to the end of a table.
|
Suppose you have recently added a number of new records to the table called Sales.
|
You can use an append query to add the new data to the table Sales Jan 97.
|
Again, it is convenient to convert an existing query into the type you require.
|
You can use the @UQuery Type@u button (or the @UQuery@u menu) to select @UAppend Query@u.
|
Now you click @UOK@u in the Append dialog box to accept the name of the table to which you want to add the records.
|
Before you run the query, you must first add a field to the design grid that you can use to select which records to append.
|
You drag the field you want to use for setting criteria from the appropriate list to the design grid.
|
Then you type the condition(s) on which additions will be made in the Criteria cell of the field.
|
You can preview the records that the query will append in Datasheet view before running the query.
|
Then you return to Design view, and choose @UQuery - Run@u (or click the @URun@u button on the toolbar).
|
You click @UYes@u to confirm that you wish to append the new rows to the chosen table.
|
Then you can return to the Database window and open the Sales Jan 97 table to view the appended records.
|
The new records have been added at the end of the table.
@@
Suppose you wish to discontinue one of your products, and recall recent sales of that product to customers.
|
You can use a @Rdelete@r query to remove records relating to that product from one or more of your tables.
|
You begin by creating a select query in Design view, where you include the tables containing records you want to delete.
|
Then you select @UDelete Query@u from the @UQuery Type@u button's menu.
|
Now you insert all the fields from the table with related records in the design grid.
|
Access automatically inserts the word "From" in the Delete row.
|
In the design grid, you also need to select the field(s) from the primary table on which you want to set criteria.
|
Access inserts the word "Where" in the Delete row, and you can type the name of the product you want to delete from the tables at the Criteria row.
|
You can use the Datasheet view to check which data will be deleted when you run the query.
|
When you are satisfied that the query will select the correct records to delete, you can return to Design view and click the @URun@u button.
|
You cannot undo a delete action, so Access asks you to confirm that you want to delete the records.
|
If the related table is open when you run the delete query, you can use the @UWindow@u menu to view it.
|
The table now shows where the deleted records used to be.
|
When you next open the table there is no trace of the deleted records.
|
However, records meeting the criteria you specified in the design grid still remain in the primary table.
|
Access cannot delete records from the primary table and related tables at the same time - you must remove the related table and run the query again.
|
You take away the selected table by choosing @URemove@u @UTable@u from the @UQuery@u menu.
|
Now you click the @URun@u button again to delete the records from the primary table.
|
If you run a delete query on a single primary table (that is, a "one" table in a one-to-many relationship) first, Access automatically deletes related records in other tables if the relationships have referential integrity enforced and cascading deletes enabled.
|
You click @UYes@u to proceed when the confirmation message is shown.
|
The Products table (which was open when you ran the query) now indicates that the record has been deleted.
@@
The fourth type of action query is an @Rupdate@r query - which allows you to make global changes to a group of records in one or more tables.
|
Suppose you wish to modify entries in the Unit Price field of your Products table to reflect a global price increase of five percent.
