Discussion:
Merge few words from other 5 fields into one fields
(too old to reply)
l***@gmail.com
2017-02-24 06:14:09 UTC
Permalink
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
2017-02-24 13:49:39 UTC
Permalink
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
l***@gmail.com
2017-02-28 01:45:05 UTC
Permalink
Post by Ron Weiner
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
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
2017-02-28 03:13:02 UTC
Permalink
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
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.
Apple,,Grape,Banana
I am appreciated your help.
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
l***@gmail.com
2017-03-01 13:39:17 UTC
Permalink
Post by Ron Weiner
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
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.
Apple,,Grape,Banana
I am appreciated your help.
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
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
2017-03-01 16:11:19 UTC
Permalink
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
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.
Apple,,Grape,Banana
I am appreciated your help.
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
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.
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 ....
l***@gmail.com
2017-03-02 13:11:24 UTC
Permalink
Post by Ron Weiner
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
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.
Apple,,Grape,Banana
I am appreciated your help.
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
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.
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 ....
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
2017-03-02 16:42:55 UTC
Permalink
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@gmail.com
Post by Ron Weiner
Post by l***@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 ?
Apple
Peach
Grape
Banana
Apple, Peach, Grape, Banana
Thank you.
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
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.
Apple,,Grape,Banana
I am appreciated your help.
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
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.
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 ....
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.
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

Loading...