SQL Query: Using IF statement in defining new field

Question

I have a table with many fields and additionally several boolean fields (ex: BField1, BField2, BField3 etc.). I need to make a Select Query, which will select all fields except for boolean ones, and a new virtual field (ex: FirstTrueBool) whose value will equal to the name of the first TRUE Boolean Field.

For ex: Say I have BField1 = False, BField2 = True, BField3 = true, BField4=false, in that case SQL Query should set [FirstTrueBool] to "BField2". Is that possible?

Thank you in advance.

P.S. I use Microsoft Access (MDB) Database and Jet Engine.

Answer 1

You would be better using a single 'int' column as a bitset (provided you have up to 32 columns) to represent the columns.

e.g. see SQL Server: Updating Integer Status Columns (it's sql server, but the same technique applies equally well to MS Access)

Answer 2

If you want to keep the current architecture (mixed 'x' non-null status and 'y' non-status fields) you have (AFAIS now) only the option to use IIF:

Select MyNonStatusField1, /* other non-status fields here */
  IIF([BField1], "BField1", 
  IIF([BField2], "BField2", 
...
  IIF([BFieldLast], "BFieldLast", "#No Flag#")
  ))))) -- put as many parenthesis as it needs to close the imbricated IIFs
From
  MyTable

Of course you can add any Where clause you like.

---

EDIT: Alternatively you can use the following trick: Set the fields to null when the flag is false and put the order number (iow, "1" for BField1, "2" for BField2 etc.) when the flag is true. Be sure that the status fields are strings (ie. Varchar(2) or, better, Char(2) in SQL terminology)

Then you can use the COALESCE function in order to return the first non-value from the status fields which will be the index number as string. Then you can add in front of this string any text you like (for example "BField"). Then you will end with something like:

Select "BField" || Coalesce(BField1, BField2, BField3, BField4) /*etc. (add as many fields you like) */
From MyTable

Much clearer IMHO.

HTH