Access query - Switch data from tabular to columns

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. Thanks Julian