Ms Access 2010 tutorial:crosstab query


Crosstab Query

XIV. Crosstab Query

A crosstab query is a type of the select query that allows you to summarize data to make it easier to read and understand. The crosstab query displays data in a datasheet, one down the side of the datasheet and other across the top. You can use the crosstab query to implement aggregate functions such as sum, average, max, min, count, standard deviation, and variance.  To create a crosstab query, you have three ways:

- Using Query Wizard

- Using Query Design

- Using SQL statement

XIV. 1. Create a crosstab query by using query wizard

Using query wizard is the best way for you if you create a crosstab query for the first time. The crosstab query wizard guide you to create a crosstab query step by step. In addition, it can group the date data into intervals(Year, Quarter, Month, Date, Date/Time) automatically when you use the field that contains data/time data as the column heading. However, you cannot use more than one table as record source, use expression to create fields,  and add parameter prompt, by using the wizard.

For example, you want to create the crosstab query by using Table1 as record source. The Table1 contains three fields including CompanyName, CategoryName, and SubTotal. You set the CompanyName as row heading, the CategoryName as column heading, and SubTotal as Value. To do this task, follow the steps below:

- Click Create tab

- Click Query Design, in queries group

A New Query dialog box appears

- Click Crosstab Query Wizard

- Click Ok

A Crosstab Query Wizard first step dialog box appears:

Ms Access query crosstab 1

This dialog box ask you to choose which table or query contains the field that you want to create the crosstab query.

- Click Table1

- Click Next

A Crosstab Query Wizard second step dialog box appears:

Ms Access query crosstab 2 

This dialog box ask you to choose the fields which you want as row heading. You can choose up to three fields.

- Click CompanyName and then click Ms Access select a field

- Click Next

A Crosstab Query Wizard third step dialog box appears:

Ms Access query crosstab 3 

This dialog box ask to select which field you want as column heading. Note that you can select only one field for column heading.

- Click CategoryName

- Click Next

A Crosstab Query Wizard fourth step dialog box appears:

Ms Access query crosstab 4 

This dialog box asks you to select the functions that you want to calculate the  field values; and allow you to choose to include row sums or not to include. If you check Yes, include row sums, the result of crosstab query will create an additional field that sums the values for each row. See the figure below:

Ms Access query crosstab include row sum 

- Click Next

A Crosstab Query Wizard last step dialog box appears:

Ms Access query crosstab final 

This dialog box asks you to name your crosstab query and to choose options for viewing the query or modify the design. View the query means that you open the query in datasheet view. If you select Modify the design, you will open query in design view.

Note: The crosstab query's name is optional.

 - Click Finish

- Click Run, in Design tab, on the Results group

You will get a crosstab query as figure below:

Ms Access table1 

Ms Access a crosstab table

Note: A crosstab query need one or more field for row headings, only one field for column headings, and a field for values to summarize.

XIV. 2. Create a crosstab query by using query design

If you create a crosstab query by using query design, you can use more than one table or query as record source, have more control over the process, use expression as fields in the query, add parameter prompt, and add other features that the wizard cannot do. By using query design, the crosstab query use Total and Crosstab in the query design grid to specify which field is used as row headings, which field is used as column headings, and which field is used as values to calculate with aggregate functions.

For  example, you want to a crosstab query by using data from TblEmployee. You set EmpName field as row headings, DOB field as column heading(group as year interval), and Sex field as summarize values. To do so, follow the steps below:

- Click Create tab

- Click Query Design, in Queries group

A Show Table dialog box appears

- Click TblEmployee

- Drag and drop or double-click EmpName, DOB, and Sex field in the query design grid

- Click Crosstab Query and then do follow the figure below

Ms Access a crosstab query in design view 

- Click Run to see the result

Ms Access a crosstab query result 

XIV. 3. Create a crosstab query by using SQL statement

Besides the two methods above, you can use SQL statement to create a crosstab query. An SQL statement for building crosstab query is TRANSFORM statement. It has the following syntax:

- Using only one table or query as record source

TRANSFROM AggFunction

SELECT FieldName1,..., FieldName3

FROM TableName or QueryName

GROUP BY FieldName1, FieldName2, FieldName3

PIVOT FieldName;

- Using more than one table or query as record source

TRANSFROM AggFunction(Table1.FieldName)

SELECT Table1.FieldName,..., Table2.FieldName2

FROM TableName1, TableName2,... or QueryName1, QueryName2,...

GROUP BY  Table1.FieldName, Table2.FieldName1, Table2.FieldName2

PIVOT Table1.FieldName or Table2.FieldName;

Note: By using the two SQL syntax above, make sure that your table name or query name and their field names must not contain space. If your table name or query name and their field names contain space, use this sign [ ] to cover table name or query name and their field names.

The explanation of the syntax:

AggFunction: It is an SQL aggregate function that uses to summarize the data.

FieldName1, FieldName2, FieldName3:  These fields use as row heading of the crosstab query.

TableName or Query:  A table or query that uses as record source.

FieldName: A field that uses as column heading.

For instance, create a crosstab query by using data from TblCustomer. You want to set CusID and CusName fields as row headings, Sex field as column heading, and Addr field as the values to summarize. To do that, do the following:

- Click Create tab

- Click Query Design

- Close Show Table dialog box

- Click drop down arrow of SQL View and then click SQL View

Or right-click on blank space above query design grid and the click SQL View

Or click SQL View on the status bar of Access window

- Type the following SQL statement

   TRANSFORM Max(Addr)

   SELECT CusID, CusName

   FROM TblCustomer

   GROUP BY CusID, CusName

   PIVOT Sex;

- Click Run to see the result

If you want to sort data in ascending or descending order, use ORDER BY clause by inserting it between GROUP BY and PIVOT clause. An ORDER BY clause sorts data in ascending order  by default. If you want your data to be sorted in descending order, type DESC ​after the field name you want to sort by.

For example, you want to sort CusName field of example above in ascending. To do so, you just add ORDER BY clause between GROUP BY and PIVOT clause as the following: 

  TRANSFORM Max(Addr)

   SELECT CusID, CusName

   FROM TblCustomer

   GROUP BY CusID, CusName

   ORDER BY CusName

   PIVOT Sex;




Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.