Concatenate into multiple columns (1 Viewer)

lala · Aug 27, 2010

Hi, i need to concatenate different rows

Code:

C1  C2
1   MD
2   MD
3   MS
1   MA
3   KS

should become concatenated into multiple columns

Code:

C1   C2   C3
1    MD   MA
2    MD
3    MS   KS

NOT in 1 column like below

Code:

C1   C2
1    MD, MA
2    MD
3    MS, KS

thank you very much

RuralGuy · Aug 27, 2010

Have you looked into pivot tables?

lala · Aug 27, 2010

you mean in excel?

lala · Aug 27, 2010

if it's excel it doesn't work for me.

Code:

Public Function fnGetAccountsDeg(TheSSN As Long) As String
Dim Temp As String
Dim rst As DAO.Recordset

Temp = ""

Set rst = CurrentDb.OpenRecordset("Select * From mt8 where [ProviderID] = " & TheSSN)
While Not rst.EOF And Not rst.BOF
   Temp = Temp & rst!Degree & ", "
   rst.MoveNext
   Wend
fnGetAccountsDeg = left(Temp, Len(Temp) - 2)
End Function

this creates a function that you can then use in a query and it will concat everything into one column

how do i break it up into columns

RuralGuy · Aug 27, 2010

Try a crosstab query in Access.

lala · Aug 27, 2010

how? what will be in a column heading?

RuralGuy · Aug 27, 2010

Access supports Pivot Tables. What version of Access are you using?

lala · Aug 27, 2010

crosstab, pivot tables don't work for me in this case

can someone please look at the code above and just give me an idea on how to break it up into different columns

thank you very much

lala · Aug 27, 2010

RuralGuy said:
Access supports Pivot Tables. What version of Access are you using?

are you saying that a pivot will break it up just like i need it?
also, this is a part of a report i'm automating, and as far as i know, even if i can create the pivot by hand - it won't do
then i have to learn automating pivots and this is a rush job
i'd rather work with what i know

RuralGuy · Aug 27, 2010

Understood. Sorry.

lala · Aug 27, 2010

Don't be)))))))) I didn't explain it to start with
But since we are already talking about it, how will the pivot do this? As far as I know it can't
Just like crosstab cant

RuralGuy · Aug 28, 2010

I think I do not know what I'm talking about when it comes to these items. Very little experience with them. I tried a sample db and could not get it to do anything useful.

lala · Aug 28, 2010

Anyone? I really need this done
I don't need the code written for me, just the direction to go to

RuralGuy · Aug 28, 2010

Lala,
I asked for assistance in the VIP forum. Hopefully someone will drop by.

MarkK · Aug 28, 2010

My 2c is that that result violates 1NF and is therefore a headache. Data in that shape is both hard to produce and hard to consume. I'd review your objectives and find a different way to get there.
What don't you like about this?

Code:

ID   States
1    MD, MA
2    MD
3    MS, KS

How is the other thing better?
Cheers,

John Big Booty · Aug 28, 2010

Perhaps the solution presented here will point you in the right direction.

Or perhaps check the solutions offered here or here

Galaxiom · Aug 28, 2010

This will do what you want for up to two states. It puts them in columns by alpha order.

Code:

SELECT C1, C2, IIF(Temp <> C2,Temp,Null) AS C3
FROM
   (SELECT Table1.C1, Min(Table1_1.C2) AS C2, Max(Table1_1.C2) AS Temp
   FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.C1 = Table1_1.C1
   GROUP BY Table1.C1)
;

Let me know if you need more columns.

ChrisO · Aug 28, 2010

Looks like it can be done by making a table.

But after that I have a bottle of Commandaria St. John which needs its cork pulled.

A2K3 attachment.

Galaxiom · Aug 28, 2010

ChrisO said:
A2K3 attachment.

Looks rather complicated. My query is better.

Joining a table to itself in a query is an often overlooked technique that works very well to combine rows.

stopher · Aug 28, 2010

This query (assuming C1 is a number):

Code:

TRANSFORM Max(C2) AS ColID
SELECT C1
FROM Table1
GROUP BY C1
PIVOT "C" & (DCount("[C2]","Table1","[C1]=" & [C1] & " AND [C2] <='" & [C2] & "'")+1)

Produces this:

Code:

C1   C2   C3
1    MA   MD
2    MD
3    KS   MS

It will produce as many columns as required. It works by populating a column number field (ColID) and then cross-tabbing. The DCount function is used to provide the sequence. The +1 bit is simpy to ensure the columns start at 2.

Unfortunately the result in each row will be in alphabetical order since there is no other order defined in your list. If there is a primary key then the order can be corrected to exactly match what you requested.

But I agree with the others. What are you planning to do with the data in this structure? If you are simply putting it in a report then consider John's post or maybe use the multiple column feature in the print options.

hth
Chris

Concatenate into multiple columns (1 Viewer)

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

bit cruncher

AWF VIP

Super Moderator

Registered User.

Attachments

Super Moderator

AWF VIP

Similar threads

Users who are viewing this thread