Creating an Append Query that can SKIP blank (null) fields

[pmcleod]

Good day everyone. I spent some time searching this site for an answer - but had no luck. I'm looking for a way to build a Query that will skip over blank fields.

Reason: I have about 40 fields that need to be printed out, comma delimited, onto a repot. The problem is that for most of the records most of the fields are actually blank.

If I build a typical query that appends and separates each value with a comma into one field - then I'll wind up with a result that looks like:

-1C,,,,,,,,,-35A,,,,,,,,,,-41C and so on.

Is there a way to "clean up" the result so it looks like: -1C, -35A, -41C?

Fields in question:

tblLabsSubmittorItem:

...NoAdditionalTests;1A;1B;1C;2A;2B;2C;3A;3B;3C;4A;4B;4C;5A;5C;6A;6B;6C;7A;7C;8A;8B;8C;9A;9C;10A;10C;11A;11C;12A;12C;13A;13C;14A;14C;16A;17A;23A;24A;25A;26A;ASTMC672;ASTMC457;...

One record will typically have only have 3 or 4 of these fields populated. If it is not possible to do this with a Query - then perhaps I could have the field it writes out to on a from - then take that field on the form and clean it up...??

either way can anyone help me?

Thank you in advance.

 

[Mile-O]

That sounds like you need to reconsider your table down as you would appear to have a huge repeating group (a violation of First Normal Form [1NF]).

 

[pmcleod]

I know the table design is off - but there were too many change management issues and this is they way it has to be.

can you help me?