Transposing Columns to Rows

[hycho]

Hi All,

I need to transpose some columns into rows, so that I can unique records in my database.

Here's an example of data I have:

ID Language
1 English
1 Spanish
2 English
2 French

Here's the output I want (notice I now have two language columns instead of one):

ID Language1 Language2
1 English Spanish
2 English French

Does anyone how I might be able to write a VBA code for this?

Thanks for your help.

 

[MStef]

Look at "DemoLanguageA20022003.mdb" (attachment, zip).
Look at Table1, Query1GroupBY (MakeTable Query).
Run query, look at a "tblNewLanguage".
Adapt it in your mdb.

 

[hycho]

A very good solution to a simple problem (can I use that query for 10 languages?), but my data gets more complex. A record can have over 10 languages, and I would like to have those records in over 10 columns. To illustrate with 4 languages,

ID Language
1 English
1 Spanish
1 Russian
1 Chinese
2 English
2 French
2 Polish
2 Italian

Here's the output I want 4 language columns.

ID Language1 Language2 Language3 language 4
1 English Spanish Russian Chinese
2 English French Polish Italian

Thanks

 

[MStef]

Try it and see.

 

[hycho]

Try it and see.

I did try it yesterday, the query works well for two dimensional data. But what happens when there are like 3 languages (and more)?

Do I set the query to have 3 columns, as in the group row, change to first, last last? Is there a feature where I can go first, second, third, ..., last?

Thanks for your help.

 

[MStef]

I think your tables not normalized well.
Learn something about DATABASE NORMALIZATION.

 

[Brianwarnock]

I think that his table is normalised if it wasn't wouldn't the data be

Id language1 language2 etc and then the query would be easy, but other queries such as who speaks Spanish difficult.

A search via Google of Transpose columns to rows in Access will yield results including one from Microsoft. I think Pbaldy has answered this question on here many years ago but cannot find it.

Brian

 

[recyan]

Is this possible ???
The sub-query to save :
qryTheRanks

SELECT 
	A.ID, 
	A.Language, 
	count(*) AS TheRank
FROM 
	tblLanguages AS A 
	INNER JOIN 
	tblLanguages AS B 
	ON 
	(A.Language>=B.Language) 
	AND 
	(A.ID=B.ID)
GROUP BY 
	A.ID, A.Language;

The final query to run :

TRANSFORM First(qryTheRanks.Language) AS FirstOfLanguage
SELECT 
	qryTheRanks.ID
FROM 
	qryTheRanks
GROUP BY 
	qryTheRanks.ID
PIVOT 
	qryTheRanks.TheRank;

Thanks