Programming Pivot Tables for Access Forms

Pivot tables represent a powerful way to convey the data in Access databases, and they empower users to perform many tasks that used to require custom programming. In this article, Rick Dobson shows how you can enable your users to manipulate data with pivot tables.

Pivot tables are popular with financial and operational analysts because they serve as a “slicer and dicer” for data. More specifically, the analysts use pivot tables to view data in a cross table layout with two axes, rows and columns. In addition, pivot tables routinely offer a third dimension, sometimes called a filter axis, for organizing data. By adding a third axis (for instance, years), you can enable analysts to focus on the orders obtained by salespersons from customers in each year. Pivot tables commonly support aggregation and filtering along with other common data analysis features such as sorting. Pivot tables derive their name from the fact that they permit analysts to pivot, or drag, variables between row, column, and filter axes. This rich combination of features permits database developers to design pivot tables for analysts who can reuse the pivot tables without repeated requests for new arrangements of the same data. Continue reading

Posted in Old Material | Leave a comment

Printing Pesky ZIP Codes and Setting the Starting Page Number for Reports

This month, Ken takes on two questions: how to most efficiently store ZIP codes (and how to deal with printing or not printing the pesky hyphens), and how to set the starting page number for a report.

I’m having a problem storing postal codes. In some cases, I know the full nine-digit ZIP code, and in others, I only know the five-digit ZIP code. I understand that I can have the U.S. Postal Service provide me with the appropriate nine-digit ZIP codes for all my addresses, but I don’t want to go through that effort. The problem is that if I use an input mask that can handle nine-digit ZIP code correctly (with a hyphen after the fifth digit), it looks ugly when I print the data. All the five-digit ZIP codes end up with a hyphen following them. How would you solve this problem? Is there some other way to store the data that would make this work better? Continue reading

Posted in Old Material | Leave a comment

Preparing To Upgrade Access Tables to SQL Server 2005/2008

This article discusses some of the subtleties that you can apply to your database tables prior to upsizing your tables to SQL Server. I do this because once you are in an environment where you have Access as a front-end and a SQL Server back-end, things are going to get more complicated. Another good thing about this article is that it will probably improve your database model and reduce the size of your database at the same time.

Continue reading

Posted in Old Material | Leave a comment

PivotTables in Access

Russell Sinclair shows how to summarize data using PivotTables.

ONE of the great new features of Access 2002 and beyond is actually one that’s been included in Excel for some time—the ability to create dynamic information summaries in tables and charts. These summaries are called PivotTables and PivotCharts, and they’re designed to allow a user to quickly create a summarized view of the data on which a form or datasheet is based. PivotTables and PivotCharts are part of the Office Web Components included with Office XP and Office 2000, and are actually based on ActiveX controls in this library. This means that these items can be used in forms, datasheets, and on Web pages. However, it’s with Access 2002 that they’ve been integrated with Access forms and datasheets so that you can use these objects without having to insert a control. Instead, they’re defined as views that you can enable or disable as you see fit.

PivotTables and PivotCharts are a new concept to many developers. Even those people who have been using PivotTables in Excel for some time will be amazed at some of the functionality that’s been added in the latest version of Microsoft Office. This month, I will concentrate on the creation and use of the PivotTable object.

Anatomy of a PivotTable

PivotTables allow you to summarize data in columns and rows, in much the same way that you can summarize data in crosstab queries. PivotTables, however, are much more powerful than crosstabs. PivotTables can be used in any datasheet view of a table or query, or as a view of the data in a form. Figure 1 shows a form in PivotTable view, and the parts that make up the object itself.


Figure 1

The first and most important element of a PivotTable is the Total/Detail section. This area contains the summarized data and/or the individual data elements that make up the summary information. The Total/Detail section corresponds to the Value fields in a crosstab query and is shown as the main data in the grid. The data in this section can be viewed in one of two modes: as detail records or as totals. When viewed as detail records, each item in the source data is shown in its own cell. In this mode, data isn’t summarized in any way. When viewed as totals, data is summarized according to the settings or calculations that you’ve defined for your data. Totals view is the default view for a PivotTable, and is where the real power lies in this new feature.

In order to make the detail section of your PivotTable contain any useful information, you’ll normally want to summarize the data by some value, rather than just having a single total. In order to do this, you need to add either a Row field or a Column field, or both. Row fields are shown on the left side of the PivotTable, and allow you to add successive groupings by which your data is summarized. This area is the best area to use for groupings that are based on data that has a high number of unique values. A row is created in the table for each unique value in the Row field, showing a subtotal or detail data for that item.

Column fields work in much the same way as a Row fields except that individual values are shown across the top of the table, forming columns by which the data is further summarized. Each unique value in the Column field forms its own column in the table. This area is best used to summarize data that has a low number of unique values, as large numbers of columns can clutter up a PivotTable and make the information difficult to read.

Both the Row and Column field areas can contain more than one field by which the data is summarized. Multiple fields in each of these areas are shown as different field names in each title bar, listed in order of categorization level from left to right. Fields shown closer to the data are referred to as Inner fields, as they’re closer to the data, and fields further from the data are called Outer fields.

The final data element of a PivotTable is the Filter field or fields. These items, appearing above the table itself, allow you to filter the data in the PivotTable by a field that isn’t included as a Row or Column field. Now that you’re familiar with the terms used in working with PivotTables, you’re ready to learn how to create and use them.

Creating PivotTables

The first step in using PivotTables is to open a table, query, or form in PivotTable view. This view can be accessed from the View toolbar item when the object is opened. The first time an object is opened in PivotTable view, you’ll be presented with a blank PivotTable showing the different drop areas that you can use to create your table (Figure 2). Along with the blank table itself, you should see the PivotTable Field List dialog, shown in Figure 3. If this dialog isn’t visible, right-click anywhere on the table and select the Field List menu item from the context menu.


Figure 2. Blank PivotTable.


 Figure 3. PivotTable Field List.

The field list shows the available fields for your PivotTable. Fields shown in bold are those fields that are already in use in the table. Only those fields available in the output of a query or table, or those fields in use on a form will appear in this list. Note that PivotTables built on forms will only be able to access those fields that are bound to controls, and only for those controls that have their Visible property set to Yes. Also, hidden columns in query or table datasheets won’t be available for use in the PivotTable. If you want fields not listed in this dialog to be available, you’ll need to add them to the source of your data, create controls for them on your form, or unhide them from your source object.

To add a field to your table, select and drag the field from the field list to the appropriate drop area on the table, or select the field and then use the Add To button with the drop area selected in the drop-down to the right of the button. The field will then appear in the selected section, ready for use in your PivotTable.

Data summarization

When you add a field to the Total/Detail area in the table, the data may automatically be summarized by the PivotTable. By default, numeric fields are summarized using the Sum function, and non-numeric data is summarized using the Count function. Each total that’s been created in your PivotTable will show up in the field list under the Totals item.

To change the summarization function for a field, select the field in the table and choose AutoCalc from the PivotTable menu to view a submenu that shows the standard summarization functions that you can use for your field. If none of these functions is appropriate for your data, you can create your own calculated total or detail fields for use in the table.

Custom calculations can be created for detail data or for totals. However, calculations can only use standard VBA functions (you can’t call your own functions) and, if based on data, must be built from existing data at the same level. In other words, detail calculations can’t use data that’s not listed as detail fields, and total calculations can’t use existing data that’s not listed as total fields. You can’t create custom-calculated totals that reference detail fields (only AutoCalc can summarize detail records), and you can’t create custom-calculated detail fields that reference total fields.

In order to create your own custom-calculated field or total, from the PivotTable menu choose Calculated Totals and Fields and select the appropriate submenu item. You will be presented with the Properties dialog shown in Figure 4.

The Name section allows you to give your field or total a meaningful name by which you can refer to this item in other calculations. I suggest that you use the same naming conventions that you use for fields in your tables. This makes it easier to determine what fields you’re using if you reference this calculated field in another calculation later on.

The large area on the bottom of this tab allows you to define the calculation that will be used for your field. Use standard VBA syntax to create your calculation. If you want to base this calculation on an existing field or total, you can manually type the field name or select it from the drop-down below the edit area and click the Insert Reference To button. Using the button is the preferred method, as it means that the potential for typographical errors or errors in field references is reduced. The dialog won’t warn you if there’s an error in your entry, but the error will show up on the PivotTable itself. Click the Change button at the bottom of this dialog to save your changes. You can then close the dialog and return to designing your PivotTable.

When you use your own calculations in PivotTables, keep in mind that it’s often faster to have your query calculate these values for you. In some cases, this isn’t practical. However, PivotTables must recalculate custom fields each time the view is changed. If you create the calculation in your query, it’s only executed the first time the object is opened.

 
Figure 4. Custom calculation.

Using PivotTables

PivotTables are extremely flexible, and you can change them with little effort. You can rearrange fields on the table simply by dragging them around to change their order in each drop area, move them from one area to another, or even remove them from the table altogether.

All fields used as Row, Column, or Filter fields allow you to filter data based on values within a field. Beside each field caption in these sections, you’ll notice a dropdown arrow. Clicking on this arrow will display the Filter drop-down, like the one shown in Figure 5.


Figure 5. Field drop-down.

This drop-down presents a list or tree of the available filters for this field. You can exclude or include a set of values by changing the check box beside each item. By default, you can select multiple items in the list by checking the appropriate items. If you want to show only one item from the list at a time, select the field in the Filter area for which you want to change the behavior. From the PivotTable menu, select the Properties menu item and on the Filter and Group tab, turn off the option “Allow selecting multiple items when in filter area.” Note that you can’t modify this behavior for fields listed in the Row or Column areas—fields in these areas always support multiple selections.

Within each summary level of your PivotTable, you can show or hide lower summaries in inner fields. Each summary level can be accessed or hidden by clicking the + (expand) or – (contract) sign to the left of each row or column entry. You can also show or hide the detail records that make up the total for each entry by clicking the expand or contract commands to the right of each row or below each column. Detail records can also be accessed by right-clicking on a field or cell for which you want to view detail data and selecting the Show Details or Hide Details item from the context menu.

Occasionally when you’re using PivotTables, the data that you see might not be up to date with entries that other users have made. If you want to refresh a PivotTable so that it contains the most current data, click the Refresh (!) button on the toolbar, and the table will be recalculated. If you want to cancel a long-running refresh, simply press the Esc key to cancel the update.

You should also note that settings for PivotTables are shared between multiple users and are “sticky” between uses of a field. This has two important repercussions. The first is that if you share your database with other users, your PivotTable might change each time you access it. This could mean that your PivotTable isn’t summarizing the data the way that you think it should be. The other repercussion is that if you or another user has summarized or formatted a field in PivotTable view in the past, the formatting and summarization that will take place will mirror previous settings. Although this can be very useful in distributed databases, where each user has their own copy of a database, it has the potential to mislead you if you don’t pay close attention to the settings used when a field is added to the table.

Taking the next step

PivotTables are extremely flexible and, used wisely, can become an extremely powerful data analysis tool. Not only do they allow you to summarize Access data in this way, they can also be used in ADP files to summarize SQL Server data. This means that the lack of support for crosstab queries in SQL Server is no longer a problem for Access developers.

PivotTables support a large number of features that simply can’t all be explored in one sitting. The Properties dialog discussed briefly earlier is context-sensitive and can apply to cells, fields, totals, grand totals, and even the PivotTable itself. I suggest that you take some time to create some PivotTables for yourself and play around with the properties of each item to see what can be accomplished.

Next month, I’ll take a look at PivotCharts, and how they can save you from going “report-happy” in Access databases.

Posted in Old Material | Leave a comment

Persistent Recordsets in ADO

There are things ADO can do that no other data access method can. Among the coolest features is persistent datasets. In this article, one of the gurus of ADO shows you how to use this technology in your Access applications.

As an Access developer, by now you must have heard about ActiveX Data Objects (ADO). So, now that you’re getting familiar with ADO, I can give you a look at one of ADO’s advanced features: persistent recordsets. If you’re not an Access 2000 user, you’ll need to download ADO from the Microsoft Web site (see the sidebar, “Getting ADO”).

What are persistent Recordsets?

You’ve probably become very comfortable with the recordset object in DAO, RDO, and ODBCDirect; you’ve probably been opening recordsets and modifying data for years. A new feature of ADO 2.0 is the ability to open a recordset created from your Access database (or any other database, including SQL Server and Oracle) and save the recordset to disk. You can then open the recordset that now resides on the disk and modify the data it contains. If you choose, you can then reopen a connection to the original database and re-sync the saved the recordset with the original data in the database. Since the original database might have changed since the recordset was saved, you need to follow the re-sync with a process that checks for successful updates. ADO provides a way to do this also.

Saving a recordset to disk in ADO is very easy. All you have to do is use the Save method of the ADO Recordset object. The syntax looks like this:

In the Filename argument, you specify a valid path to output the data to. With the FileType argument, you specify what type of file format to save your recordset to. There’s only one valid file type in ADO 2.0: Table Datagram. You specify this file type with the predefined constant, adPersistADTG. With ADO 2.1, you also have the ability to save a recordset in XML format using adPersistXML (ADO 2.1 ships with IE 5.0, Office 2000, and Windows 2000).

I’ll demonstrate this feature of ADO with a complete code example that opens a recordset based on the Shippers table in Northwind and saves it to disk. Before saving a recordset to disk, you have to create a recordset. This code creates a Connection object for the database Saccess.MDB, using the OLE DB provider for Access (OLE DB is the technology underlying ADO):

With the Connection object created, the following code opens a recordset on the Shippers table, using the Connection object just created:

Now that the recordset is created, I can save its contents to the file Saccess.DAT with this code (after first deleting any previous versions of the file):

Opening a recordset

To open the recordset from disk, all you have to do is use the Open method of a Recordset object. Instead of specifying a SQL statement to load your recordset, you just specify the name of the file you want to open as the command you pass to the Open method. You must also tell ADO that the command you’re passing is a filename by using the Options parameter of the method:

In the following code, I’ll show how to load a recordset from disk and iterate through its contents, filling a string with values to populate a list box. This technique can come in handy if you’re in a client/server environment and want to fill some list boxes with data from the server. If the data doesn’t change that often, you can have an application that runs every night, deletes the old saved recordset on disk, and then replaces it with a new updated one. Throughout the following day, your Access application can read the saved recordset and fill list and combo boxes, or even local tables, without having to connect to the server, saving a lot of time.

This code re-opens the recordset that I created in the previous example:

With the recordset open, you can iterate through the recordset exactly as if it had been created from a database, loading the data into a string with each value separated by a semi-colon:

This string can be loaded directly into a ListBox’s RowSource to have the box display the values from the persisted recordset. Like a regular recordset, the persisted recordset must be closed when you’re done with it:

Working offline

The real power of persisted recordsets is that you have to ability to take the saved recordset and work with it offline. When you’re done with the recordset, you can send your changes back to the computer with the database to be re-synced with the data. In the following code, I’ll save the Orders table of Northwind database to disk:

Now that I have the Orders table saved to disk, I can e-mail it to someone who has to work with it offline, disconnected from the corporate LAN. When my partner gets the file, the recordset can be used in an unbound form in Access to look at the data and modify it. Figure 1 shows the unbound Access form used to perform this task.


 Figure 1

The way the unbound form works is by declaring a module-level ADO recordset variable called rst. I then create the recordset object on the form’s load event as shown in the following code, loading it with data from the persisted file. After I load the recordset into memory from disk, I fill the text boxes with the data from the current record in the recordset. Here’s the code:

Moving to the next record to browse through the form is easy. I just use the MoveNext method of the recordset object that I defined in the form’s Declarations section. I also check the recordset’s EOF property to make sure that I’m not at the end of the data. As I move through the recordset, I save the current record by using the Update method of the recordset. This effectively emulates the Access form’s behavior of saving the records as you scroll through them.

 

I’ve chosen to retrieve the data into an Access form for simplicity in the demo. However, since ADO is a COM library that can be called from any COM-compliant language, I could have used a Visual Basic form, or even a Web page. In addition, with ADO 2.1, I could have saved the recordset to XML format. XML increases the flexibility of the recordset, since there are XML processors for many different types of systems, including Mac, UNIX, Linux, and Windows (see Michael Corning’s article on XML, “Access to XML,” in the March 1999 issue for more information). Further, in Access 2000, the process of binding the form to the recordset would be even simpler since I’d only have to set the form’s Recordset property to the recordset that I created from the file.

Re-syncing the data

After all of the changes have been made in my unbound form, my partner can send the persisted dataset back to me. When I receive the file, I can re-sync the data. Re-syncing is a simple three-step process:

1. Open the recordset from the disk.

2. Set the recordset’s ActiveConnection property to a valid connection object.

3. Use the recordset’s UpdateBatch method.

The UpdateBatch method will update the records from the recordset back to the database.

Once the UpdateBatch method completes, you should loop through the recordset to check the status of each record and determine whether the record was re-synced successfully or not. Records will fail to re-sync if the record was deleted or updated since the recordset was saved to disk.

To check the status of a record’s re-sync, you first must filter the recordset to see only the affected records. You can use the recordset’s Filter method like this:

With the filter set, you can iterate through the recordset and check the Status property of each record. ADO will return many different status values to you, but you only want to know whether your record was re-synced or not. ADO will return the constant adRecUnmodified (value=8) in the Status property if the record was unmodified by the sync.

In this loop, you can perform as much error handling as you want. This can include your own custom conflict resolution code, since each field in each record will make available both the original value and your updated value. Figure 2 shows all of the Status codes that are available, as seen in the Object Browser.


 Figure 2

Here’s some code that will re-sync a recordset with a database and loop through the recordset after the re-sync to display the OrderID and the Status code of each record:

As you can see, the ability to save a recordset to disk and then work with it as a recordset in your code is a very powerful technique. Like the other features of ADO, persistent recordsets are available to you today using Access 97. Access 2000 will give you even more power with the new Recordset property, and ADO 2.1 will give you more flexibility by allowing you to save a recordset to XML format. So get out and start saving those recordsets to disk!

Sidebar: Foundation Concepts

ADO is Microsoft’s long-term replacement for DAO. Like DAO, ADO provides an object-oriented way to retrieve data, organized around the Recordset object. While offering a simpler object model compared to DAO, ADO also offers some features that DAO doesn’t have.

Sidebar: Getting ADO

If you don’t have ADO installed on your machine, you’ll have to download it from the Microsoft Web site at http://www.microsoft.com/data. In order to use ADO in your Access 97 or 95 applications, you’ll have to set a reference to ADO by opening up a module and selecting Tools | References from the main menu. Once you have the References dialog box open, you’ll need to set a reference to “Microsoft ActiveX Data Object 2.0,” and you’re ready to go. You must have ADO 2.0 installed to use persistent recordsets (you Access 2000 users can skip this step).

Posted in Old Material | Leave a comment