Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

change blank values to NA

Discussion in 'Information Technology' started by Amity, Oct 27, 2005.

  1. Amity

    Amity
    Expand Collapse
    Guest

    I have a table with 89577 records and 36 columns. It is a table displaying
    measurements taken from many different sheep (all with an individual ID).
    The columns represent different traits for which measurements were taken eg
    fleece weight, body weight etc. My problem is not all traits were recorded
    for all animals leaving a whole lot of spaces throughout the table. To
    analyse this info the spaces need to be "NA" so the program can recognise the
    trait was not recorded. Is there an easy way to do this? Find and replace
    would be good however it doesn't recognise "blank" as a value it can find to
    replace it with NA. I am using Access 97.
     
  2. Loading...


  3. tina

    tina
    Expand Collapse
    Guest

    > To
    > analyse this info the spaces need to be "NA" so the program can recognise

    the
    > trait was not recorded.


    are you analyzing the data from within Access? if so, a query should
    recognize Null values as easily as it would NA values. at any rate, in
    answer to your question: one way to update Null values to NA, in the table,
    is to use an Update query. recommend you make a backup copy of the table
    first, so if you make a mistake that screws up the data, you can start over.

    hth


    "Amity" <Amity@discussions.microsoft.com> wrote in message
    news:B3B8DE88-6009-46EE-9127-95316313CC30@microsoft.com...
    > I have a table with 89577 records and 36 columns. It is a table displaying
    > measurements taken from many different sheep (all with an individual ID).
    > The columns represent different traits for which measurements were taken

    eg
    > fleece weight, body weight etc. My problem is not all traits were

    recorded
    > for all animals leaving a whole lot of spaces throughout the table. To
    > analyse this info the spaces need to be "NA" so the program can recognise

    the
    > trait was not recorded. Is there an easy way to do this? Find and

    replace
    > would be good however it doesn't recognise "blank" as a value it can find

    to
    > replace it with NA. I am using Access 97.
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    Hi Amity
    You can create a query that is based on the table, and use the NZ function
    to replace Null with NA

    Select [traits], Nz([traits],"NA") As Newtraits From TableName

    So, you don't need to update your table.
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Amity" wrote:

    > I have a table with 89577 records and 36 columns. It is a table displaying
    > measurements taken from many different sheep (all with an individual ID).
    > The columns represent different traits for which measurements were taken eg
    > fleece weight, body weight etc. My problem is not all traits were recorded
    > for all animals leaving a whole lot of spaces throughout the table. To
    > analyse this info the spaces need to be "NA" so the program can recognise the
    > trait was not recorded. Is there an easy way to do this? Find and replace
    > would be good however it doesn't recognise "blank" as a value it can find to
    > replace it with NA. I am using Access 97.
     
  5. Amity

    Amity
    Expand Collapse
    Guest

    I have found the answer.
    An update querie will work on a column by column basis by putting NA in the
    update to- and NOT "*" in the criteria. This means that all the blank or
    null values (ie anything that is not something "*") will be changed to NA and
    all the other values in that column I wish to keep will not be changed.
    Just to clarify-
    all columns contained some info I needed so I couldn't do a whole column
    general update as someone suggested
    also the data is not being analysed by Access but by a scientific analysis
    computer built for the purpose (not a microsoft program), and it does not
    recognise null values.

    Thanks for your help!

    "Amity" wrote:

    > I have a table with 89577 records and 36 columns. It is a table displaying
    > measurements taken from many different sheep (all with an individual ID).
    > The columns represent different traits for which measurements were taken eg
    > fleece weight, body weight etc. My problem is not all traits were recorded
    > for all animals leaving a whole lot of spaces throughout the table. To
    > analyse this info the spaces need to be "NA" so the program can recognise the
    > trait was not recorded. Is there an easy way to do this? Find and replace
    > would be good however it doesn't recognise "blank" as a value it can find to
    > replace it with NA. I am using Access 97.
     

Share This Page