Transpose Data

[jamesfranklin]

Hi,

I have a table which contain data in single column. i want to transpose data to this.

This is the data which i have
Name


Robin
Mark
Sydney
Karun


then I need

Name Robin Mark Sydney Karun

Thank u for ur great time spend in this post...

[June7]

How many names are in the column? Why do you need to do this?

[alansidman]

Echoing June's Question. But in the meantime, I found this code you could use.

http://access-4suc6.blogspot.com/200...umns-into.html

[jamesfranklin]

Thank you Guys. If its possible in Query....

[June7]

Your requirement isn't making sense. Is the example you show what you want as a row of data?

A CROSSTAB query requires data source with at least 2 fields.

A SELECT query would be like:
SELECT "NAME" As F1, "Robin" As F2, "Mark" As F3, "Sydney" As F4, "Karun" As F5 FROM tablename;

Otherwise, will need VBA code.

Again, how many names are there and why do you need this?

[jamesfranklin]

Hi

Thank u june7 for ur reply. But in this i have set of data like this
Name
(1)
Robin
Mark
Sydney
Karun

(2)
james
Victor
Kallis
Franklin.................

Thank u for ur valuable time spend in this post

[June7]

I don't understand data structure. What do (1) and (2) mean? I still don't know what you are trying to accomplish nor why.

[jamesfranklin]

Hi,

Transpose Column To Row

Input Data

Supplier	1	2	3
a
b
c
d
e
f
g
h

Output

Supplier	1	2	3
a	b	c	d
e	f	g	h

[June7]

Still doesn't make sense. What criteria determines that a,b,c,d should be one record and e,f,g,h in another?

I doubt what you want can be done with a query alone and that VBA code will be needed.

Review
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html

[orange]

Can you tell us in a couple of sentences what your database is about, and why you need the structure you do? There may be some options.

Your question and responses don't say much, but it doesn't look like a relational database requirement.

[jamesfranklin]

Hi,

Thank u for reply.

For Example;
I copy Address from website and paste into excel. The address save in three columns. Each n Every time i want cut n copy the data n paste into the row. i want to take lot of data. So, "it will click" it reduce my time wasting in that cut n paste from column to row.

Like this
Contact:
Asante Shakuur
Associate Director of Development and External Relations
(301)405-8189


Contact:
Carrie Hilmer
Coordinator for External Relations
(301)405-4471

Ouput
Contact: Asante Shakuur Associate Director of Development and External Relations (301)405-8189

Contact: Carrie Hilmer Coordinator for External Relations (301)405-4471


Thank U for UR Time Spending int this post....

[June7]

You say address is in three columns, however, your data sample reflects one column. This data cannot be rotated by query alone. Will require VBA code. Refer to links in previous post.

[alansidman]

Since you have the data in Excel, why not transpose the data there before you bring it into Access

Look here for a quick to develop Excel Macro that I believe will accomplish what you are trying to do.

[recyan]

In addition to Alan's suggestion, look at CONCATENATE in Excel.

Thanks

[jamesfranklin]

Hi,

Thank u for ur reply. Ya, Ur suggestions is good but i'm not familiar with excel macro. So, can u help on access it will helpful to me.


Thank u