Instead of queries, use the Access 2007 PivotTable view

Access 2007 lets you create pivot tables without building a query. See how this feature can simplify your data analysis.

With Access 2007, you no longer need to build a query to create a pivot table. You can create one straight from a table by using PivotTable view. For example, suppose you want to know how many orders were shipped each quarter by country. Follow these steps:

  1. Open the table, go to the Views group, click the arrow, and select Pivot table.
  2. Click the Field List button, if the field list is not visible (Figure A).
  3. Click and drag the ShipCountry field to the row area (Figure B).
  4. Click Order Data By Month in the field list and then click and drag Quarters to the row area (Figure C).

Figure A

Figure B

Figure C

  1. Click Order ID in the field list and drag it to the filter area.
  2. Right-click the filter area, point to AutoCalc, and then select Count (Figure D).

Figure D

  1. Click Count Of Order ID under Totals in the field list.
  2. Click the drop-down arrow of the Pivot Table areas list and select Data area.
  3. Click the Add To button (Figure E).
  4. Click anywhere in the Qtr column and drag it to the row area (Figure F).

Figure E

Figure F

You can analyze the data further by applying filters to the pivot table. For example, by clicking the drop-down arrow of the ShipCountry field, you can limit the results to a specific country or countries. No additional queries are required.

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox