Create table with 'dynamic columns' on the fly

[tezread]

Fresher VBA standard here so be gentle.

How could I generate a table on the fly using VBA?

At the moment I have a cross tab query that works a treat but I was experimenting and wanted to try something different.
The cross tab has columns for days 0,1,2,3 up to 12 and 'date'. The user can choose start/end dates. Here is the cross tab to illustrate

PARAMETERS [Forms]![frmRptDialogSingle]![begdate] DateTime, [Forms]![frmRptDialogSingle]![enddate] DateTime;
TRANSFORM Count(qry_cardiology1.EpisodeID) AS CountOfEpisodeID
SELECT Format([RequestDatetime],"yyyy/mmm") AS [Date]
FROM qry_cardiology1
WHERE (((qry_cardiology1.RequestDatetime) Between [Forms]![frmRptDialogSingle]![begdate] And [Forms]![frmRptDialogSingle]![enddate]))
GROUP BY Format([RequestDatetime],"yyyy/mmm"), Format([RequestDatetime],"yyyy/mm")
ORDER BY Format([RequestDatetime],"yyyy/mm")
PIVOT qry_cardiology1.Days In (0,1,2,3,4,5,6,7,8,9,10,11,12);

I wanted to store the results of the query in a 'table' so I can comment on the report and store those comments in the table as well. This means I can the report can be given a filename and be recovered by the user

 

[SteveSchapel]

Tezread,

Am I correct in my understanding that this crosstab query is a saved query? If so, try this:

CurrentDb.Execute "SELECT * INTO NewTable FROM YourCrosstabQuery"

 

[tezread]

Thank you Steve. I see how tis generates the table on the fly now. It is pullong the results from the cross tab and building the table with its respective field names. BUT - how do I add another 'Comments' field in the new table on the fly as well - remembering this is a cross tab query

 

[DJkarl]

You could use a SQL statement.

ALTER TABLE [YourTable] ADD [NewColumn] TEXT(255)

 

[tezread]

Is there a way I can let the user decide what the table name is by essentially assigning a 'filename' to the table so they can store and recover results

 

[SteveSchapel]

Tezread,

Not sure exactly that I understand what you mean here. But to expand upon my earlier suggestion, does this match your purpose?...

   Dim NewTableName As String
   NewTableName = InputBox("What name do you want to give the table?")
   DBEngine(0)(0).Execute "SELECT * INTO " & NewTableName & " FROM YourCrosstabQuery", dbFailOnError

But frankly, I am a bit suspicious of your desire to do this. It is unusual to want to store the results of a query, when those results could presumably be re-created on demand by repeating the query. Maybe I'm barking up the wrong tree here, but at the moment it looks like you might be making life harder than necessary for yourself.

 

[tezread]

Tezread,

Not sure exactly that I understand what you mean here. But to expand upon my earlier suggestion, does this match your purpose?...

   Dim NewTableName As String
   NewTableName = InputBox("What name do you want to give the table?")
   DBEngine(0)(0).Execute "SELECT * INTO " & NewTableName & " FROM YourCrosstabQuery", dbFailOnError

But frankly, I am a bit suspicious of your desire to do this. It is unusual to want to store the results of a query, when those results could presumably be re-created on demand by repeating the query. Maybe I'm barking up the wrong tree here, but at the moment it looks like you might be making life harder than necessary for yourself.

thats is kind of what I want. I understand your reservation though as I suspected I am making life difficult. What I am trying to do is have the user be able to make comments on a report basically. To do this i wanted to create a form that pulls the results of the cross tab query and lets the user comment on it before hitting 'preview'