MS Access 2010: Create Simple Crosstab Query

Queries are very important tool for pulling out, searching and grabbing data from tables. It performs different actions or operations on the database and carry out results according to the needs. Microsoft Access Crosstab Query enables user to summarize information in a compact format which is similar to spreadsheet. This post elaborates why to use crosstab query and how to use it.

For Instance: If you want to analyze the marks secured by students in different courses, it would be difficult to analyze with Select query in which you will be scrolling down to find out the details of a specific student.

In the table shown below, there are three students in total, secured different marks in the respective courses.

Now if we want to show the marks obtained in different courses by each student, we need to change the names of the students in to column or field heading, and course name in to row heading to get a brief but complete view of the records. In crosstab query, we need to assign at least one row heading, one column heading and a value. So in this case we change student Name field in to row heading, Course name into row column heading and assign value to marks.

Navigate to Create tab to create query, click Query design for design view. Add table to see the contents.

query MS Access 2010: Create Simple Crosstab Query

Now head over to Design tab and hit Crosstab.

Crosstab

You will notice two new rows are added in field pane below by the name of Crosstab and Total. Now in the Crosstab row, click small drop-down button for options and select Row Heading for assigning Course field as row heading. In the next row beneath the Name field, select Column Heading from drop-down options. Select Value for Marks field and in it’s corresponding row (above), select Sum from drop-down options.

column added

Now Course, which was column heading earlier is converted into row heading, and names of the students which was row heading earlier is changed into column headings, resultantly brevity has achieved and now we don’t need to scroll down to see the details of any student.

Crosstab executed1

The demonstration shown above contains small amount of data, crosstab query real usage can be seen, when you have huge data sheet to deal with.

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *