Aceess Query - If field is null dont output?

Question

I have an access query (2003):

SELECT [User] [100], [101], [102], [103], [104], [105], [106], [107], [108], [109], [110]
FROM [Access_Count>1] 

The results I get look like this:

[User], [100], [101], [102], [103], [104], [105], [106], [107], [108], [109], [110]
UserA,100,101,,,,,,,,,
UserB,,,,,,,,109,110

Is there any way I can exclude the blank columns/fields from the output? So my results would be like this:

[User], [100], [101], [109], [110]
UserA,100,101,,
UserB,,,109,110

I have had a massive hunt today through google, have found a similar question asked elsewhere but never solved.

Sample Data: http://db.tt/rM2JUvNR

Cheers,

Michael

Answer 1

Does the output have to be a table? Can the values be concatenated into a string?

If so, you can do something like the following:

select t.[user],
       mid((iif(Keep100 = 'Y', ','&[100])&
            iif(Keep101 = 'Y', ','&[101])&
            . . .
           ), 2)
from [AccessCount>1],
     (select t, iif(max([100]) is null, 'N', 'Y') as Keep100,
             iif(max([101]) is null, 'N', 'Y') as Keep101,
             . . .
      from [AccessCount>1]
     ) tkeep

The idea is to build a string with the commas as the separator. The mid statement just removes the first character in the string.