Converting Access query to an Access table 2

[dyarwood]

Hi

Can anyone tell me how to change the following run query in vba to an access table

strSQLQ1 = "SELECT TblMastProd.[Product Code], [Class Value]^3+[Cost Value]^2+[Demand LY Value] AS Rank"
strSQLQ2 = "FROM TblMastProd INNER JOIN TblClassify ON TblMastProd.[Product Code] = TblClassify.[Product Code];"
strSQLQ = strSQLQ1 & strSQLQ2
cnn.Execute strSQLQ

 

[shaddow]

Should use that querry combined with INSERT INTO

strSQLQ0 = "INSERT INTO MyNewTable ([Product Code],[New Class Value],[New Cost Value],Rank) "
strSQLQ1 = "SELECT TblMastProd.[Product Code], [Class Value]^3+[Cost Value]^2+[Demand LY Value] AS Rank "
strSQLQ2 = "FROM TblMastProd INNER JOIN TblClassify ON TblMastProd.[Product Code] = TblClassify.[Product Code]; "
strSQLQ = strSQLQ0 & strSQLQ1 & strSQLQ2

Looks like an simple insert but the values will be inserted from an select in the order.

Not shure if the table needs to be created before. I always had the table before inserting.

________
George, M

 

[TonyJollans]

Hi dyarwood,

Easiest way is just to add an INTO clause ..

strSQLQ1 = "SELECT TblMastProd.[Product Code], [Class Value]^3+[Cost Value]^2+[Demand LY Value] AS Rank INTO NewTableName"

Enjoy,
Tony

 

[dyarwood]

Cheers both. Still a little new to the SQL code. Cheers for your help.

dyarwood

 

[shaddow]

That was good Tony, nice and easy.

________
George, M