Repeating ID's and Trasnposing Data

[Xander]

I'm trying to transpose a dataset, without loosing the ID's

I've tried different ways, but the only thing I can seem to achieve is to tranpose all the data like you would do in a copy paste in excel.

I made a small example of what I'm trying to do.

I've got this:

[FONT=Calibri][SIZE=3][FONT=Consolas]ID jan feb mar apr ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]1 23 24 53 22 ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]2 45 67 87 87 ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]3 11 14 16 31 ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]4 66 78 98 99 ..[/FONT][/SIZE]
[/FONT]

And I want to create a table that looks like this:

[SIZE=3][FONT=Consolas]ID Dates Values[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]1 jan 23[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]1 feb 24[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]1 mar 53[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]1 apr 22[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]1 .. ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]2 jan 45[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]2 feb 67[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]2 mar 87[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]2 apr 87[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]2 .. ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]3 jan 11[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]3 feb 14[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]3 mar 16[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]3 apr 31[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]3 .. ..[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]4 jan 66[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]4 feb 78[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]4 mar 98[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]4 apr 99[/FONT][/SIZE]
[SIZE=3][FONT=Consolas]4 .. ..[/FONT][/SIZE]

(sorry for the layout, they didn't want to allign)

Hope anybody can get me pointed in the right direction with this.
I know Access pretty well, but I don't have a lot of experience with VBA.

 

[SimonB1978]

Hi,

Where does the "I've got this:" comes from? Excel file, Access table?

The idea would be to read it line by line, have the ID saved in a variable. Then, for each value, add a record in your table.

BTW, I would store the months as numbers, would be easier to sort if ever need be.

If you could give more info on the source we could help a lot more.

Simon B.

 

[gemma-the-husky]

easiest way is to write several append queries

extracting a single column each, and appending them to your final table