Microsoft Access has been a leading relational database product for many years. Each new version refines the concept and adds powerful new features, some of which can be overwhelming to the new or even the experienced user. Because this piece of software is so complex, instructor-led training is one of the fastest and most effective ways to get you up to speed on using these new tools effectively.

Let's look at an example of a technique learned in the advanced Microsoft Access training course.

In Access 2002, Microsoft added pivot tables and pivot charts as powerful data analysis tools that allow the user to organize and summarize the database information.

Introducing the pivot table


A pivot table allows analysis of a large amount of data, sorted and sub-sorted by any data field. With the help of the Pivot Table Wizard, it is easy to create comprehensive reports that present the data in practically any way imaginable.

A typical exercise you might see in an instructor-led training environment would give you a database of sales information and have you extract customer orders through a previously designed query. To make use of this data, you must display it in a useful format, and the pivot table is perfect for that.

Select View/PivotTable View from the main menu to create a blank pivot table. Creating a finished table involves simply dragging fields from the field list onto areas of the report.

For example, you might drag "Sale Representative", "Category", and "Month of Sale" to the column fields and "Amount of Sale" to the data field. This gives a report of all sales sorted by representative, sub-sorted by category of item, and sub-sub-sorted by month. The report is automatically subtotaled by category then again by representative.

You can expand or hide the detail at any level, thus hiding the detail for a representative or a category. You can filter certain fields, for example limiting category to "Toys" and month to "November" and "December" to see how the Christmas shopping seasons affected toy sales.

Advanced pivot table techniques


Microsoft Access training shows how a pivot table can do more than just present the data. By adding calculation fields, you can provide a further level of analysis. This allows you, for example, to analyze average sales by sales representative or sales volume by month.

Formatting allows you to emphasize certain data, easily drawing attention to trends such as profit or loss within certain sectors. You can create your own data groupings, such as tagging certain cities in your report and tagging them "Western England" and other cities as "Eastern England" to see if there is a difference in sales between the two regions.

Microsoft Access training has more to offer


This is a quick view of a small part of what an advanced training course offers you. With two to five days of instructor-led classroom training, you can acquire a wide range of skills that will beef up your resume and drastically increase your effectiveness when working with spreadsheet data.