Create table in code from crosstab type query

  • Thread starter Thread starter Piri
  • Start date Start date
P

Piri

Access97
I have been using a crosstab query from the design view and want to
change it to code.
The SQL derived from the design view is: ( INTO statement added):

strSQL = "TRANSFORM Sum(tblData.Qty) AS Count " _
& "SELECT tbldata.Stock " _
& "INTO [" & TempTable & "] IN '" & TempDBase & "' " _
& "FROM tblData " _
& "WHERE (((tblData.Status)='CO')) " _
& "GROUP BY tblData.Stock " _
& "ORDER BY tblData.Stock " _
& "PIVOT tblData.WeeksBack In
('W0','W1','W2','W3','W4','W5','NW0','NW1','NW2','NW3','NW4','NW5');"

DBEngine(0)(0).Execute strSQL, dbFailOnError

TempTable and TempDBase are/is in/an external MDB I use for holding
temporary data for reports.

I get an error 3000 - reserved error 3002 "there is no message for
this error"

Is it possible to create a table from a crosstab type query?

Piri
 
Open query in design view. Click on icon bar and change query type to 'Make
Table' and save.
 
Open query in design view.  Click on icon bar and change query type to 'Make
Table' and save.  
--
KARL DEWEY
Build a little - Test a little

Piri said:
Access97
I have been using a crosstab query from the design view and want to
change it to code.
The SQL derived from the design view is: ( INTO statement added):
Click to expand...
strSQL = "TRANSFORM Sum(tblData.Qty) AS Count " _
& "SELECT tbldata.Stock " _
& "INTO [" & TempTable & "] IN '" & TempDBase & "' " _
& "FROM tblData " _
& "WHERE (((tblData.Status)='CO')) " _
& "GROUP BY tblData.Stock " _
& "ORDER BY tblData.Stock " _
& "PIVOT tblData.WeeksBack In
('W0','W1','W2','W3','W4','W5','NW0','NW1','NW2','NW3','NW4','NW5');"
Click to expand...
DBEngine(0)(0).Execute strSQL, dbFailOnError
Click to expand...
TempTable and TempDBase are/is in/an external MDB I use for holding
temporary data for reports.
Click to expand...
I get an error 3000 - reserved error 3002 "there is no message for
this error"
Click to expand...
Is it possible to create a table from a crosstab type query?
Click to expand...
Piri
Click to expand...
Click to expand...

That seems to change the crosstab query to a select-type make table
query - losing the pivot in the process?

Piri
 
No, But you can create the crosstab query, then use that as the source of a
second (maketable query). Once you have the SQL statement for that second
query, just cut the SQL from the Crosstab into it, replacing the query name
with a pair of ( ) with the Crosstabs code inside of the ().

I think it would look something like:

strSQL = "SELECT T.* " _
& "INTO [" & TempTable & "] " _
& "IN '" & TempDBase & "' " _
& "FROM (TRANSFORM Sum(tblData.Qty) AS Count " _
& "SELECT tbldata.Stock " _
& "FROM tblData " _
& "WHERE tblData.Status='CO' " _
& "GROUP BY tblData.Stock " _
& "ORDER BY tblData.Stock " _
& "PIVOT tblData.WeeksBack " _
& "In ('W0','W1','W2','W3','W4','W5'," _
& "'NW0','NW1','NW2','NW3','NW4','NW5')) as T"

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top