Transpose a table

[ijergas]

I have the following table in access

A B
1 1
1 2
2 3
2 4
2 5
3 6

and would like to write code to make it look like:
A B C D
1 1 2
2 3 4 5
3 6

Can anyone help please?

Thanks.

 

[raskew]

Hi -

Where do C & D come into the picture (looking at your current table)?

Bob

 

[richary]

If it's just a one-off exercise and not too much data, I would export the original table into Excel, and manipulate it there (maybe use Excel's Paste Transpose function) and re-import it back into Access.

 

[ijergas]

It's actually needs to be automated and therefore in code. The C & D are variables that are created once the data is transposed...

Thanks for your help.

Josh

 

[Bat17]

but what is the logic behind the change?
you start with 12 items of data and end up with 9!

Peter

 

[modest]

It's actually needs to be automated and therefore in code. The C & D are variables that are created once the data is transposed.../QUOTE]

Josh, before we can answer your question, we have to know what you want.

You start off with:
A B
1 1
1 2
2 3
2 4
2 5
3 6

Therefore you should end up with the same thing, since all you want to do is rearrange your data. In the end there should be A,B, 1(3x), 2(4x), 3(2x), 5, 6... and NO C or D.

Is it possible that you want something like this instead:
A 1 1 2 2 2 3
B 1 2 3 4 5 6

 

[ijergas]

Thank you so much for your interest.

Let me try this a different way.

I have the data:

ID NAME
1 JOE
1 DAVID
2 GEORGE
2 GARY
2 RONALD
3 JEFF

and I need a table that is like this:

ID NAME1 NAME2 NAME3
1 JOE DAVID
2 GEORGE GARY RONALD
3 JEFF

Does that make sense?

Thanks again,

josh

 

[RuralGuy]

Pat,
It looks like it is the same transformation process except the OP threw in the C & D from nowhere on the 1st example.

 

[modest]

Cross tab is the easiest way, the formatting won't be how you want it though.

 

[workmad3]

the c and d were thrown in as column headers from the looks of things,

started with 2 columns of data, a and b, ended up with 4 columns of data, a, b, c and d.

its just data grouping in the end (all the data items in b where the A column is the same get grouped, etc).

 

[modest]

ID NAME
1 JOE
1 DAVID
2 GEORGE
2 GARY
2 RONALD
3 JEFF


With the data you gave a crosstab would look as follows:

ID JOE  DAVID  GEORGE GARY RONALD JEFF
1   1     1
2                1     1     1
3                                  1

Note that you would have to include a 3rd erroneous field