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 com-
parison with Outlook contacts can be modified for use anywhere you need to produce a single flat-
file table of data from linked Access tables.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 tblCompanyInfo
table is linked one-to-many with two tables: tblCompanyIDsPhones and tblContactInfo, 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.NOTENOTE
NOTENOTE
Part II Writing VBA Code to Exchange Data between Office Components