From 1 column to 2 columns

[MarcovH]

Hi,

currently I am having a table in which the data is in 1 column, but this needs to be in 2 columns. How to do?

I have:
VendorCode VendorName Quality MaxClaim
411411 SCA PACKAGING MUNKSUND 1001 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1200 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1300 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1400 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1500 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1600 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1700 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1001 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1200 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1300 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1400 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1500 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1600 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1700 FSC Mix 90%

But what I want to have is:
VendorCode VendorName Quality MaxClaim MaxClaim
411411 SCA PACKAGING MUNKSUND 1001 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1200 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1300 FSC Controlled Wood FSC Mix Credit


411411 SCA PACKAGING MUNKSUND 1400 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1500 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1600 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1700 FSC Controlled Wood FSC Mix 90%

I looked everywhere, but the crosstab function is not the one, as that function will put all other data in separate columns, whilst I only want to have 2 column MaxClaim. If possible the other way around would help as well.

[ItsMe]

Rather than changing your table structure, I would try creating a Crosstab Query. Perhaps the Query Wizard can help.

[MarcovH]

thanks ItsMe, but the crosstab query is giving per MaxClaim a new column, whilst 1 only want one.

[June7]

First you said 2 columns, then you say 1 column - which is it? From what I see in the sample, a CROSSTAB would create at least 3 fields from the MaxClaim data.

Options for Getting data from one field of multiple records concatenated into a single record:

1. VBA, review: http://allenbrowne.com/func-concat.html

2. multiple queries
SELECT * FROM table WHERE MaxClaim="FSC Controlled Wood";
SELECT * FROM table WHERE MaxClaim<>"FSC Controlled Wood";
SELECT query1.*, query2.* FROM query1 INNER JOIN query2 ON query1.VendorCode=query2.VendorCode AND query1.Quality=query2.Quality;
or an all in one statement
SELECT query1.*, query2.* FROM (SELECT * FROM table WHERE MaxClaim="FSC Controlled Wood") AS query1 INNER JOIN (SELECT * FROM table WHERE MaxClaim<>"FSC Controlled Wood") AS query2 ON query1.VendorCode=query2.VendorCode AND query1.Quality=query2.Quality;

Can be explicit in the selection of fields instead of using * wildcard. Can even concatenate the two MaxClaim fields:

query1.MaxClaim & " " & query2.MaxClaim AS NewMaxClaim

3. involves VBA and temp table - code would be similar to Function example in option 1 but would be a Sub that writes records to temp table instead of just returning value to a query