How to conditionally update multiple columns

[MW0CBC]

I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.

For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.

To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.

It was simple enough to do this one column at a time...

UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"

WHERE ((([Capture history intermediate].[1971]) Is Null))

But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.

So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately? It's probably obvious to folks with more than the couple of months experience I have with SQL!!

Cheers

Denis

 

[Brianwarnock]

I would scrap the criteria and use an iif clause

Iif(field is null, "0" ,field) for the update then 1 query can accommodate all columns.

Don't forget to take a copy before running the mass update incase of problems

Brian

 

[MW0CBC]

Hi Folks,

Thanks for the Iif suggestion. That worked a treat

On the schema issue, I should perhaps explain that my master data is stored differently and this data extract is in a different format, that I arrived at using a pivot table. I won't be adding any more columns (years) - I'm analysing a historical data set up to 2012. I won't be doing any work beyond that (I hope!!)

Thanks for all the help and advice. Much appreaicted.

Cheers
Denis