Solution to Brain Teaser #5: MS-Access Data Fix-Up

Solution

Using MS-Access

1. Back up the database
2. Open the table with the Telephone field in Access
3. Create an update query that eliminates the extra characters:
   Update To: mid(TEL,2,3)+mid(TEL,7,3)+mid(TEL,11,4)
4. Modify the size of the TEL field to 10 characters
5. Go to lunch

Using MS-Excel

1. Back up the database
2. In Access, export customer records as a comma-delimited file
3. Retrieve the file in MS-Excel
4. Insert a new column (e.g. column D) to the right of the telephone number column
5. Enter the following formula in cell D1:
   =mid(C1,2,3)+mid(C1,7,3)+mid(C1,11,4)
6. Copy the formula to the entire column
7. Use Paste Special to copy the formulas in column D into the same cells, but as values
8. Delete the old Telephone column
9. Save the workbook as a .CSV file
10. In Access, delete all records in the customer table
11. Import updated records from the comma-delimited file
12. Go to lunch

Using MS-Word

1. In Access, export customer records as a comma-delimited file
2. Retrieve the file in MS-Word (or better yet, a good text editor)
3. Replace all left parenthesis characters with nothing
4. Replace all combinations of a right parenthesis and space with nothing
5. Replace all dash characters with nothing
6. Save the file
7. In Access, create a table with the same structure as Customers
8. Back up the database
9. Delete the old table and substitute the new one
10. Go to lunch