Denormalizing Data

Sometimes you may need to do the opposite of the last section’s tasks—export data from linked, normalized tables to a non-normalized table—generally because such a table structure is required for compatibility with a flat-file mainframe database, or to meet government requirements (which are often based on outdated database technology). The task of extracting data from separate linked (normalized) tables, and appending it to a single flat-file table, is sometimes called denormalizing because you are reversing the usual process of separating data into linked, normalized tables. As an example of such a table, I made a structure-only copy of tblRawCustomers, by copying the table to the clipboard, then pasting it with the Structure Only option selected in the Paste Table As dialog, as shown in Figure 10.26.

Figure 10.26

The target table (tblCustomersFlatFile) has three sets of fields of the same type (phones, emails, and shipping addresses). To fill these fields with data from tblCustomers (the “one” table in the relationships), and the three linked “many” tables, three procedures are needed to pick up data from tblCustomers and the three “many” tables. The first procedure uses a query to fill the target table (tblCustomersFlatFiles) with records, one record per CustomerID, and also to fill the separate phone fields with data from the linked tblCustomerPhones. The two other procedures work with the two linked tables, updating the records in tblCustomersFlatFiles with email and shipping address data from the linked tables. I use a query to work with the shipping address data, because calculated fields are needed to split the street address into two fields. The appending or updating has to be done in code, because the query and the linked tables have multiple records per CustomerID, so data from different records in the query or linked tables need to be written to the same record in the target table.

The query (qryCustomersAndPhones) includes two tables, tblCustomers and tblCustomerPhones. To ensure that the query includes all the Customer records, whether or not they have linked records in tblCustomerPhones, select the middle (LEFT JOIN) selection in the Join Type dialog for the join between the table. This query needs a few calculated fields: CustomerName (to concatenate data from the separate Contact Name fields in tblCustomers) and a set of Address1 and Address2 fields to separate data in the Billing Address field in tblCustomers. These fields are created based on the boilerplate fields in two queries from the Query Expressions sample database: qryConcatenateNameComponents and qrySplitAddressComponents, as in the “Using Queries with Calculated Fields to Append Data from a Non-Normalized Table to a Normalized Table” section earlier in this chapter, with one exception: the AddressPlus intermediate fields can be used to simplify the query field expressions, since this is a select query, not an append query. The query expressions for the concatenated name field and the split billing address fields are:

FirstNameFirst: IIf([ContactMiddleName],[ContactFirstName] & “ “ & [ContactMiddleName],[ContactFirstName]) & IIf([ContactLastName],” “ & [ContactLastName],[ContactLastName]) AddressPlus: IIf(InStr([BillingAddress],Chr(10)),Mid([BillingAddress],InStr([BillingAddress], Chr(10))+1)) Address1: IIf(InStr([BillingAddress],Chr(13)),Left([BillingAddress],InStr([BillingAddress], Chr(13))-1),[BillingAddress]) Address2: IIf(InStr([AddressPlus],Chr(13)),Left([AddressPlus], InStr([AddressPlus],Chr(13))-1),[AddressPlus])

The first query (qryCustomersAndPhones) is shown in Design view in Figure 10.27.

Figure 10.27

Figure 10.28 shows the same query in Datasheet view, with just the CustomerID, CompanyName, PhoneNumber, and PhoneDescription fields showing. The number of records per CustomerID is equal to the number of phones for that customer in the linked tblCustomerPhones table.

Figure 10.28

The procedure that creates new records in tblCustomersFlatFile uses two recordsets, one based on qryCustomersAndPhones (the source data) and one based on tblCustomersFlatFile (the target table). The code loops through rstCustomers, starting out by checking whether the CustomerID value is the same as in the previous record (the lngOldCustomerID variable is set to 0 before processing the first record). If the CustomerID is different, a new record is added to the flat file table, and its name and address fields are filled in from tblCustomers. Then, for all records (whether the CustomerID is the same or different), the value of the PhoneDescription field is checked, and the phone number is saved to the appropriate field in tblCustomersFlatFile. After updating the record, the code moves to the last record and edits it again in case there are more records for the same CustomerID.

 Public Sub AppendCustomersAndPhones() On Error GoTo ErrorHandler        Dim rstCustomers As DAO.Recordset    Dim rstFlatFile As DAO.Recordset    Dim lngCustomerID As Long    Dim lngOldCustomerID As Long    Dim strPhoneDesc As String        lngOldCustomerID = 0    Set dbs = CurrentDb    Set rstCustomers = dbs.OpenRecordset("qryCustomersAndPhones")    Set rstFlatFile = dbs.OpenRecordset("tblCustomersFlatFile")    Do While Not rstCustomers.EOF       lngCustomerID = rstCustomers![CustomerID]       Debug.Print "Customer ID: " & lngCustomerID       Debug.Print "Old Customer ID: " & lngOldCustomerID       ‘Check whether this record has a new CustomerID.       If lngCustomerID <> lngOldCustomerID Then          ‘New record has different CustomerID;          ‘add a new record to target table and fill the name and          ‘address fields          rstFlatFile.AddNew          rstFlatFile![CustomerID] = lngCustomerID          rstFlatFile![CompanyName] = rstCustomers![CompanyName]          rstFlatFile![CustomerName] = rstCustomers![FirstNameFirst]          rstFlatFile![JobTitle] = rstCustomers![ContactTitle]          rstFlatFile![MainAddressStreet] = rstCustomers![Address1]          rstFlatFile![MainAddressStreet2] = rstCustomers![Address2]          rstFlatFile![MainAddressCity] = rstCustomers![City]          rstFlatFile![MainAddressState] = rstCustomers![StateOrProvince]          rstFlatFile![MainAddressPostalCode] = rstCustomers![PostalCode]       End If              ‘Determine type of phone number from its description, and store       ‘it in the appropriate field.       ‘Phone numbers need to be updated for all records in the source query.       strPhoneDesc = Nz(rstCustomers![PhoneDescription])       Debug.Print "Phone description: " & strPhoneDesc       Select Case strPhoneDesc                 Case "Phone 1"             rstFlatFile![Phone1] = rstCustomers![PhoneNumber]                 Case "Phone 2"             rstFlatFile![Phone2] = rstCustomers![PhoneNumber]                       Case "Fax"             rstFlatFile![Fax] = rstCustomers![PhoneNumber]                 Case "Callback Phone"             rstFlatFile![CallbackPhone] = rstCustomers![PhoneNumber]                 Case "Car Phone"             rstFlatFile![CarPhone] = rstCustomers![PhoneNumber]                 Case "Cell Phone"             rstFlatFile![CellPhone] = rstCustomers![PhoneNumber]                 Case "Pager"             rstFlatFile![Pager] = rstCustomers![PhoneNumber]                 Case Else             ‘Other phones can’t be stored in the target table, so             ‘they won’t be processed.       End Select              lngOldCustomerID = lngCustomerID       rstFlatFile.Update       rstFlatFile.MoveLast       rstFlatFile.Edit       rstCustomers.MoveNext    Loop     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

After running this procedure, there are 569 records in tblCustomersFlatFile (the same number of records as tblCustomers), with their phone number fields filled in.

The next procedure updates the records in tblCustomersFlatFile with email addresses. It cycles through a recordset based on tblCustomerEMails, and for each record it searches for the matching CustomerID record in tblCustomersFlatFile. An intCount variable is set to 1 every time a new CustomerID occurs in tblCustomerEMails, and incremented by 1 with each successive record that has the same CustomerID. This variable is used to create a field name (“Email” & CStr(intCount)), and the email address from the current record in tblCustomerEMails is written to the EMail1, EMail2, or EMail3 field in tblCustomersFlatFile, depending on whether it is the first, second, or third email address for that customer in tblCustomerEMails (if there are more than three email addresses, the extra ones are not written to the flat-file table). The UpdateEMails procedure is listed below:

 Public Sub UpdateEMails() On Error GoTo ErrorHandler        Dim rstEMails As DAO.Recordset    Dim rstFlatFile As DAO.Recordset    Dim strCustomerID As String    Dim strOldCustomerID As String    Dim strEMail As String    Dim strSearch As String    Dim intCount As Integer    Dim strFieldName As String        strOldCustomerID = ""    Set dbs = CurrentDb    Set rstEMails = dbs.OpenRecordset("tblCustomerEMails", dbOpenDynaset)    Set rstFlatFile = dbs.OpenRecordset("tblCustomersFlatFile", dbOpenDynaset)    Do While Not rstEMails.EOF       strCustomerID = rstEMails![CustomerID]       strSearch = "[CustomerID] = " & Chr$(39) & strCustomerID           & Chr$(39)       Debug.Print "Search string: " & strSearch       Debug.Print "Customer ID: " & strCustomerID       Debug.Print "Old Customer ID: " & strOldCustomerID       ‘Check whether this record has a new CustomerID       If strCustomerID <> strOldCustomerID Then          ‘New record has different CustomerID;          ‘Set email count back to one          intCount = 1       Else          ‘Increment email count by one          intCount = intCount + 1       End If              ‘Store the email address in the appropriate field of the       ‘flat-file table (Email1, Email2, or EMail3; if there are       ‘more than three emails, only the first three will be stored).       If intCount < 4 Then          rstFlatFile.FindFirst strSearch          strEMail = Nz(rstEMails![CustomerEMail])          Debug.Print "Email address: " & strEMail          rstFlatFile.Edit          strFieldName = "Email" & CStr(intCount)          Debug.Print "Field name: " & strFieldName          rstFlatFile(strFieldName) = strEMail          rstFlatFile.Update          rstFlatFile.FindFirst strSearch          rstFlatFile.Edit       End If                 strOldCustomerID = strCustomerID       rstEMails.MoveNext    Loop     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

The procedure for adding shipping addresses to tblCustomersFlatFile is similar, except that it uses a select query based on tblShippingAddresses, with calculated fields to separate out the street address field into two fields (similar to the fields in qryCustomerNamesAndPhones). This procedure is:

 Public Sub UpdateShippingAddresses() On Error GoTo ErrorHandler        Dim rstShipping As DAO.Recordset    Dim rstFlatFile As DAO.Recordset    Dim strCustomerID As String    Dim strOldCustomerID As String    Dim strSearch As String    Dim intCount As Integer        strOldCustomerID = ""    Set dbs = CurrentDb    Set rstShipping = dbs.OpenRecordset("qryShippingAddresses", dbOpenDynaset)    Set rstFlatFile = dbs.OpenRecordset("tblCustomersFlatFile", dbOpenDynaset)    Do While Not rstShipping.EOF       strCustomerID = rstShipping![CustomerID]       strSearch = "[CustomerID] = " & Chr$(39) & strCustomerID           & Chr$(39)       Debug.Print "Search string: " & strSearch       Debug.Print "Customer ID: " & strCustomerID       Debug.Print "Old Customer ID: " & strOldCustomerID       ‘Check whether this record has a new CustomerID.       If strCustomerID <> strOldCustomerID Then          ‘New record has different CustomerID;          ‘Set email count back to one          intCount = 1       Else          ‘Increment address count by one          intCount = intCount + 1       End If              ‘Store the shipping address info in the appropriate set of       ‘fields in the flat-file table; if there are more than three       ‘shipping addresses, only the first two will be stored.       rstFlatFile.FindFirst strSearch       rstFlatFile.Edit       If intCount = 1 Then          rstFlatFile![ShippingAddressStreet] =              Nz(rstShipping![ShippingAddress1])          rstFlatFile![ShippingAddressStreet2] =              Nz(rstShipping![ShippingAddress2])          rstFlatFile![ShippingAddressCity] =              Nz(rstShipping![ShipCity])          rstFlatFile![ShippingAddressState] =              Nz(rstShipping![ShipStateOrProvince])          rstFlatFile![ShippingAddressPostalCode] =              Nz(rstShipping![ShipPostalCode])          rstFlatFile![ShippingAddressCountry] = "USA"       ElseIf intCount = 2 Then          rstFlatFile![Shipping2AddressStreet] =              Nz(rstShipping![ShippingAddress1])          rstFlatFile![Shipping2AddressStreet2] =              Nz(rstShipping![ShippingAddress2])          rstFlatFile![Shipping2AddressCity] =              Nz(rstShipping![ShipCity])          rstFlatFile![Shipping2AddressState] =              Nz(rstShipping![ShipStateOrProvince])          rstFlatFile![Shipping2AddressPostalCode] =              Nz(rstShipping![ShipPostalCode])          rstFlatFile![Shipping2AddressCountry] = "USA"       End If       rstFlatFile.Update       rstFlatFile.FindFirst strSearch       rstFlatFile.Edit                 strOldCustomerID = strCustomerID       rstShipping.MoveNext    Loop     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

If you need to run the code that fills tblCustomersFlatFile with denormalized data on a regular basis (say, for a monthly export to a mainframe database), you can convert the Sub procedures to functions, so that they can be run from a macro with RunCode actions. You’ll also need to make a structure-only copy of tblCustomersFlatFile with the zz prefix (indicating a table for copying), so you can make a fresh copy of tblCustomersFlatFile by copying from zstblCustomersFlatFile. The macro’s actions are:

DeleteObject, Table, tblCustomersFlatFile CopyObject, tblCustomersFlatFile, Table, zztblCustomersFlatFile RunCode, AppendCustomersAndPhones RunCode, UpdateEMails RunCode, UpdateShippingAddresses

The mcrCreateFlatFileTable macro can be run every time you want to create the flat-file table with fresh data.