Using a crosstab query to summarize data in Microsoft Access

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.

Find out more about our Microsoft Access training courses Sydney, Australia or our Microsoft Access training courses London, UK.

 

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Access, Microsoft Access 2007, Microsoft Access 2010 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s