INSERT INTO a table results of a TRANSFORM 1

[Soundsmith]

How can I get the results of a TRANSFORM query into a pre-existing table? (Access 2000)

I have tried this:

DoCmd.RunSQL "DELETE * FROM tblServRpt"

strSQL = "INSERT INTO tblServRpt TRANSFORM Sum([tblBySsn].[cost]) AS SumOfcost SELECT [tblBySsn].[serv_code], Sum([tblBySsn].[cost]) AS [Total Of cost] FROM tblBySsn GROUP BY [tblBySsn].[serv_code] PIVOT [tblBySsn].[serv_date];"

DoCmd.RunSQL strSQL

The TRANSFORM as a stand-alone query works perfectly, but Access doesn't want the INSERT and TRANSFORM in the same SQL statement.

Thanks in advance. David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[oharab]

The easiest way is to not try it in one query.
Create your transform query, then create an append query based on the transform query.
"Make things as simple as possible, but not simpler"
(paraphrased from einstein I think!)

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[Soundsmith]

Thanks for the reply, oharab, but I don't understand. Where does the transform query reside while I'm appending? I guess I could run the query with DoCmd.OpenQuery, but I don't want it displayed onscreen. Could you give me a brief example of the code?

Thanks.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[oharab]

Just save the transform query as a saved query and then create a new strSQL based on that.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[Soundsmith]

OK, I got it (finally!) Let the INSERT INTO call the query.

Thanks, Ben.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[srobiolle]

Can you give a more detailed example ? I have the same problem, but do not understand how to resolve it

Thanks,
Stéphane