INSERT INTO from a Crosstab query

[T2B]

Can something like this be done in Access? (I know this example will
fail, but is there a way to make it work?)

Can an Acces transform/pivot table be a subquery?

INSERT INTO tTableTwo(col1,col2)
SELECT b.col1, b.col2 FROM
(TRANSFORM First(a.xQTY) AS FirstOfxQTY
SELECT a.col1, a.col2
FROM tTableOne AS a
GROUP BY a.col1, a.col2
PIVOT a.Component In ("col1","col2")) AS b

 

[dinah sosthenes]

Can something like this be done in Access? (I know this example will
fail, but is there a way to make it work?)

Can an Acces transform/pivot table be a subquery?

INSERT INTO tTableTwo(col1,col2)
SELECT b.col1, b.col2 FROM
(TRANSFORM First(a.xQTY) AS FirstOfxQTY
SELECT a.col1, a.col2
FROM tTableOne AS a
GROUP BY a.col1, a.col2
PIVOT a.Component In ("col1","col2")) AS b

 

[John Spencer]

If you save the crosstab query and then reference the saved version, you
should be able to use it.

INSERT INTO tTableTwo(Col1,Col2)
SELECT Col1, Col2
FROM qSavedCrosstabQuery

I believe for this to work you will need to designate the columns in the Pivot
clause, but (as usual) I could be wrong.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[T2B]

I think you're right. You can't do it as sub-query but you can save
it, then query the saved query (columns designated as you suggested).
I wanted to avoid a saved query. I can put the sql into a recordset
in vba and do somewhat of a workaround, however. Thanks.