If we need to summarise data such as products sold per region, we can do this by using a crosstab query which will allow us to use data from more than one table. You can create a crosstab query by using the Crosstab Query Wizard which will ask you to specify which field should be the row heading, which should be the column heading and which fields you want to calculate the summary on, and what type of summary you want – Sum, Average, etc.
To create a crosstab query using the Crosstab Query Wizard in Access 2007 or Access 2010:
Click the Create tab, and then in the Other group, click Query Wizard. The New Query dialog box will be displayed.
Click Crosstab Query Wizard, and then click OK. The first stage of the Crosstab Query Wizard will be displayed.
To base the query on a table, click Tables; to base the new query on an existing query, click Queries. Click the name of the table or query on which you want to base the query. Click Next.
Select up to three fields to be used for row headings. Click Next.
Select the field to be used for the column heading. Click Next.
If you have asked for a date you are asked how the date information should be grouped, e.g. per month. Click Next.
Select the fields on which the value will be based and the type of summary calculation to be used. Click Next.
You are then asked to name the query. Type in a name or use the suggested one. Click Finish.
The query will then be created and run, and the results displayed.