need help with query 1

[JennW]

I am trying to create a query that will consolidate fields of information into one alias field.

For example:

The alias field should be comprised of information that appears first in this order of fields.

If there is data in DPS1, use that data..otherwise
use the data in DPS2....otherwise
use the data in DPS3....otherwise
use the data in DPS4....otherwise
use the data in DPS5.

My intent is for all of these records to come together and there be just one field that represents whichever DPS had the latest data.

Can this be done in a simple select query or is there some other way I should be looking?

Thank you,

JennW

 

[biggoober]

you could do this

query1
select dps1 as dps from table where not isnull(dps1);

query2
select dps2 as dps from table where isnull(dps1) and not isnull(dps2);

query3
select dps3 as dps from table where isnull(dps1) and isnull(dps2) and not isnull(dps3);

Union query
select dps from query1
union
select dps from query2
union
select dps from query3;

You get the idea