IF statements in SQL 1

[Excelerate2004]

Is it possible to execute the following If statements in Access?


IF cola > colc AND cola > colg AND cola > colt THEN
sig = "A"
ELSEIF colc > cola AND colc > colg AND colc > colt THEN
sig = "C"
ELSEIF colg > cola AND colg > colc AND colg > colt THEN
sig = "G"
ELSEIF colt > cola AND colt > colc AND colt > colg THEN
sig = "T"
END IF

Do I use UPDATE?

Thanks

 

[DotNetGnat]

take a look at

IIF()

-DNG

 

[Excelerate2004]

I tried this but no luck:

Select *
CASE WHEN cola > colc AND cola > colg AND cola > colt THEN
sig = "A"
ElseIF colc > cola AND colc > colg AND colc > colt THEN
sig = "C"
ElseIF colg > cola AND colg > colc AND colg > colt THEN
sig = "G"
ELSEIF colt > cola AND colt > colc AND colt > colg THEN
sig = "T"
End
from affydata;

I'll look at the IIF but I get stuck on nesting IF's so I'd rather use a switch of some type.

Thanks

 

[DotNetGnat]

Access does not support case statement...you need to work with IIF()

try it out and post back with your query and errors

-DNG

 

[lespaul]

SELECT 
iif(cola > colc AND cola > colg AND cola > colt, sig = "A", 
iif(colc > cola AND colc > colg AND colc > colt, sig = "C",
iif(colg > cola AND colg > colc AND colg > colt, sig = "G",
iif(colt > cola AND colt > colc AND colt > colg, sig = "T", ""))))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

My guess:
SELECT
IIf(cola > colc AND cola > colg AND cola > colt, "A",
IIf(colc > cola AND colc > colg AND colc > colt, "C",
IIf(colg > cola AND colg > colc AND colg > colt, "G",
IIf(colt > cola AND colt > colc AND colt > colg, "T", "?")))) AS sig

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

 

[Excelerate2004]

Cant get either les or PHV's code to work, it keeps asking me for the parameter values for columns cola, colg, colt, colc and sig, what am I missing here?

thanks

 

[PHV]

what am I missing here?
You have to know which fields from which table are concerned.

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

 

[lespaul]

you mean you want:

UPDATE tableName set sig = iif(cola > colc AND cola > colg AND cola > colt, "A", 
iif(colc > cola AND colc > colg AND colc > colt, "C",
iif(colg > cola AND colg > colc AND colg > colt, "G",
iif(colt > cola AND colt > colc AND colt > colg, "T", ""))))

assuming you have cola, colc, colg and colt as the FIELD NAMES in the TABLE and SIG is in the SAME table.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Excelerate2004]

I understand now, thanks to all who helped! Greatly appreciate it!