Make table from cross tab query 1

[BakeMan]

I have several dynamically created cross tab queries that I need to place into a table. These cross tab queries are created in VBA and look like the following:

strSQL1 = "TRANSFORM Count(RecordNumber) AS [The Value] SELECT 1 AS Header" _
& "FROM qryMIRs GROUP BY 1 PIVOT Format([DateInvestigationInitiated],""mmm-yy"&quot In" _
& "(""" & Format(DateAdd("m", -1, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -2, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -3, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -4, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -5, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -6, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -7, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -8, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -9, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -10, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -11, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -12, Now()), "mmm-yy&quot & """"

I am currently making them the "Record Source" for reports that are then used as sub reports on a main report to make lists of data in a drawn table. Now I've been asked to perform calculations on the data to come up with totals and percentages. I can easily do this if I had all of the data stored in a table, but I can't seem to figure out how to take these cross tab queries made in VBA and turn them into tables. I tried to used the queries as the from portion of a "SELECT * INTO [tablename] FROM (query)" SQL statement but I keep getting a syntax error on the FROM statement. Does anyone have any ideas of how I could get this data into a table?

Thanks for your help in advance,

BAKEMAN

 

[scriverb]

This is pretty easy for you. Just treat one of these CrossTab queries as a recordset(table) of data that can be used as input into a make-table query. While in the Design windows of a new query and after you have selected the first cross-tab query select from the query menu above the selection Make-table query. You will be prompted for a table name. Don't pick one from the dropdown list but just type in a new name. Close this window and save the query. Now run the query and a new table is created from the first cross-tab query.

Now with each of the next two cross-tab queries you want to create action queries to Append these records to the new table just created. In the design window of a new query select the second cross-tab query. Again select from the Query menu Append query. You will be prompted for the table to append to. Select the newly created table from the first make-table query that was just created. update the columns of the new table with the data from the second cross-table query. Save and run the query. Do the same with the third.

You now have a new table with the records from all 3 cross-table queries. Let me know if you need more of an explanation.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[BakeMan]

scriverb,

This would work if the query was static and saved as an object in the database. The real problem is that the query is dynamically created based off of a VBA program that alters the SQL based off of the date it is run. I am trying to get a rolling year based off of the last completed month. In order to use this data in a report I have to have field names for each of the 12 months in question. This is easy enough to do with column headers but I have to change these every time a new month comes around. So I wrote this VBA program that changes these for me based on the date the report is run. Now that this is done I cannot uses this as a from object in a Make Table query using the GUI provided by Access. I was hoping for a piece of SQL that would allow me to use this as a From in a Make Table query.

Thank anyway for your suggestion.

BAKEMAN

 

[scriverb]

I will work on this tonight and get you a response by morning. We can do this. There are a couple of ways to proceed. I will let you know.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

If you can please forward me via email a sample database with the tables and queries used and I will see what I can do. It is difficult to work with your query as stated without being able to see the outcome. I will post back here in the thread how I intend to get you the table. See my email address in my profile.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[BakeMan]

scriverb,

Thank you for your offer of help, but I broke the golden rule of posting and placed the same question in two different fora.

sxschech responded to my question in the "Access Other Topics" forum and he/she replied with:


Although there might be another way, this method should work. It will create a query that is useable from the queries tab and then the make table will use that query to generate the table. If you don't want the query to be on the queries tab after the code is finished, you can repeat the line of code that deletes the query from the database window.

Sub CrossTabTable()
Dim db As Database, QD As QueryDef
Dim strSQL1 As String
Set db = CurrentDb
On Error Resume Next
'Delete existing query
db.QueryDefs.Delete ("qryCrossTab&quot

On Error GoTo 0
strSQL1 = "TRANSFORM Count(RecordNumber) AS [The Value] SELECT 1 AS Header" _
& "FROM qryMIRs GROUP BY 1 PIVOT Format([DateInvestigationInitiated],""mmm-yy"&quot In" _
& "(""" & Format(DateAdd("m", -1, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -2, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -3, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -4, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -5, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -6, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -7, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -8, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -9, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -10, Now()), "mmm-yy&quot _
& """,""" & Format(DateAdd("m", -11, Now()), "mmm-yy&quot & """,""" & Format(DateAdd("m", -12, Now()), "mmm-yy&quot & """"
On Error GoTo 0
Set QD = db.CreateQueryDef("qryCrossTab", strSQL1)
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT * INTO tblCrossTab FROM qryCrossTab;"
DoCmd.SetWarnings True
End Sub


I think this will work for my needs. I am trying it now and will respond on both threads as to my progress.

Thank you again for your help.

BAKEMAN

 

[BakeMan]

Sorry so long to respond, but sxschech's response to my question worked out for me. I had a few other things to work out with the process before I was sure that this was going to be sufficient. Thank you scriverb for responding so quickly to my question.

BAKEMAN

 

[scriverb]

Anytime. Hope I can help you the next time you post.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???