title: vba to make table when field names are dynamic
body: <p>I have a crosstab query that uses a dynamic date function to result in the column headers and therefore the field names. Ex: ForecastPeriod:DateAdd("m",[Period],[StartDate])
  This means that every time I run the crosstab query, I could end up with different field names.
  I need to take the results of this crosstab and combine it with the results of 3 other similar crosstabs to make a new table. Right now I use a make table query and 3 append queries. The reason I do this is to include 4 different categories of data per material item over the range of forecast periods.</p>
  
  <p>Result looks something like this:</p>
  
  <p>Material  Category   Per1 value  ...Per2 value ...... Per24 value</p>
  
  <p>MatA Demand              0     ...    10     .......    0</p>
  
  <p>MatA Demand Dollars     $0     ...   $10     .......   $0 </p>
  
  <p>MatA Forecast           10     ...    20     .......   50</p>
  
  <p>MatA Forecast Dollars  $10     ...   $20     .......  $50</p>
  
  <p>The problem is that the make table query is built already against the results of the current crosstab query. When I run the crosstab next month, the results will have different field names. So I am stuck manually changing the periods in the make table query design, dropping the one no longer in the results and adding the new one.</p>
  
  <p>Is there a way to use VBA to create a new table without knowing the field names until after the crosstab runs? </p>
  
  <p>Or is there a way to code the field names or pull them from the crosstab after it runs?</p>
  
  <p>If I use code like:
      strSQL = "SELECT tblForecast.Material, tblForecast.Category, tblForecast.X " &amp; _
               "INTO tblTemp " &amp; _
               "FROM tblForecast;"</p>
  
  <p>I really don't know what tblForecast.X will actually be called. It could be 11/1/08 or 12/1/08, etc.</p>
  
  <p>If I declare a variable to hold the field name and use the date code to change it, how to I add it to the table? Would I have use Alter Table?</p>
  
  <p>I'm sure this can be done, I just can't get my head around how to do it, so any help would be appreciated!!  Thanks!</p>