Creating a Denormalized Table from a Set of Linked Tables

There are situations where you need to create a single table filled with data from a set of linked Access tables (denormalize the tables). One such situation is the preparation of a data file for import by a mainframe, or a legacy database or spreadsheet application; another is for use in Access VBA code or by a query.

The process of creating a single flat-file table from data in a set of linked tables is called denormalizing the reverse process — writing data from a flat-file table back to a set of linked tables — is called renormalizing.

If you encounter a "Query too complex" message when trying to run a deeply nested query based on multiple tables (this is less of a problem now than with previous versions of Access, but still might happen with extremely complex queries), you can run a make-table query to create a flat-file table based on some of the linked queries and use that table as part of the final query, to reduce its complexity. The techniques I use in this chapter to prepare a single table of Access data for comparison with Outlook contacts can be modified for use anywhere you need to produce a single flat-file table of data from linked Access tables.

j^g^g^^jijggjj^^' The sample database for this chapter is Synchronizing Contacts.accdb.

In Access, my contact-related data is stored in a set of linked tables, as shown in the Relationships diagram (Figure 11.1).

The tables are normalized, which means that they are designed so that data of a particular type is stored in only one table, and only the linking ID fields have matching values. The tblCompanylnfo table is linked one-to-many with two tables: tblCompanylDsPhones and tblContactlnfo, because a company can have multiple phone numbers and IDs, and also multiple contacts. tblContactInfo is also linked one-to-many with two tables: tblContactIDsPhones, containing phone numbers and IDs for contacts, and tblContactAddresses, containing addresses.

The Relationships diagram for the Synchronizing Contacts database.

The Relationships diagram for the Synchronizing Contacts database.

Because Outlook only supports a fixed number of addresses and emails (three of each), and a larger (17) but still fixed number of phone numbers, for purposes of synchronizing contact data between Outlook and Access, only the matching addresses, emails, and phone numbers will be synchronized. Practically, this is not likely to leave much data unsynchronized, except in the case of phone numbers.

BV For best results when synchronizing data, when entering a phone number or ID in one KB™- of the subforms on frmContactlnfo, select one of the default selections for addresses, emails, and phone numbers from the drop-down list; they are the only selections that will be synchronized with Outlook contact items.

Figure 11.2 shows a phone number being selected on the Contact Information (frmContactInfo) form.

FIGURE 11.2

Selecting a default phone number type on the Contact Information form.

FIGURE 11.2

Selecting a default phone number type on the Contact Information form.

Of course, you will sometimes need to enter phone numbers that aren't on this list of default phone number choices (such as the Coffee Harvest Line number shown in Figure 11.4); you can enter a custom phone or ID description manually as needed, but these phone numbers and IDs won't be synchronized with Outlook.

Figure 11.3 shows the Contact Addresses tab of the Contact Information form; unless you need to enter data for very wealthy people who have more than three addresses, the standard three choices should be enough.

FIGURE 11.3

Selecting an address type for a new contact address.

FIGURE 11.3

Selecting an address type for a new contact address.

The Company and Contact Information (frmCompanyInfo) form displays company and contact information so you can easily match up contacts with their companies. Figure 11.4 shows the Company Info tab of this form, with a Company IDs and Phones subform.

FIGURE 11.4

The Company Info tab of the Company and Contact Information form.

The Company Info tab of the Company and Contact Information form.

Figure 11.5 shows the Contact Info tab, with a Contact IDs and Phones subform.

The Contact Info tab of the Company and Contact Information form.

The Contact Info tab of the Company and Contact Information form.

The sample database's main menu (shown in Figure 11.6) has a command button for selecting the Attachments folder path; its event procedure uses the same technique as for similar command buttons in earlier chapters, opening an Office Folder Picker dialog to let you select a folder. In this chapter the selected folder is used to temporarily store files for use as attachments when copying attachments from an Access table record to an Outlook contact or vice versa.

FIGURE 11.6

The main menu of the Synchronizing Contacts database.

FIGURE 11.6

The main menu of the Synchronizing Contacts database.

The code for the Attachments Folder Path button (listed next) starts by popping up a Folder Picker dialog for selecting the folder where files to be used as attachments are stored. The selected path is saved to the textbox under the command button:

Private Sub cmdAttachmentsFolderPath_Click() On Error GoTo ErrorHandler

Create a FileDialog object as a Folder Picker dialog box.

Set fd = Application.FileDialog(msoFileDialogFolderPicker) Set txt = Me![txtOutputDocsPath] strPath = GetOutputDocsPath()

With fd

.Title = "Browse for folder where attachments " _

& "should be stored" .ButtonName = "Select"

.InitialView = msoFileDialogViewDetails .InitialFileName = strPath

If .Show = -1 Then txt.Value = CStr(fd.SelectedItems.Item(1)) Else

Debug.Print "User pressed Cancel" End If End With

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

0 0

Post a comment