To: Richard Feemster, Wink Incorporated
From: Tom Marcellus, Marble Publications, Inc.
Date: April 17, 2001
Re: Data Conversion Report

This document summarizes what we found and how we handled the conversion of your 11 Q&A databases to Microsoft Access 2000 tables. All 11 tables in the Access 2000 database provided by us (in the file WINK.MDB) are named the same as the corresponding Q&A database filenames, as are the default forms (which you can further customize if you like) that we've provided at no additional charge.

Data Types
In several of the Date, Number and Yes/No type fields in the Q&A databases that contain these types of fields, we found numerous data entry violations that rendered such fields convertible as text fields only.

These "violations" are due to users having entered, for example, "1999" or "Pending" (not valid dates) in a Date type field, "X" in a Yes/No type field and/or "-" (a minus sign by itself) in a Number type field. This is not uncommon in Q&A databases because, in database parlance, Q&A is "loosely typed." That is, Q&A warns of such data entry violations when the user enters the data, but allows them to continue and leave the "illegal" entry in the field. Such data entry errors can be prevented by designing an appropriate Restriction Spec and/or Programming Spec for the database, but no such additional illegal value "trapping" features were evident in the Q&A databases you provided.

Because we cannot presume to know what data should be in such "problem" fields, we extracted them as text fields - as is our usual practice in these cases - then ported them to Access as text fields. It will be up to your people to peruse the Access tables to locate and correct such data entry errors. This is not difficult. For example, scrolling down a table column that contains a valid date in most of the records, one can easily spot entries that are not valid dates. If these are corrected (by entering a valid date or deleting the invalid entry), you can then change the field type (at the Table Design screen) to Date/Time, save the modified design, and Access will now treat the field as a valid Date/Time type field.

Yes/No fields, even when they might contain valid entries (Y, N, T, F, etc.) in all the Q&A records, were exported as text fields because Access turns these into checkbox "fields" which in our experience are difficult for users coming from Q&A to get used to. If you want such fields converted to true Access Yes/No checkbox fields, simply go to the Table Design screen in Access and change the field type from Text to Yes/No. If any invalid entries are found for this data type, Access will display a warning screen when you attempt to save the modified table. Or, you can leave the field as a text field, and simply restrict entries to "Y" or "N."

If you have any questions as to the data type of a field in a Q&A database, you can open the database's Format Spec in Q&A (File / Design / Customize / Format values). The code in each field (T=text, D=date, M=money, Y=Yes/No, etc.) will tell you what the Q&A database designer intended the field type to be.

Y2K-related Issues
As part of our Y2K compliance check, we found in two of the databases (CLOSEOUT and CLOSE2) a total of 1,606 instances where a Date type field contained a "1900" or "1901" date. This is due to Q&A not being Y2K-compliant and occurs when a user enters a date with an abbreviated two-digit year ("4/17/01," for example). Because the date display format the database designer selected for the database (at the Global Format Options screen) displays only the two-digit year on the data entry screen, users assume that Q&A has interpreted the year "01" as "2001" when they have just typed "4/17/01" into the field. But this is NOT the case: Q&A has actually stored the date with year as "1901." This is why it's essential in Q&A to choose a date display format that shows the full four-digit year and to ensure that users type the full four-digit year when entering dates. (You don't have this problem in Access 2000 since it is Y2K compliant.)

Using our Y2K utilities, we found and corrected (added 100 years to) all such erroneous dates before porting the data to Access.

Data Corruption
We performed our standard data integrity tests on all the databases and found no damage or corruption in any of the files.

Field Names
In virtually all the Q&A databases, we found field names or field labels that, while legal in Q&A, are illegal or risky in Access. In such cases, for the Access tables, we endeavored to assign legal field names that were as close to their corresponding Q&A field names as possible. We could not presume to know, what, for example, a Q&A field name with a (5) suffix such as JOB NO.(5) might signify, so we retained such suffixes in the corresponding Access field names. In cases where there were duplicate Q&A fields in the same database, such as multiple COMPLETION(2) fields, we named them Completion(2) and Completion2(2) in the Access table.

Data Extraction, Order of
We extracted the data from the Q&A databases in the order that the fields occur in the Q&A database form. Viewing the Q&A form, this starts with the first field at the top left corner of page 1, proceeding across that line, then moving to the first field on the second line, and so forth down to the end of the form. The extracted data was ported to the Access table in the same field order.

Grids in Database Design
Areas of several of the Q&A database forms were laid out in a grid-like format with a descriptive text label at the top of each grid column, while other fields not related to the grid fields were placed to the right of this grid and on the same lines. (See PCOMTEMP.DTF, form page 2, for example.) This is poor database design. Nevertheless, we stuck to the rule and extracted the fields in the order in which they occur in the Q&A database forms.

When designing any custom forms in Access, you would be wise to first open the pertinent Q&A database in Search/Update mode and print any record with Print Field Labels set to Yes and Print Expanded Fields set to No at the Print Options screen. This will give your form designer a picture of the Q&A form layout, reveal any grid-like areas in the form, and allow him or her to re-create a similar grid-like layout in the new Access form.

Sort Order
For all databases we extracted the Q&A data in ascending sort order based on the first field in the database as no other fields appeared to be obvious sorting candidates. This is why, when you look at the Access table, the first field (after the Auto-Number "Rec" field) in the initial group of records may be empty. (In the corresponding Q&A records, this first field contained no value.) In an ascending sort, blank fields are lower in the sort order than fields containing anything, so they appear first in the table.

In all cases, the Q&A data was ported to the new Access tables in the same order that it was extracted from the Q&A databases.

Blank fields
Virtually all the Q&A databases contained many blank or empty fields in many of the records. All such fields are also empty in the corresponding Access tables.

Manual Data Formatting
Some of the Q&A databases contained multi-line fields with manually-formatted data. For example, in the PROPOSAL database, there is a multi-line Address field with entries hand-formatted like the following example:

This is done by the database designer in an effort to have the data display in a compact form for readability, but it represents poor database design. Such a field should actually be five fields (Company, Address, City, State, Zip). Fields like these cause data conversion issues because of the hard-carriage return manually entered at the end of each line during data entry, which Access does not match. Thus, in the Access tables for such fields, you will typically find two spaces for each such hard carriage return.

Another type of manual data formatting was found in some Q&A database fields containing lengthy entries (more than 250 characters). (See the "Folder" field in FILEBOX, for an example of this.) In these cases, the Q&A database designer created a text label for each of two columns, then placed a single screen-wide multi-line field below them. The person performing the data entry is supposed to type the first value under the first column heading, press the spacebar enough times to position the second value under the second column heading, then press Enter to start a new line with the next two values. This effort to force "alignment" of the data is poor database design because (1) it is rarely followed (or impossible to follow) by data entry people, (2) results in what should be separate data values being stored in the same field, and (3) makes the data problematic for use in a program other than Q&A. These fields were converted to Memo type fields in the Access tables.

Auto-Number Field
As instructed, an Auto-Number field was added (as the first field) to each Access table after the data was ported to it. This field is named Rec.