compare 2 columns using an SQL query 1

[Excelerate2004]

Hello everybody, I'm having a difficult time trying to write an Access 2000 SQL query:

How can I write a query using SQL to assign a number in a marker column if between 2 columns labelled original and duplicate I wish to have the following criteria:

original = duplicate [assign a 1 in a marker column]
orignal <> duplicate [assign a 2 in a marker column]
no value in original column, value in duplicate [assign a 3]value in original column, no value in duplicate [assign a 4]

Is this as complicated as it seems? Is it possible to do this all within one script??

Thanks for any help I can get

 

[PHV]

In query grid:
marker: IIf(original & "" = "" And duplicate & "" = "", 0, IIf(original & "" = "", 3, IIf(duplicate & "" = "", 4, IIf(original = duplicate, 1, 2))))

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

 

[Excelerate2004]

Can I use your posted suggestion script in a regular query in MS Access 2000? i.e. SQL view?

I was thinking of something along the lines of this, but I cant get it to work

UPDATE Master
SET marker = 0 WHERE original<>dup OR
SET marker = 1 WHERE original=dup OR
SET marker = 2 WHERE original = " " OR
SET marker = 3 WHERE dup = " ";

Thanks

 

[PHV]

Something like this ?
UPDATE Master SET marker =
IIf(original & "" = "" And duplicate & "" = "", 0,
IIf(original & "" = "", 3,
IIf(duplicate & "" = "", 4,
IIf(original = duplicate, 1, 2))))
;

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

 

[Excelerate2004]

Hi PHV,

Your solution is perfect! Thanks very much.

Just one question on some syntax:

What is the I before the IF statement purpose?

Thanks again

Cheers!

 

[Excelerate2004]

I just thought of something else...is it possible to automate the script above so that it could do the exact same procedure but for 35 different columns x 3 (for each original, duplicate and marker columns)?

Meaning I have 35 original columns and with each of these there is 35 duplicate columns along with 35 marker columns.

i.e. the above procedure was for set 1 of 35

So really it should have been:

UPDATE Master SET 990524marker =
IIf(990524 & "" = "" And 990524dup & "" = "", 0,
IIf(990524 & "" = "", 3,
IIf(990524dup & "" = "", 4,
IIf(990524 = 990524dup, 1, 2))))

Is this possible? The column header names for all 35 have no set pattern theyr'e just arbitrary numbers.

Thanks for the help