Using Queries with Calculated Fields to Append Data from a Non-Normalized Table to a Normalized Table


The query that appends records from tblRawCustomers to tblCustomers needs several calculated fields, to match the structure of the normalized tblCustomers:

  • FirstName, MiddleName, and LastName fields that extract their data from the CustomerName field

  • A StreetAddress field that concatenates data from the MainStreetAddress and MainAddressStreet2 fields

I’ll modify some of the boilerplate calculated fields from queries in the Query Expressions database to do these tasks. (See Chapter 4, Sorting and Filtering Data with Queries, for more information on the Query Expressions database and the calculated fields in its queries.)

To start, select tblRawCustomers in the database window and select Query in the New Object selector on the toolbar, as shown in Figure 10.7.

click to expand
Figure 10.7

Select Design View in the New Query dialog, and select Append Query in the Query Type selector on the Query Design toolbar. Select tblCustomers in the Append dialog, as shown in Figure 10.8.

click to expand
Figure 10.8

Save the new append query as qappRawCustomers. Not all of the fields in tblRawCustomers are needed in this query—just the ones that correspond to fields in tblCustomers, with the exception of Customer ID. If the matching fields in the source table and target table have the same names (for example, CompanyName), Access will automatically select the target field in the Append To row for the tblRawCustomers field. If the field has a different name, you have to select it manually from the list of available fields in tblCustomers.

If the target table for an append query has an AutoNumber field, don’t append to that field unless the following conditions are met: (1) The fields in the source and target tables are of matching data types (you can use one of the conversion functions in a calculated field if necessary to convert the data type), and (2) the target table is empty. If you try to append to an AutoNumber field when the target table has data, you’ll probably get a key violation error. If you omit the AutoNumber field, Access will create the new AutoNumber automatically.

The source table (tblRawCustomers) has a CustomerName field that includes a middle initial. The target table only has two name fields: ContactFirstName and ContactLastName. We can either throw away the middle initial from the source table or add a ContactMiddleName field to the target table. I chose to add a MiddleName field to the target table.

The table below shows the status of each field in tblRawCustomers (the source table of the append query), and its matching field (if any) in tblCustomers (the target table).

tblRawCustomers Field

tblCustomers Field(s)

Comments

CustomerID

CustomerID

Leave out of query—this is an Auto-Number field, whose value will be created automatically.

CustomerName

FirstName

The CustomerName field in tblRaw-Customers is the source of three fields in tblCustomers, using calculated expressions.

MiddleName

LastName

CompanyName

CompanyName

JobTitle

ContactTitle

MainAddressStreet

BillingAddress

The two street address fields in tblRawCustomers are concatenated into the BillingAddress field in tblCustomers.

MainAddressStreet2

MainAddressCity

City

MainAddressState

StateOrProvince

MainAddressPostalCode

PostalCode

ShippingAddressStreet

Data in these fields will be appended to tblShippingAddresses.

ShippingAddressStreet2

ShippingAddressCity

ShippingAddressState

ShippingAddressPostalCode

ShippingAddressCountry

Shipping2AddressStreet

Shipping2AddressStreet2

Shipping2AddressCity

Shipping2AddressState

Shipping2AddressPostalCode

Shipping2AddressCountry

Shipping2AddressCountry

Fax

Data in these fields will be appended to tblCustomerPhones.

Phone1

Phone2

CallbackPhone

CarPhone

CellPhone

Pager

Email1

Data in these fields will be appended to tblCustomerEMails.

Email2

Email3

To use the boilerplate query expressions for splitting and concatenating name and address data, start by importing the qrySplitNameComponents and qryConcatenateAddressComponents queries from the Query Expressions database into the current database. The qrySplitNameComponents append query contains expressions for splitting name fields into their components. There are two versions of the expressions for splitting names in this query—one set of fields to split a last name first name field (indicated by an “L” suffix), and another set of fields to split a first name first name field (indicated by an “F” suffix). The CustomerName field in tblRawCustomers has a first name first CustomerName field, so the FirstNameF, MiddleNamePlusF, MiddleNameF, and LastNameF fields are the ones to use. Copy these fields from qrySplitNameComponents to qappRawCustomers.

Each of these copied fields needs to be edited to replace the original field names with the appropriate field names from the source table. You can edit directly in the Zoom window (opened from the field by pressing Shift-F2). I recommend changing the font to a larger size by clicking the Font button and selecting 10 pt (or larger). Figure 10.9 shows the FirstNameF field with the original FirstNameFirst field being replaced by CustomerName.

click to expand
Figure 10.9

Replace FirstNameFirst with CustomerName in the FirstNameF and MiddleNamePlusF fields. The MiddleNameF and LastNameF fields both reference the intermediate field MiddleNamePlusF. This technique works fine in a select query (and it makes the calculated expressions far more comprehensible), but for an append query you need to replace the calculated field MiddleNamePlusF with the actual calculated expression (because each field in an append query must have a target field). This modification is difficult to do in the Zoom window, so I prefer to open a blank Word or Notepad document, paste the query expression into it, and use Search and Replace to replace [MiddleNamePlusF] (don’t forget the brackets!) with the expression for the MiddleNamePlusF calculated field, as shown in Figure 10.10. After replacing all the occurrences of MiddleNamePlusF, paste the modified expression back into the query.

click to expand
Figure 10.10

After doing this, the MiddleNamePlusF field can be deleted from the append query. The modified append query calculated name fields are listed below:

FirstNameF: IIf(InStr([CustomerName],Chr$(32))>0,Mid([CustomerName],1,InStr([CustomerName], Chr$(32))-1),[CustomerName]) MiddleNameF: IIf(InStr(IIf(InStr([CustomerName],Chr$(32))>0,Mid([CustomerName], InStr([CustomerName],Chr$(32))+1),””),Chr$(32))>0,Mid(IIf(InStr([CustomerName], Chr$(32))>0,Mid([CustomerName],InStr([CustomerName],Chr$(32))+1),””),1, InStr(IIf(InStr([CustomerName],Chr$(32))>0,Mid([CustomerName], InStr([CustomerName],Chr$(32))+1),””),Chr$(32))-1),””) LastNameF: IIf(InStr(IIf(InStr([CustomerName],Chr$(32))>0,Mid([CustomerName], InStr([CustomerName],Chr$(32))+1),””),Chr$(32))>0,Mid(IIf(InStr([CustomerName], Chr$(32))>0,Mid([CustomerName],InStr([CustomerName],Chr$(32))+1),””), InStr(IIf(InStr([CustomerName],Chr$(32))>0,Mid([CustomerName], InStr([CustomerName],Chr$(32))+1),””),Chr$(32))+1),IIf(InStr([CustomerName], Chr$(32))>0,Mid([CustomerName],InStr([CustomerName],Chr$(32))+1),””))

The next task is to concatenate the data from the two main street address fields into the Address field in the target table. Start by copying the Address field from qryConcatenateAddressComponents into qappRawCustomers. This field concatenates data from three separate street address fields, and there are only two address fields in the source table, so the final & IIf(Nz([Address3])<>””,Chr(13) & Chr(10) & [Address3]) portion of the expression can be deleted. The Address1 field needs to be replaced with MainAddressStreet, and the Address2 field with MainAddressStreet2. The modified expression is:

Address: IIf(Nz([MainAddressStreet2])<>””,[MainAddressStreet]  & Chr(13) & Chr(10) & [MainAddressStreet2],[MainAddressStreet]) 

Important

Chr(13) & Chr(10) puts a CR + LF (carriage return plus linefeed) into a query expression. In VBAcode, you can use the vbCrLf named constant instead.

The append query is now finished. It is shown in Design view in Figure 10.11.

click to expand
Figure 10.11

Now you can run the append query. Often, there will be a problem with appending some of the records. When I ran qappRawCustomers, I got the error message shown in Figure 10.12.

click to expand
Figure 10.12

The error message shows that 106 records have validation rule problems. However, an examination of tblCustomers reveals that none of its fields have validation rules (it’s not at all uncommon for append query error messages to be misleading). If you run the query, the 106 problem records will be discarded, and there is no easy way to append those 106 records later, after fixing the problem.

When I get an error running an append query, I cancel the update and make a copy of the target table with a dash prefix and the suffix “BeforeAppend” (in this case, the copy is -tblCustomersBeforeAppend). The copy preserves the data in the target table before appending, for use after resolving the issue that caused the problem while appending. After fixing the problem, I’ll delete tblCustomers and copy tblCustomersBeforeAppend to tblCustomers, before running the append query.

After running the append query, skipping the records that can’t be appended, tblCustomers now has most of the records from tblRawCustomers—excluding the 106 problem records. To identify the 106 problem records, I use one of the selections in the New Query dialog. Create a new query, selecting the Find Unmatched Query Wizard in the New Query dialog, as shown in Figure 10.13.

click to expand
Figure 10.13

Select tblRawCustomers on the first screen of the wizard, and tblCustomers on the second screen. They can’t be linked by CustomerID, so I’ll use CompanyName, as shown in Figure 10.14.

click to expand
Figure 10.14

This won’t link the two tables with perfect precision, but it’s close enough to diagnose the problem. On the next screen of the wizard, select the first few fields (CustomerID, CustomerName, JobTitle, and CompanyName) for the query, and save it as qryUnmatchedCustomers. Figure 10.15 shows this query and some of the appended records in tblCustomers in Datasheet view.

click to expand
Figure 10.15

One thing stands out: all the records in qryUnmatchedCustomers have no data in the JobTitle field, while the appended records in tblCustomers all have data in the corresponding ContactTitle field, so it looks like this field is the one that caused the problem. Looking at the ContactTitle field in tblCustomers in Design view, the problem is clear: its AllowZeroLength property is set to No, so the records with no job title could not be appended.

From time to time, I like to make copies of database objects I am working on, in case a modification doesn’t work out and I need to return to a previous version. To make a copy of an object quickly, I use a macro in the AutoKeys macro group (one of the few uses left for Access macros). This macro (hot key Ctrl-D) makes a copy of a database object with a dash (Access 2000 and 2002) or underscore (Access 2003) in front of its name (so that all the copies sort to the top of the database window, for easy clean-up). The macro has a SendKeys statement with the following keystrokes: %fa{home}+^{right 2}-{end}. To use this macro, after adding it to your AutoKeys macro group, select a database object, and press Ctrl-D. The Save As dialog pops up, prefilled with the object name preceded by a dash. You can accept that name, or (if you want to save multiple backup copies), add a digit at the end of the name. Figure 10.16 shows the Save As dialog when making a second backup copy of tblCustomers.

click to expand
Figure 10.16

Now tblCustomers (with the missing records) can be deleted, and the backup copy made before doing the append (-tblCustomersBeforeAppend) can be copied to tblCustomers. Open tblCustomers in Design view, change AllowZeroLength to Yes for the ContactTitle field, check that any other field that could legitimately be blank also has this property set to Yes, and save the table. Generally speaking, only a few fields in a table—if any—should have the AllowZeroLength property set to Yes. If a record isn’t valid unless it has a value in a field (say, a Social Security number for an employee), set the AllowZeroLength property to Yes; otherwise, it should be No. Try running qappRawCustomers again—this time all the records are appended.

Deleting the original tblCustomers and recreating it from the backup table deleted the links between tblCustomers and its linked tables, so open the Relationships diagram and redo the links. tblCustomer needs to be linked one-to-many to tblCustomerPhones, tblCustomerEMails, tblShippingAddresses, and tblOrders.




Expert One-on-One(c) Microsoft Access Application Development
Expert One-on-One Microsoft Access Application Development
ISBN: 0764559044
EAN: 2147483647
Year: 2006
Pages: 124
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net