Query to eliminate blank fields

[Mary M]

We have a DB that is holding info for different people in several different
fields, some of which are not populated. An example would be:

LastName FirstName Field1 Field2 Field3 Field4

Test1 Test1 1 3
Test2 Test2 2 3
Test3 Test3 1 2 4

How can I run a query that would return only the fields with values per
person? An example of the desired result would be:

Test1 Test1 1 3
Test2 Test2 2 3
Test3 Test3 1 2 4


Many thanks in advance for any assistance.

 

[Guest]

Try this ---
SELECT Mary_M.LastName, Mary_M.FirstName, IIf([Field1] Is Not
Null,[Field1],IIf([Field2] Is Not Null,[Field2],IIf([Field3] Is Not
Null,[Field3],IIf([Field4] Is Not Null,[Field4],Null)))) AS Expr1,
IIf([Field1] Is Not Null,IIf([Field2] Is Not Null,[Field2],IIf([Field3] Is
Not Null,[Field3],IIf([Field4] Is Not Null,[Field4],Null)))) AS Expr2,
IIf([Field1] Is Not Null,IIf([Field2] Is Not Null,IIf([Field3] Is Not
Null,[Field3],IIf([Field4] Is Not Null,[Field4],Null)))) AS Expr3,
IIf([Field1] Is Not Null,IIf([Field2] Is Not Null,IIf([Field3] Is Not
Null,IIf([Field4] Is Not Null,[Field4],Null)))) AS Expr4
FROM Mary_M;

 

[Mary M]

Thanks for the reply.

Expr1 woks great, however the other expressions work only if Field1 had a
value.

For example:

Will produce the following output:

Test1 Test1 1 3
Test2 Test2 2
Test3 Test3 1 2 4

Is there any way to tweak the SQL so that it accounts for this?

Again, many many thanks in advance.

 

[Guest]

This raw data ---
LastName FirstName Field1 Field2 Field3 Field4
Test1 Test1 1 3
Test2 Test2 2 3
Test3 Test3 1 2 4
gives me this output.
LastName FirstName Expr1 Expr2 Expr3 Expr4
Test1 Test1 1 3
Test2 Test2 2
Test3 Test3 1 2 4

 

[Mary M]

Thanks again.

On Test2 the 3 is missing.

Try your Query with a Blank in field1 and values in other fields, including
some blanks. The correct result generally works when there is a value in the
first field.

Any ideas?