transpose data

[Mac]

Hello
I have an access table with multiple columns. What I would
like to do is tranpose the data. Column 1 has id Column
2 through 20 different headings. I would like to take
column 1 as one field column 2-20 as a field column 3 as
the data under headings 2-20. Does this make any sense?
column 1 column 2-20 column 3
id acctno amt

Any help would be greatly appreciated. Thank You

 

[Steve Schapel]

Mac,

Yes, it makes perfect sense. This is a process of normalising your
data, which is a fantastic idea.

I would imagine this is just a one-off exercise, right? So you don't
need to have the procedure automated for repeat performance? I would
just set up and manually run a series of 19 Append Queries. Create your
3 field table. Then make a query based on your original table, with
columns 1, 2, plus a calculated field which if you are doing it in query
design, would look something like this...
Acct: 2
.... or...
Acct: "Name of first one"
Make it into an Append Query (select Append from the Query menu, and
nominate your new table as the one to append to).
In the Append To row of the query design grid, enter the fields from the
new table that each of the columns will append to, i.e. in the order
suggested above it will, I think, be id, amt, and acctno.
Run the query (click the toolbar button with the red ! icon)
In the query, replace column 2 with column 3, and replace the data in
the calculated field with the applicable value.
Run the query again.
Repeat for each column up to 20.
Make sense?

 

[Guest]

Thank you. Thank you. It worked perfectly.

-----Original Message-----
Mac,

Yes, it makes perfect sense. This is a process of normalising your
data, which is a fantastic idea.

I would imagine this is just a one-off exercise, right? So you don't
need to have the procedure automated for repeat performance? I would
just set up and manually run a series of 19 Append Queries. Create your
3 field table. Then make a query based on your original table, with
columns 1, 2, plus a calculated field which if you are doing it in query
design, would look something like this...
Acct: 2
.... or...
Acct: "Name of first one"
Make it into an Append Query (select Append from the Query menu, and
nominate your new table as the one to append to).
In the Append To row of the query design grid, enter the fields from the
new table that each of the columns will append to, i.e. in the order
suggested above it will, I think, be id, amt, and acctno.
Run the query (click the toolbar button with the red ! icon)
In the query, replace column 2 with column 3, and replace the data in
the calculated field with the applicable value.
Run the query again.
Repeat for each column up to 20.
Make sense?

--
Steve Schapel, Microsoft Access MVP


.