Microsoft access tutorial 5

Unit 9. Crosstab queries (I)

Introduction

A crosstab query is used when we want to represent a summary query with two grouping columns like a double input table, in which each one of the group columns is an entry, one down the left side of the datasheet and the other across the top.

E.g we want to obtain the monthly sales of our employees from their sold orders. We need to design a summary query that calculates the sum of the amount of orders grouped by employee and month of sale.

The query will be much more elegant and clear, presenting the data in a more compact form as we see next:

Well this last result is obtained by way of a crosstab query. Observe how one of the grouping columns (Agent) defines the rows that appear (there is a row for each agent), whilst the other grouping column (month) now serves to define the columns, each month value defines a column in the result, and the convergence cell of an agent value and a month value is the summary column, the one that contains the summary function (the sum of the sales). Crosstab queries can be created from the design view but it is faster more comfortable to use the wisard. Unit 10 . Action queries (I) Action queries are queries that permit us in just one operation to perform changes on one o various rows in a table. With these queries we can create a new table including in it the records of another, change the data stored, insert new records or erase old records. Either way, before making the modification effective a dialogue box appears to confirm the operation, giving us the opportunity to cancel. These messages can be unshowned, if you want to know how click here . Depending on what the query performs, there are fout types of queries: Make-Table queries Update queries Append queries Delete queries Next we will explain each type of action query to you. Make-Table queries. A Make-Table query creates a new table with the records stored in one or more tables. They are usually used to create working tables (we create it for a determine task, for example to store the results of a complex query that is taking long to execute and that we will use in various reports, and when we are finished with the task we erase it). They can also be usefull to create export tables (extract data from a table to send it to someone or to make copies of our tables). And finally they are used to create history tables. To create a Make-Table query: Open a new query in design view. We add one or more tables from where we are going to extract the data to save in our new table. We design the query in the same way as a normal selection query, so that only the records we want to save in the new table will appear. Then, we drop down the Query menu and select the Make-Table Query... option. or, we can drop down the list from the button on the toolbar and select the Make-Table Query... option. The Make table dialogue appears: We type the name of the new table in Table Name: box. We normally create the table in the same database (Current database option) but we can create it in another database, and in this case we will need to check the Another database: option and type the name of the database where the table should be made in the File Name: box. It needs to be the complete name including the complete path, and therefore it is easier looking for the database using the Browse... button; click on it and search for the database where we want to save the new table. Finally, click on the OK button and we return to the Query design: The design window is the same as that of a selection query, and in it we define the selection query in order to obtain the data to be saved in the new table. The only difference is that on the title bar, after query name, we can see the words Make Table Query and if we open the query properties by clicking on the button on the toolbar we will see the name of the table to create in the Destination table property and the database where it will be made in the Destination DB as we can see below: To see the data that will be stored in the new table, before create it, click on the Datasheet view type on the toolbar or drop down the View menu and select the Datasheet view option. This option allows us to visualize the data without creating the new table to make sure they are the correct data. The Run query option makes it so that the new table is made with the data obtained from the query. To run the query click on the button or drop down the Query menu and select the Run option. When we run a make-table query we are advised of this, and we are also advised when a table already exists with the same name as the new one. The fields in the new table are named as the headers of the query fields, and inherit the same data type as the source fields but they do not inherit the properties such as the primary key, indexes, etc...