Transpose data

[Kay Ree]

Hello there. I need some help. I would like to transpose
my data from rows to columns. Below is an example.

Field1 field2 field3
Name john
Address xxx1
ZipCode 12345
City atlanta
State ga
Name jane
Address xxx2
ZipCode 12345
City nashville
State tn
Name alex
Address xxx3
ZipCode 12345
City sacramento
State ca
Name daniel
Address xxx4
ZipCode 48945
City san diego
State ca

Instead of field1 field 2 and field3, I would like the
columns to be Name Address ZipCode City State and the data
that follows to go under the column headings-- the norm.
Any ideas on how to accomplish this? Thanks in advance!

 

[Lynn Trapp]

Here's one way. Create a new table (i'll call it tblAddresses for now) with
the fields you want. Then you can do a series of insert queries to get your
data.

Insert Into tblAddresses([FullName])
Select Field2
From YourTable
Where Field1 = "Name";

Insert Into tblAddresses([Address])
Select Field2
From YourTable
Where Field1 = "Address";

And so on until you have all the data in the new table.

 

[Kay Ree]

Lynn,

If I understand this correctly, that will append the data
to your table. It will not put the data side by side in
the columns. Do you care to clarify? Maybe I am not
thinking or doing it correctly. Big thank you in advance.
I think I am in the right direction!

-----Original Message-----
Here's one way. Create a new table (i'll call it tblAddresses for now) with
the fields you want. Then you can do a series of insert queries to get your
data.

Insert Into tblAddresses([FullName])
Select Field2
From YourTable
Where Field1 = "Name";

Insert Into tblAddresses([Address])
Select Field2
From YourTable
Where Field1 = "Address";

And so on until you have all the data in the new table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello there. I need some help. I would like to transpose
my data from rows to columns. Below is an example.

Field1 field2 field3
Name john
Address xxx1
ZipCode 12345
City atlanta
State ga
Name jane
Address xxx2
ZipCode 12345
City nashville
State tn
Name alex
Address xxx3
ZipCode 12345
City sacramento
State ca
Name daniel
Address xxx4
ZipCode 48945
City san diego
State ca

Instead of field1 field 2 and field3, I would like the
columns to be Name Address ZipCode City State and the data
that follows to go under the column headings-- the norm.
Any ideas on how to accomplish this? Thanks in advance!

.

 

[John Spencer (MVP)]

First of all, where is the data currently residing? Is it in a text file,
excel, another db?

If it is in an Access table, how do you identify the group of items (name,
address, city, state, zip) that belong together?

You could probably build this data into a new table using a series of queries if
you have a way to specify which Name, address, city, state, and zip goes
together. If all you have is the order of the fields in a text file, you would
probably be best off using VBA to populate a new table.

Lynn,

If I understand this correctly, that will append the data
to your table. It will not put the data side by side in
the columns. Do you care to clarify? Maybe I am not
thinking or doing it correctly. Big thank you in advance.
I think I am in the right direction!

-----Original Message-----
Here's one way. Create a new table (i'll call it tblAddresses for now) with
the fields you want. Then you can do a series of insert queries to get your
data.

Insert Into tblAddresses([FullName])
Select Field2
From YourTable
Where Field1 = "Name";

Insert Into tblAddresses([Address])
Select Field2
From YourTable
Where Field1 = "Address";

And so on until you have all the data in the new table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello there. I need some help. I would like to transpose
my data from rows to columns. Below is an example.

Field1 field2 field3
Name john
Address xxx1
ZipCode 12345
City atlanta
State ga
Name jane
Address xxx2
ZipCode 12345
City nashville
State tn
Name alex
Address xxx3
ZipCode 12345
City sacramento
State ca
Name daniel
Address xxx4
ZipCode 48945
City san diego
State ca

Instead of field1 field 2 and field3, I would like the
columns to be Name Address ZipCode City State and the data
that follows to go under the column headings-- the norm.
Any ideas on how to accomplish this? Thanks in advance!

.

 

[Kay Ree]

It is in an Access table. The group of items are very
sequential. Name, Address, City, State and zip and then it
repeats itself. It is exactly like the example below. Any
ideas?

 

[John Spencer (MVP)]

Well, if it stays in that order you are probably ok, but I wouldn't rely on it.

ON A COPY, I might try this as a quick and dirty method of transposing the data.

Add a column and make it's type AUTONUMBER Sequential.

Access should then assign one up numbers to all the records.

Add a New table with your desired structure. Then run the following append
queries.

INSERT INTO NewTable ([RecordID], [fldName])
SELECT RecordID, [field2]
FROM OriginalTable
WHERE RecordId Mod 5 = 1

That should build your records and give them a recordid 1,6,11 etc

Now you need update queries to do the rest of the fields

UPDATE NewTable INNER JOIN OriginalTable
ON NewTable.RecordId + 1 = OriginalTable.RecordID
Set NewTable.Address = OriginalTable.Address


UPDATE NewTable INNER JOIN OriginalTable
ON NewTable.RecordId + 2 = OriginalTable.RecordID
Set NewTable.ZipCode = OriginalTable.ZipCode

Continue the process for your other fields.

The other method would be to use VBA to add records to a recordset and step
through the records in the table. That can be written.

 

[Lynn Trapp]

Kay,
You are absolutely right, except for the first insert statement. The
subsequent statements would need to be update statements, as pointed out by
John Spencer. Thanks for catching my faux pas.

 

[Kay Ree]

John,

Somehow when I run it, I get an "Enter parameter value"
and the records arent updated into the new table? Do you
know why? Thanks!

 

[John Spencer (MVP)]

I have no idea. I assume you changed the field names and table names to match
your actual field and table names.

Enter Parameter Value usually specifies the name of the parameter. That should
at least tell you what the query sees as an invalid field or tablename.

For instance "Name" is a bad fieldname, since every object in Access has a Name
property. You must wrap "Name" with square brackets in a query if you want to
be sure the query will work.

If you cannot get this to work, try posting the SQL of the query that fails.
Perhaps someone can see where we have gone wrong.