vba to make table when field names are dynamic

Question

Hi,

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.

Result looks something like this:

Material Category Per1 value ...Per2 value ...... Per24 value

MatA Demand 0 ... 10 ....... 0

MatA Demand Dollars $0 ... $10 ....... $0

MatA Forecast 10 ... 20 ....... 50

MatA Forecast Dollars $10 ... $20 ....... $50

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.

Is there a way to use VBA to create a new table without knowing the field names until after the crosstab runs?

Or is there a way to code the field names or pull them from the crosstab after it runs?

If I use code like: strSQL = "SELECT tblForecast.Material, tblForecast.Category, tblForecast.X " & _ "INTO tblTemp " & _ "FROM tblForecast;"

I really don't know what tblForecast.X will actually be called. It could be 11/1/08 or 12/1/08, etc.

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?

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!

Answer 1

It is possible to use your own headings for the crosstab, for example:

ColHead:"Month" & DateDiff("m",[SaleDate],Forms!frmForm!txtSomeDate)

This would result in column headings called 'Month' and an offset number from txtSomeDate.

It is also possible to use vba to build your new query.

Answer 2

VBA to create a table mirroring the columns of a query:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim SourceField As DAO.Field

Set db = CurrentDb

'Create a new tabledef
Set tdf = New DAO.TableDef

'Reference existing, saved querydef
Set qdf = db.QueryDefs("Query1")

tdf.Name = "Table2"

'iterate fields in the query and create fields in the new tabledef
For Each SourceField In qdf.Fields
    Set fld = tdf.CreateField(SourceField.Name, SourceField.Type, SourceField.Size)
    tdf.Fields.Append fld
Next

'Table is created and saved when the tabledef is appended to the current db
db.TableDefs.Append tdf