Blog

Create crosstab query in access is very important to analysis your data with different angle. A crosstab query is use to get the sum, average and other aggregate functions. In MS Access database the crosstab query divided into two parts the 1st is left side and 2nd is the top with in columns.

When you create crosstab query in access it depend on your which field are to be the row handling and which filed of table are columns headings.

To create a crosstab query in access in 2016 you must have the two table data if you don’t know how to create table in MS access , first you read my previous article how to create table in MS access 2016.

To create crosstab query in access first we create sample data in database and save some example data into the tables. Here I create three tables. And we process these table data into the crosstab analysis.

1. Product Table

The first table is tbl_products and have the following sample data.

2. Customer Table

The first table is tbl_customer and have the following sample data.

3. Customer Order Table

Design the customer order table as below here I use access built in function current date time filed to get the record modify date when user inert some order in this table.

The first table is tbl_customer_order have the following sample data.

4. Customer Order Detail:

The 4th table is customer order detail it is the child table of customer order table (tbl_customer_order). The customer order table is parent table. And create the relationship between these two table like below.

For learn more about database relationship please click here

Now we have successfully design the tables in our database so the next step is to create crosstab query in access. in MS Access there are two method are used to creating crosstab query in access database 1st is query wizard and 2nd is query design.

1. Query Wizard

To creating the crosstab query in ms access follow these simple and easy step.

Stp 01. Click on query wizard in top ribbon.

Stpe 02. Choose the 2nd option cross query wizard and click ok.

Step 03 . Choose the table which you want to create in crosstab query. Here I choose the tbl_customer_order table and then click on next.

Step 04. Choose the field which you want to keep in row handling. I want to keep product name. Click next.

Step 05. Choose the table field which you want to keep in column heading i.e order_qty, then click next.

Step 06. This is last step in this step choose the function like sum, average of filed and get the summary in row handling. click next

After click next a lost dialog will appear then click on finish the following query will be show like below.

The Rsult

1. Query Design

The 2nd method to create crosstab query in access 2016 is query design in this method we learn more function like criteria, sort record, hide column and other aggerate function.

In this method we learn how to create access crosstab query multiple values, access crosstab query multiple column headings, access crosstab query where clause and last is very important how to include row sums in the crosstab query.

Click Here To Download Sample Inventory_pro database.

Just follow these simple and easy step to create crosstab query in access.

Sep 01 : Open your database and on the top ribbon menu choose query design the follow tables and query dialog box will appear. Choose the tables which you want to create crosstab query here you can choose either multiple tables.

And here I will choose the tow table as below.

1. tbl_customer_order
2. tbl_customer_order_detail

Step 02 : After adding your required tables in query design close the show table dialog. And click on the crosstab button on the ribbon.

After click on crosstab button you will see in it in below section like this.

Step 03: Choose table and its required filed from the design view. Here I want to get a report which include product, customer name, order date and sum of order qty of each customer. So what you will do

1. Choose product name as row heading
2. Choose customer name as row heading
3. Choose order date as column heading
4. And las is select order qty as value and select sum instead of Grouped By.

Step 04 : Click on the run button on the top of left corner which is red.

The Result Will Be

Now in the last step we you will learn how to filter a specific filed in crosstab query access.

How To Create Access Crosstab Query Filter

In access crosstab query design you need to get some specific record of row heading while create crosstab query in access like product or customer name. You define multiple filter at once in singe field and septate field.

So in the this crosstab query I want filter a specific customer for check how many order of such customer and its product.

In the criteria row of customer column enter the name of that customer which you want to get .

Run the query again and you will see the following result.