How to Summarize Data in MS Access using Crosstab Query

Here we learn about the use of Crosstab Queries in MS Access, and how to create them.

When you are working on Access databases, and need a summary that looks more like an Excel spreadsheet, you might want to try using the Crosstab Query. Just like an Aggregate Query gives you an aggregate of certain fields, a Crosstab Query can give you a comprehensive summary of certain fields. Plus an Aggregate query can be used when you are looking for one final number, the function of Crosstab query is not exactly that. It does not give you a final outcome, but a probable summary, using which you can make sense of the contents in a tableSummarizing Data In MS Access Using Crosstab Query

Crosstab Query

Crosstab Queries In MS AccessOut of the several Special Queries that are available in MS Access, Crosstab query is one of them. This is in-fact one of those queries that provides you with a very well-structured form of data. A lot of the times users opt for a Crosstab Query simply because they are looking for a more structured summary of the data. As the results from this query are presented in a datasheet which has a structure that is different from what we usually see in Access.

Although a Crosstab query is not the only option that we have in MS Access, it is certainly one of the most structured options, when compared with any other Special Query in MS Access. As important it is for you to know how and why you should use the Crosstab query, you should also know how to create it. And any Access user would tell you that there is no better way to create a query than using the query wizard.

Using the Query Wizard to create Crosstab Query

  1. From the Query group in the Create Tab, select Query Wizard.
  2. In the appearing dialog box, you will get a list of all types of special queries. From here you need to click on Crosstab Query.
  3. Click OK after making your selection.
  4. From the appearing Crosstab Query Wizard, you need to select the tables that you want to appear in your Crosstab query results. You can select these tables either by selecting the Tables radio button, or Queries radio button. To proceed to the next step, click Next.
  5. From the available fields in your tables or queries, you now need to select those which you want to see as headings in your Crosstab Query Results.
  6. The selection in the previous step should be determined by what kind of results you are expecting and what exactly do you wish to evaluate. So make sure you select the right fields.
  7. What field selection you make initially, will determine the set of questions you are asked or the options you get in the remaining steps. Keep on selecting the most appropriate option, and proceed by clicking on Next
  8. The final question that you will be asked is going to be what name you wish to give to your query. Enter a title, select the view query option, and click on finish.

While Access users can safely boast about the user friendliness of the application, they do have to worry about its robustness. Access crashes are common and hence it is advisable to keep an mdb fix tool nearby to deal with contingencies.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.