Creating table from crosstab-type table

[ejc00]

I have a table imported from Excel which is set up like a Crosstab query. For instance, the data looks as follows, where the numbers as columns are months:

Year Name 1 2 3 4
2001 aaaa 100 120 150 20
2001 bbbb 50 75 80 100
2002 cccc 200 100 10 75

I need to get it into the following format:

Year Name Month Amount
2001 aaaa 1 100
2001 aaaa 2 120
2001 aaaa 3 150
2001 aaaa 4 20
2001 bbbb 1 50
2001 bbbb 2 75

and so on... what is the best way to get the data into this format? Any tips would be greatly appreciated.

Thanks,
Evan

 

[dhookom]

This depends on how often you do this. If only rarely, I would create an append query and change the syntax slightly and perform the appends. If this is done on a fairly regular basis, I would write some code that would loop through the fields collection of the imported table and then create and run the SQL appends.

Duane
MS Access MVP