change blank values to NA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
To
analyse this info the spaces need to be "NA" so the program can recognise the
trait was not recorded.
Click to expand...

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
 
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.
 
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!
 
Back
Top