Append or Update Query

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

Guest

I have a large table with several fields in it. Some of the fields have
values of 0 and numbers larger than zero. I need to write an update or
append (not sure which) that will read all of the values and convert any
digits >0 to a 1.

So, for example, it field1 has the following values before:
0
1
3
0
9
I need the query to update or produce:
0
1
1
0
1

Any ideas on how I can accomplish this?
 
You want an UPDATE query.

UPDATE changes existing data in records
APPEND add records to tables.

So an UPDATE that looks like:
UPDATE YourTable
SET YourField = 1
WHERE YourField > 0

IF you need to do this for multiple fields, you might be able to use the
following. If you have no null (blank) values then you can simplify the IIF
statement to IIF(YourField=0,0,1)

UPDATE YourTable
SET YourField = IIF(YourField Is Null,Null,IIF(YourField=0,0,1)),
AnotherField = IIF(AnotherField Is Null, Null,
IIF(AnotherField=0,0,1)

TEST on a copy of your data first to see if you get the desired results.
 
If you just want a SELECT query, you could try something like:

SELECT IIf([Field1]>0,1,[Field1]) AS Expr1
FROM MyTable;

For an UPDATE query it might look something like:

UPDATE MyTable
SET MyTable.Field1 = IIf([Field1]>0,1,[Field1]);

This will update all records in the table. If you need to update only
selected records, you can add a WHERE clause.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a large table with several fields in it. Some of the fields have
values of 0 and numbers larger than zero. I need to write an update or
append (not sure which) that will read all of the values and convert any
digits >0 to a 1.

So, for example, it field1 has the following values before:
0
1
3
0
9
I need the query to update or produce:
0
1
1
0
1

Any ideas on how I can accomplish this?
 
Back
Top