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.