Database: 1997 US IO data.mdb
Notes / Method:
· The raw Make and Use data files (IOMakeDetail.txt and IOUseDetail.txt) are read into Access and saved as tables Make Data raw and Use Data raw.
· The sector mapping from NAICS to SAM sectors must be read into Access twice and saved as two identical tables: Final Category Mapping—Commodities, and Final Category Mapping—Industries.
· The numbered listing of the SAM Sectors (i.e. numbered 1 through 106, including LABOR and CAPITAL) must be read into Access twice and saved as two identical tables: Sector Descriptions—Industries, and Sector Descriptions—Commodities.
· The Make Data raw table must now be appended with dummy data to ensure that every row of the Make Matrix has at least one non-null entry, and every column has at least one non-null entry as well. This ensures that the final Make Matrix is in fact a 106x106 matrix (including NA, LABOR, and CAPITAL) and not something smaller. This is accomplished by running the append query entitled ‘Append Make Table’.
· The Use Data table must be appended in a similar way, by running the append query entitled ‘Append Use Table’.
· With all of the above complete, the crosstab query ‘Aggregate Make Matrix Numbers’ will produce a Make Matrix. This query acts similarly to a Pivot Table in Excel. The row and column headings will be the SAM sector codes, in order, from 1 to 106. Note that the one-to-many sectors (i.e. the retail, wholesale, and ACC sectors) will not show up here, so this matrix will actually be 90x90 instead of 106x106. This will be fixed in a later Excel spreadsheet.
· Likewise, the crosstab query ‘Aggregate Use Matrix Numbers will produce the Use Matrix, which will also be 90x90.
· To export the Make matrix out of Access and into Excel, return to the query listing and right click on the ‘Aggregate Make Matrix Numbers’ query. From the drop down menu, select ‘Export’. On the dialog box that follows, select to save as an xls file, and do not change the file name.
· The Use matrix can be exported in similar fashion.