I have attached an Access 2007 database with 3 tables to transform 2 indexs to help and include in the new table and the new table as it should look after the query.
If you could create a query that
1) Cycles through the Index monthly table picking each table one by one switching all the data to columns and adding stock_codes, type and category. After it has gone through each table.
2) Creates a union of all the different tables.
3) Eliminates #N/A N/A and blank fields in the B_Value column.
I think this can be done in a query or two and may need a little VBA to cycle through the tables. Of course all this is much bigger I have about 10 tables with upto 200 columns with upto 6000 rows of data so the query will over time create a table with over 10 million rows.