Merge few words from other 5 fields into one fields

[learning...@gmail.com]

Hi,

Is there a way to group from Field A to D into one field "All Types"? I have the query containing "Field A", "Field B", "Field C", "Field D". I want to create one "All Types" but I am not sure if there is a possible way to make using Query if statement ?

Field A:
Apple

Field B:
Peach

Field C:
Grape

Field D:
Banana

All types:
Apple, Peach, Grape, Banana

Thank you.

[Ron Weiner]

learning...@gmail.com presented the following explanation :

Is this what you are looking for?

SELECT [FIELD A],[FIELD B],[FIELD C],[FIELD D],
[FIELD A] & ', ' & [FIELD B] & ', ' & [FIELD C]
& ', ' & [FIELD D] AS ALLTYPES
FROM WHATEVER
WHERE WHATEVER
ORDER BY WHATEVER

It concatenates all of the 4 columns into a single one.

Rdub

[learning...@gmail.com]

Yes, That's what I am looking for.. What if [Field B] is blank, I'm getting like this. How I remove the comma from two commas between Apple and Grape.

All types:
Apple,,Grape,Banana

I am appreciated your help.

[Ron Weiner]

learning...@gmail.com explained on 2/27/2017 :

Untested Air code --- Try:

SELECT [FIELD A],[FIELD B],[FIELD C],[FIELD D],

Replace([FIELD A] & ', ' & [FIELD B] & ', ' & [FIELD C] & ', ' &
[FIELD D],",,","'") AS ALLTYPES

FROM WHATEVER
WHERE WHATEVER
ORDER BY WHATEVER

Rdub

[learning...@gmail.com]

thanks I tried - it seems worked but I am getting like this...

,,,,Field D]
''[field B],,
[Field A],,,,

Some of the fields has blank.

Thanks again for your help.

[Ron Weiner]

learning...@gmail.com laid this down on his screen :

Hmmm... I see what's happening. Replace will not work if the first
field is missing, or if multiple adjacent fields are missing on one
row. So we'll have to test for each field individually as we build the
output.

SELECT [Field A], [Field B], [Field C], [Field D],
IIf(Len(Nz([FIELD A],""))>0,[FIELD A] & ",","") &
IIf(Len(Nz([FIELD B],""))>0,[FIELD B] & ",","") &
IIf(Len(Nz([FIELD C],""))>0,[FIELD C] & ",","") & [FIELD D] AS AllTypes
FROM YourTable
WHERE ....
ORDER BY ....

[learning...@gmail.com]

Thanks Ron.

That's awesome.

It looks great but it's only minor.

[Field A],
[Field B],[FIELD C],
[Field A],[FIELD B],[FIELD C],
[Field A],[Field D] (no comma in the end)

The first three lines have the comma in the end but not third line.

Thanks again.

[Ron Weiner]

learning...@gmail.com explained on 3/2/2017 :

OK, this time I actually took the time to create a Table and a query to
test the sql. I think I have all of the possibilities covered at this
point. Here ia the Sql:

SELECT [Field A], [Field B], [Field C], [Field D],

nz([FIELD A],"") & IIF(Len(nz([FIELD A],"")) AND Len(nz([FIELD B],"") &
nz([FIELD C],"") & NZ([FIELD D],""))>0, ",","") &
nz([FIELD B],"") & IIF(Len(nz([FIELD B],"")) AND Len(nz([FIELD C],"") &
NZ([FIELD D],""))>0,",","") &
nz([FIELD C],"") & IIF(Len(nz([FIELD C],"")) AND Len(nz([FIELD
D],""))>0,",","") &
nz([FIELD D],"") AS AllTypes
FROM Table7;

Here is the Result:

Field A Field B Field C Field D AllTypes
AA BB CC DD AA,BB,CC,DD
A2 B2 D2 A2,B2,D2
B3 C3 D3 B3,C3,D3
A4 A4
B5 C5 B5,C5
D6 D6

Rdub