Assigning values to a new column

[wdbouk]

Hi,

I have a column with only two text letters Q and M. I want to create a new column where for the values Q I want to assign number 1 in the new column and for M I want to assign number 2.
In Excel it is easy (=if a1="Q",1,2) asuming the cell is a1 in the column containing Q and M values. Is there a way to do so in Access. I have 900,000 rows and I can't do it in excel.
Best
W

 

[PHV]

Have a look at the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

This is the SQL view of a query that will create a new two column table from a table (tblTable) with one column containing Q or M:
[tt]SELECT tblTable.QorM, IIf([QorM]="Q",1,2) AS 1or2 INTO tblNewTable
FROM tblTable;[/tt]

(The relevant bit is IIf([QorM]="Q",1,2) AS 1or2, if you want to create some other type of query.)

 

[NthDegree]

Open a query in SQL view mode and enter the following:

UPDATE Table1 SET Table1.numbercolumn = IIf([textcolumn]="Q",1,2);

then just run it.