The Hermit Hermit's Free Library  Brain Teasers

One in a series of exercises that demonstrate how to accomplish real-world computing tasks using time- and work-saving techniques.

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