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.
Now head over to Design tab and hit 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.
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.
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.
>>”Navigate to Create tab to create query…”
Where is the Create tab? I dont see it, cannot find it.
good example. Please me a little more help.
What happens if i have already created report based on the query above and data is added to the table after i create the report?
If you have any solution, please share it.
thanx
I have my own question I input the raw code in SQL mode and it does not execute as it should. Here is what I wrote and here is what I get if it lets me attach it
SELECT INVOICE.CUS_CODE,INVOICE.INV_NUMBER,INVOICE.INV_DATE,PRODUCT.P_DESCRIPT,LINE.LINE_UNITS,LINE.LINE_PRICEFROM CUSTOMER,INVOICE,LINE,PRODUCTWHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODEAND INVOICE.INV_NUMBER=LINE.INV_NUMBERAND PRODUCT.P_CODE=LINE.P_CODEORDER BY INVOICE.CUS_CODE,INVOICE.INV_NUMBER,PRODUCT.P_DESCRIPT;
thanks for the examsple!