Post by l***@gmail.comPost by Ron WeinerPost by l***@gmail.comPost by Ron WeinerPost by l***@gmail.comPost by Ron WeinerPost by l***@gmail.comHi,
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