Create table in code from crosstab type query

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
 
K

KARL DEWEY

Open query in design view. Click on icon bar and change query type to 'Make
Table' and save.
 
P

Piri

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
 
D

Dale Fye

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.
 
Top