Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Unanswered: Change Data Types

    I am building a new DB from some Excel SpreadSheets I have imported. A few fields have "YES/NO type" data that I want to convert - only there are not formated correctly: some have "Y" and blanks, other fields have "Complete" and blanks - and they all imported in as TEXT data types. How can I convert these to YES/NO fields?

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Change Data Types

    Originally posted by NightZen
    I am building a new DB from some Excel SpreadSheets I have imported. A few fields have "YES/NO type" data that I want to convert - only there are not formated correctly: some have "Y" and blanks, other fields have "Complete" and blanks - and they all imported in as TEXT data types. How can I convert these to YES/NO fields?

    Thanks
    You need a translation tables.

    1) import the spreadsheet "as is" into Access.

    2) make a new table with two fields "Old" and "New." The "New" should be a boolean or Yes/No type.

    3) Place all of the spreadsheet possibilities into the "Old" column and the corresponding equivalent in the "New." Include in the "Old" a blank and null possibility. You can make one or multiple tables.

    To be really percise you may want to run Select Distinct queries on each of the fields in question to make sure that creative spelling options are in the mix.

    4) Set up an append or new table query. Join the convertable field with the "Old" field and with the "New" field in the results view. Query!

    Another percision method is to insert columns next to the field being converted. Instead of Appending, Update the new column in the same table. You can visually check for options not picked up and correct them easily. After updating delete the converted columns.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •