How to Create a Crosstab Query From Scratch in Microsoft Access

Video Transcript

"Hi. My name is Chip Browne. I teach computer programing and Microsoft Office at a local business college, Steven Henagar, here in Utah. I also am a creator for practice test for Microsoft Office Certifications. Today, I'm going to tell you how to do a crosstab query in Microsoft Office Access 2007. Microsoft Access is a registered trade mark of Microsoft corporation. I am not affiliated with Microsoft Corporation. Crosstab query, is based upon the transform statement of SQL. The syntax of the transform statement is, transform, followed by the agate function we want calculated, followed by a flex statement, followed by a pivot, in the pivot fields. Crosstab query creates a matrix, where across the top are values that are pivot values. And down the left column are values which are row headings, or the group by values, from our select statement. Let's create one. On the create tab, in the other group, click query wizard. Next, click crosstab query wizard. Next, choose the table of query's that contains the fields we want for our crosstab query results. Next, choose fields we want values for as row headings, down the left column. Next, choose the values we want as column headings. These are our pivot values. Next, choose the aggregate function that we want calculated in all the row, column combinations. Let's choose count. That will count the number of records that have the row-column combination of values. Here is the result. Some matrix, which has down the left, all the values of ship date. Across the top, all the values of ship city. And in the intersection, a count of the number of records, which have the combination of the row and column values. Now lets look at the sql that was generated. Here's the syntax. To do the sql, click, home, view, sql view. You see the transform, followed by the aggregate function, followed by the selected statement, and the group by clause. These are the rows followed by pivot and the field for the column headings. These are the pivot values of the columns. This is how you create a crosstab query from scratch. For more information, visit me chipbrowne.com. That's brown, with an e."