Unit 9. Crosstab queries (I)



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.

To continue with this Unit go to the next page...


Page. 9.1

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.